Creating array from matrix with blank values

I have a matrix in the following format

1 X X X X X X X X
X X X 3 X X X X X
4 X X X X X X X X

where 'X' is a blank cell.

I need to get the data in the form
1
3
4

Any suggestions? I tried a nested if statement but it doesn't work
since there are more than 7 ifs.

0
10/6/2006 1:29:52 PM
excel 39879 articles. 2 followers. Follow

1 Replies
449 Views

Similar Articles

[PageSpeed] 52

Andryll,

If there is only one value in each row, then simply using

=SUM(A1:L1)

where A1:L1 is your top row, then copied down for two more rows, will give you your desired result.

HTH,
Bernie
MS Excel MVP


<Andryll.Davis@gmail.com> wrote in message 
news:1160141392.644344.173360@i3g2000cwc.googlegroups.com...
>I have a matrix in the following format
>
> 1 X X X X X X X X
> X X X 3 X X X X X
> 4 X X X X X X X X
>
> where 'X' is a blank cell.
>
> I need to get the data in the form
> 1
> 3
> 4
>
> Any suggestions? I tried a nested if statement but it doesn't work
> since there are more than 7 ifs.
> 


0
Bernie
10/6/2006 1:44:04 PM
Reply:

Similar Artilces:

Blank E-Mails
Some of my users are complaining that e-mails sent or received are blank, I have tried the SP3 patch, changing text formats. Even some random command line. regsvr32 - inetcomm.dll. The cause of this blank e-mail fettish is unknown and doing my head in. We have restarted the SBS a number of occasions and all seems to be going fine until I hear those lovely word, "My e-mails are blank AGAIN" Ahhhh! Can someone, ANYONE help with this, The problem as far as I can see is common and microsoft seem to know nothing about it. Please write to my e-mail address or post here if you k...

If any cell in a range is blank question
Hello. I have two sheets in a workbook. One is a Summary sheet, and the next is titled "Bldg 5". What I am trying to make sure is that the range A1:K20 on the Bldg 5 sheet has had data entered to them (ie, they are not blank). The question that corresponds to Summary sheet A1 is "Has the data for Bldg 5 been updated?" If ANY blank in Bldg 5's range A1: K20, then put a "NO" in Summary A1's cell. If ALL of the Bldg 5 range had data in it, then put a "YES" in Summary A1. I also need A1 to update as data is entered into the Bldg 5 range (a...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

Ending balance doesn't equal market value
I am using MS Money 2004. In my portfolio the Contributions ($) Market Value amount is showing a negative amount, but Today's Balance shows a zero Ending Balance. How do I get them to both show tha same number? In microsoft.public.money, Eggman wrote: >I am using MS Money 2004. In my portfolio the Contributions ($) Market Value >amount is showing a negative amount, but Today's Balance shows a zero Ending >Balance. How do I get them to both show tha same number? Try File->RepairMoneyFile->QuickFileRepair Look for future transactions. ...

Lowest value in a selection of data
Hi What formula would I use to find the lowest value in a selection of data i.e. B49:E52. Much Appreciated Brian =MIN(B49:E52) -- Gary''s Student - gsnu2007a Jumping in here... Is it possible to know the address of the cell containing the minimum value? John. You can use the MATCH function to locate the (relative) position, but if you have more than one value which are minima then it will only find the first. Pete On Nov 16, 7:23 pm, John Google <JohnGoo...@hotmail.co.uk> wrote: > Jumping in here... > > Is it possible to know the address of the cell containin...

How to change background color of a window that has been created.
I need to change the background of a window that has been created, can you help? Hello Jerry, > I need to change the background of a window that has been created, can > you help? > Answer depends upon the type of window. Typically you can do this in OnEraseBkgnd. Certain controls respond to WM_CTLCOLOR and yet some other, like CButton, need to be owner drawn to change the color. If you have a generic CWnd, you can always specify the brush in the window class; so no special painting is needed. This is good when you know that the background will have one color all the time. -...

Creating a Report using Tables (that have no relationships)
Im looking to create an invoice report that uses data from three different tables that are created dynamically by a query. The data for these will change but I would like to simply pull the data from these tables to create a report. The tables have the following information: 1. Company Info (name, address) 2. Customer Info (address, name, etc) 3. Customer Order (products, price, other info) 4. Invoice Info (ID, GST #, etc) I realize I could have created a table with relationships but the data above is dynamic as there are 4 different companies and based upon user inp...

Continuation of Formula in New Array
Hi I am using this formula VLOOKUP($B$1,'Team A'!$A$18:$IV$45,MATCH($B5,'Team A'!$18:$18,0),0) The Array starts from A18 As you realise due to Excel limitation (!) the array only goes as far as Column IV; I wish to continue the Table array ( starting from a new row rather than a new worksheet) say from Cell A50 to IV77 and then continue again from A82 to IV109 and perhaps etc Is it possible. Please Note If it is to be done in Macro Please provide me a detailed Idiot Guide as I have NEVER PERFORMED/USED a Macro, and would not know where to start. Thx Gunjani I don'...

Adding Values From Different Tabs
Is there a way to add values from different tabs on the same spreadsheet? For example A2 from tab 1 and A2 from tab 2 -- Flipkid2 ------------------------------------------------------------------------ Flipkid2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17984 View this thread: http://www.excelforum.com/showthread.php?threadid=398184 Assuming the name on Tab 1 is Sheet1 and Tab 2 is Sheet2, use thi formula: =Sheet1!A2+Sheet2!A2 note the name of the sheet is followed by "!" to indicate it is a shee name. HTH Bruc -- swatsp0 ------------...

Money 2005 Bug: Portfolio toolbar button loads blank page titled Portfolio Manager
Problem Description: Clicking on the Portfolio toolbar button loads a blank page that's titled Portfolio Manager. That's it! I can't see any of my open positions, etc... using this toolbar button. Frequency of problem: Always Happens Operating system / version: Microsoft Windows XP Professional - 5.1.2600 Computer manufacturer and model: Dell Computer Corporation, Dimension 8200 Processor model and speed: I586IV, 1994 MHz Memory (RAM): 523.276 MB The only input I can offer is that I have the same problem on one computer, but it works fine on the other. I converted from 2004 Delu...

creating a recurring reminder email message
I want to create a reminder message that gets emailed to a distribution list on the same date every month. How can we do that? ...

Display only duplicate values and delete UNIQUE Items
All I have a very large list of data and on a monthly basis i need to display only the duplicate items in a spreadsheet. I would like to do this in VBA and then run it as a macro on the spreadsheet. Alot of the sites that i have seen only show how to removed the duplicates. Excel 2007 has a function which removed all duplicates but so far i have found nothing that only displays the duplciates.... any ideas anyone? Assuming that the field you use to determine uniqueness is column A, you can put this formula in a helper column: =3DIF(COUNTIF(A:A,A2)>1,"Duplicate","Unique&...

Dynamic array
I am trying to grab missing numbers from a sequence of numbers in an ID field in a table using a For Next loop that will plug the missing numbers into dynamic array. I'm working off of Access help. I've tried so many variations of this trying to get it to work that I can't remember them all, but here is the latest version. It generates an index out of range error just before the Redim statement. Can anyone tell me why? Actually getting an array to work in VBA is my focus right now because I've never been able to create one that worked. I haven't even given any...

Problem creating full text index
I seem to be unable to create a full text index of either my public or private store. When I try to creat the index I get the error; The directory name is invalid. Facility: MSSearch.Admin.1 ID no: 8007010b Exchange system Manager This seems to be happening for both the public and private stores. The directory does exist. I have looked over the permissions and tried to compare to known working servers and I haven't moticed any obvious differences. Any ideas? Did you follow this article: http://support.microsoft.com/kb/822932/en-us Nue "Jonathan Huber" <JonathanHu...

Messages Forwarded as Attachments Appear Blank
Hello, I am running an Exchange 2003 Org, with mixed Outlook XP/2003 clients, all on the latest service packs. I have one client (our CEO) that when he receives forwarded email from outside the organization, the forwarded message shows up as an attachment, but when he opens the attachment, it is displayed as a blank email (no to, from, cc, subject, body, or anything). I have checked the AV logs on every "AV for Email" gateway from the source to the destination, and I don't see anything getting stripped from the message. I have also checked all the AV servers, and made sure...

Passing 2-D array as function parameter.
If I pass a 2-D array ar[4][6] like this: foo(int ar[][6]...); What has been passed? Is it a pointer of whole array? Thanks. if you want to point to whole array you must call foo(ar) and in prototype or definition write foo (int ar[] [6]) and if you want to accsess only one row you must write foo(ar[4]) at calling the function "Frank E Rogers" <syang@pelco.com> wrote in message news:#u9vtA0UEHA.212@TK2MSFTNGP12.phx.gbl... > If I pass a 2-D array ar[4][6] like this: > foo(int ar[][6]...); > What has been passed? Is it a pointer of whole array? > Thanks. > &...

How do I create a spread sheet for tracking batting statistics?
Trying to keep a running log of batting stats for the season. Can anyone help? http://www.baseball-almanac.com/bstatmen.shtml "Robert" wrote: > Trying to keep a running log of batting stats for the season. Can anyone help? ...

Install 3.0 Failed
I am in the process of installing 3.0 on a server. All of the inital checks are fine. I proceed to install and it fails with an error "Action Microsoft.Crm.Setup.Server.CreateSetupUserAction failed HRESULT 0x80040237" I searched the Knowledge Base and found KB 941498. I did what it said but got the following error for msxml2.dll - LoadLibrary("msxml2.dll") failed. The specified module could not be found. How do I go about getting XML 2 (the parser I'm guessing) when it hasn't be supported in years? Is there something else I can do? I am installing 3.0...

Exclude like values with a JOIN?
I have the following query: SELECT dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD FROM dbo.qry_UV_CARS_PlateX_Valued RIGHT OUTER JOIN dbo.qry_UV_CARS_PlateX_ShouldBeValued ON dbo.qry_UV_CARS_PlateX_Valued.Yl_iD = dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL) And I have another secondary table containing the same field as Yl_iD. What I want to do is have my main query exclude all the Yl_iD values that are in my secondary table. What JOIN and criteria will do this? Many thanks.....Jason WHERE Yl_iD N...

System Restore all off, still creating restore points on 2nd parti
Although we have System Restore turned off on all drives, restore points are still being created on the secondary partitions. The OS partiton is drive C, NTFS. The secondary partition is drive D, FAT32. The restore points are being created when applications (i.e. DirectX, WinDVD, etc.) are installed from the second partition (drive D). We must install these from a secondary partition. What are we missing? These restore points are filling up our drives. Also, I thought System Restore wouldn't work on FAT32 drives? Thanks, -- Bill Baker Bill, I use two drives b...

increment a value by 1
How do I increment, automatically, a cell by 1 if a condition is met? If one of my cells reaches 10k I want another cell to increment by 1. Can anyone help me if this function can be done? Thank you, Ron Do you want the increment only when it changes from below 10K to above 10K, or anytime is is above 10K? -- Gary's Student "Workshops" wrote: > How do I increment, automatically, a cell by 1 if a condition is met? If one > of my cells reaches 10k I want another cell to increment by 1. Can anyone > help me if this function can be done? > Thank you, Ron Increm...

Create a new restore point
How does one create a new restore point in both Vista and Win7? When I go to Accessories-->System Tools-->Restore all I get is how to restore from a previous one. That's not what I want. I want to create a restore point. OldEd On 3/9/2010 5:14 PM, OldManEd wrote: > How does one create a new restore point in both Vista and Win7? When I > go to Accessories-->System Tools-->Restore all I get is how to restore > from a previous one. That's not what I want. I want to create a restore > point. > OldEd Click "Computer" in start menu th...

multiply value based on quantity
I am trying to calculate a discount price based on quantity purchased. For example if someone buys 20 widgets they pay $4.00 each for them, if they buy 50 widgets they pay $4.00 each for the first 20, then $3.50 each for the next 30, 100 widgets - $4.00 first 20, $3.50 next 30 and $3.25 for the other 50.............. Any hope? Here's a way with quantity in A2: =MIN(A2,20)*4+MAX(MIN(A2-20,30),0)*3.5+MAX(MIN(A2-50,50),0)*3.25 Regards, Per "Bigfoot3910" <Bigfoot3910@discussions.microsoft.com> skrev i meddelelsen news:524D35AE-4524-46A3-A963-703CB385C7F...

conditional formatting: separating rows with differing values
Hi, Need help with conditional fomatting. What I need: I got a worksheet with 950 rows and 5 columns. The first row contain the headers. Columns A, B, D and E contains unique values. But the column C contains text values which repeats sequentially for 6-7 rows and changes thereafter. Now I want to put up a line (using border) through the whole row dividing this transition row. My Idea: Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so on till 2 consequtive values differ. Now divide the 2 rows using the border formatting option in conditional formatting dialog box. So...

Macro: Find matching DATA, copy correspondently values into another list
Okey guys - please advise me on this. (There was unfortunately no quick response to my latest problem - and I begun to swim... However - that brought me out on deep VBA-water...again - and now need a life buoy (metaphorical spoken)). The core of my macro problem is to look for all the values in column A (Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in another list placed in column A in sheet "LIST". When finding the match I will copy correspondent celle.value in column D / sheet "BOARD" into column B in sheet "LIST" - this w...