First non blank number in a row

I've been trying to figure out how i can identify the first non blank cell in 
a series and show me which column that cell is in. e.g.
        Col1   Col2   Col3  Col4      Col5  
Row1  34.2  3.45  45.1  432.1
Row2           43.1          541   
Row3                    34.1   51.12

What formula can i key in Col6, that shows me the for row2,that col2 is the 
first non blank number, and for row 3, col3 is the first non blank number and 
similarly for Row1, col1 is the first non blank number. 

Thanks 
0
Ivey (2)
5/16/2005 8:57:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
635 Views

Similar Articles

[PageSpeed] 50

In F2

=MIN(IF(A2:E2<>"",COLUMN(A2:E2)))

which is an array formula, so commit with Ctrl-Shift-Enter

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Rajiv@Ivey" <Rajiv@Ivey@discussions.microsoft.com> wrote in message
news:01F5843A-3183-4498-AE38-FC0B96293217@microsoft.com...
> I've been trying to figure out how i can identify the first non blank cell
in
> a series and show me which column that cell is in. e.g.
>         Col1   Col2   Col3  Col4      Col5
> Row1  34.2  3.45  45.1  432.1
> Row2           43.1          541
> Row3                    34.1   51.12
>
> What formula can i key in Col6, that shows me the for row2,that col2 is
the
> first non blank number, and for row 3, col3 is the first non blank number
and
> similarly for Row1, col1 is the first non blank number.
>
> Thanks


0
bob.phillips1 (6510)
5/16/2005 9:20:58 PM
You did mention that the result should be in colmn 6.
As long as you do not need to add more columns a nested if formula will do 
it.

=IF(A1>0,1,IF(B1>0,2,IF(C1>0,3,IF(D1>0,4,IF(E1>0,5,"none")))))

-- 
Greetings from New Zealand
Bill K


"Rajiv@Ivey" <Rajiv@Ivey@discussions.microsoft.com> wrote in message 
news:01F5843A-3183-4498-AE38-FC0B96293217@microsoft.com...
> I've been trying to figure out how i can identify the first non blank cell 
> in
> a series and show me which column that cell is in. e.g.
>        Col1   Col2   Col3  Col4      Col5
> Row1  34.2  3.45  45.1  432.1
> Row2           43.1          541
> Row3                    34.1   51.12
>
> What formula can i key in Col6, that shows me the for row2,that col2 is 
> the
> first non blank number, and for row 3, col3 is the first non blank number 
> and
> similarly for Row1, col1 is the first non blank number.
>
> Thanks 


0
5/16/2005 9:29:18 PM
Another way

=MATCH(TRUE,A2:D2<>"",0)


entered with ctrl + shift & enter


regards,

Peo Sjoblom

"Rajiv@Ivey" wrote:

> I've been trying to figure out how i can identify the first non blank cell in 
> a series and show me which column that cell is in. e.g.
>         Col1   Col2   Col3  Col4      Col5  
> Row1  34.2  3.45  45.1  432.1
> Row2           43.1          541   
> Row3                    34.1   51.12
> 
> What formula can i key in Col6, that shows me the for row2,that col2 is the 
> first non blank number, and for row 3, col3 is the first non blank number and 
> similarly for Row1, col1 is the first non blank number. 
> 
> Thanks 
0
PeoSjoblom (789)
5/16/2005 9:59:13 PM
excellent options

Thanks Bob and Peo

Bill K
"Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> wrote in message 
news:F9C91E1B-A5C1-4656-8DA8-54E1F110C8DE@microsoft.com...
> Another way
>
> =MATCH(TRUE,A2:D2<>"",0)
>
>
> entered with ctrl + shift & enter
>
>
> regards,
>
> Peo Sjoblom
>
> "Rajiv@Ivey" wrote:
>
>> I've been trying to figure out how i can identify the first non blank 
>> cell in
>> a series and show me which column that cell is in. e.g.
>>         Col1   Col2   Col3  Col4      Col5
>> Row1  34.2  3.45  45.1  432.1
>> Row2           43.1          541
>> Row3                    34.1   51.12
>>
>> What formula can i key in Col6, that shows me the for row2,that col2 is 
>> the
>> first non blank number, and for row 3, col3 is the first non blank number 
>> and
>> similarly for Row1, col1 is the first non blank number.
>>
>> Thanks 


0
5/16/2005 11:19:11 PM
Reply:

Similar Artilces:

Look up column name, match rows (a/cnumber) & summing up....its confusing!
Hi All, Here is what I'v been trying to do but..! I'v 2 sets of data (data1 & data2) Column name may be sane/different in each data set and same applies to acct_no.. I want to prepare a report that combine product & accounts data show accumulatd result on another sheet/file. Data 1 - upto Jan 2006 Acct. No Total Prod - A Prod - B Prod-C 1001 51 10 30 11 1002 47 15 20 12 1003 80 20 15 45 1004 64 25 16 23 Total 242 70 81 91 Data 2 - Feb 2006 Acct. No Total Prod - A Prod - B 1002 7 5 2 1004 16 10 6 1009 9 3 6 Total 32 18 14 Report required Acct upto Jan'06 Prod - ...

Getting row indexes on Range
(I refer to C# code, but answers in VB are welcome) I have a Range in Excel, which includes several cells (the cells the user selected in the Excel sheet). The range might include the following cells A2, B7, G4. This means that the cells might not be connected. If I look at myRange.Cells.Count, it will return 3. If I look at myRange.Row, it will return 2 (if A2 was the first selected row by the user). Now, I need to get the row numbers of all selected rows, so in the above range, I need an int[] of {2, 7, 4}. But I can't see any solution to go through the Cells and get t...

Row Height #11
I have a spreadsheet that periodically the row height is taller than needed. I tried autofit to no avail. Any Suggestions would be appreciated. Bill The default row height is determined automatically by Excel based on the size of the "Normal" font, as set under Format, Style for the active workbook. If you don't manually adjust the row height it will expand automatically to accommodate larger fonts or wrapped-text cells on that row. -- Jim "Bill" <Bill@discussions.microsoft.com> wrote in message news:A3F866D0-333F-47B9-9D4C-1AAED911B734@microsoft.com......

Formula to find the last number of a column (lower cell)
I have a column with numbers. I want a formula that it will be finding the lower cell that has data. For example in column A i have numbers in cells A1, A2 and A3. In cell A10 i want a fotmula. With this formula will see in cell A10 the number that i have in cell A3. If i put a number in cell A4 i want that number to be shown in cell A10. Try the below in cell A10 =LOOKUP(10^10,A1:A9) -- Jacob (MVP - Excel) "yiannis5231" wrote: > I have a column with numbers. I want a formula that it will be finding the > lower cell that has data. For example in colu...

Non genuine Outllook PST
I have had Microsoft Office 2003 which appears to be non genuine. I have bought Office Home & Student 2007 which doesn't have outlook. I cannot import my pst to Windows livemail nor can I copy it to my Outlook 2000 on another machine. Is my only option to buy Outllok 2007 ? Will it let me import the pst from my unvalid copy. Any help appreciated Aside from a pirated version of OL you are also multiposting. You were told to go ask in the Windows Live newsgroup. Hint: Buy a valid and licensed Outlook -- Peter Please Reply to Newsgroup for the benefit of others ...

insert page number in Roman letter
I need to insert page number in Roman letter like this i, ii, iii, iv; how can i do that ? please help me Have a look at Jim Cone's code at this URL http://snipurl.com/9qze Gord Dibben Excel MVP On Tue, 12 Oct 2004 22:35:10 -0700, "Pragati" <Pragati@discussions.microsoft.com> wrote: >I need to insert page number in Roman letter like this i, ii, iii, iv; how >can i do that ? please help me ...

REALLY miussing rows in Excel2000
have some big Excel spreadsheets, 50 columns by 65525 rows (but definitely not 65536), 45MByte. They are all very similar, just a few formala tweaks different On one of them, the top nine rows have become "invisible" It is possible to navigate through all the cells involved with the cursor keys- the formula bar. name box bar etc all function correctly and show cell contents etc. They are just not visible The missing rows are not hidden The missing rows are not filtered The missing rows have a standard row height The missing rows are not locked If I move the cursor from row 10 towa...

First in First out
I need help in figuring out how to implement FIFO. I order a product and it is put in inventory with a PO and price. Then I may order some more in a few months before I use up all the old, it is added in with its own PO and price. Now when I take out of inventory I want to use up all the items from the first order then switch to the next one. Any help would be nic Thank Ji You need a date stamp at the time the product into inventory. Maybe use an "expected arrival" date on the PO. But how does this relate to Excel? Questions like this should be directed to: http://www.allexpert...

Top 90% of records
Have the oddest SLA requirement I've ever been asked to have a look at, hope you can help. Basically have a set of data of closed records for 2005. My manager wants to show of the closed records how many days were 90% of them completed? She is looking at this as a 'best case' scenario, so therefore in simplistic terms this means: If there were 50 records closed, look at 90% of these records (in shortest amount of days order) and give the highest number once at 90%. e.g. (with a small dataset). There are 50 records. The largest number of the dataset once sorted is 20 (at the 4...

Rotate row
Hi all, A friend has a huge workbook that her tutor has entered lots and lots of data into. She intends to use this data for her college project, but it's been entered across the book and she'd really like to turn all the rows into columns. Any way to do this? All at once or one by one? You can do it all at once as long as you have enough room on the sheet, where the new configuration will *not overlap* the old configuration. Select the rows and right click in the selection and choose "Copy". Right click in the top left cell of the new location (remember - no overlap), a...

items assigned both lot numbers and serial numbers
Hi, Does anyone know of an add-on that would support tracking inventory in house by lot number but then assigning a specific serial number when the item is shipped? Thanks for any info you can provide. -- Thanks very much! Michele Durkin As you know, you can have a serial number mask such that one portion is an incrementing serial number and the other can be a constant. the only problem is that you want to assign that serial number when shipping... You may be able to achieve that using Manufacturng serial/lot number functionality. HS "mdurkin" <mdurkin@discussions.micros...

How do I print just the first page in my received email
To me it looks like I can either print all, even or odd pages only. can I just print first page only, and how do I do that? HTML messages can be printed one page at a time if you use File, Print on the menu. You can also Edit a plain text message to change it to HTML be able to use File, Print. "PeterM" wrote: > To me it looks like I can either print all, even or odd pages only. can I > just print first page only, and how do I do that? > > Many thanks Mary..........When I'm looking at my email (please remember, this is my 3 day with outlook) how do I cha...

Text after non-breaking space truncated: Publisher 2003 WinXP
Hello All, Watch out folks! If you are using non-breaking spaces in your Pub docs and edit your story in Work (Edit-->Edit Story in Word), beware that the character after the non-breaking space is truncated. Also, when you close the Word edit window and return to Pub the characters are truncated in the original Pub doc. Kinda scary. If some one has time, can you test it and see if you get the same behavior. Thanks in advance ...

Auto Numbering #4
Hello All, Autonumbering I am using Office 2003 and have the following problem Col B 1 Mar-07 2 Sales 3 Sales 4 Mar-07 5 Sales 6 Apr-07 7 Apr-07 8 Sales .... .... .... I wish to enter in Column A, a formula which when dragged down should show me Nos. 1, 2, 3.... Only against the Months (viz Mar, Apr, May...etc) The Cell in Column A against the Word "Sales" in Column B should be blank. The Output should come as follows: Col B 1 Mar-07 Sales Sales 2 Mar-07 Sales 3 Apr-07 4 Apr-07 Sales .... .... Any one can help me in the right direction! Thanks in a...

Numbers did not reset Jan. 1
I am using access 2003 in XPsp2 I have a log that assigns consecutive numbers preceded by a 2 digit year. (ex 10-001) On Jan. 1 the number was supossed to change and reset from 09-690 (last number of the year) to 10-001. This did not happen. The year changed but the number carried over showing 10-691. Here is the VB code that was used for this change I think. I did not write it and my VB knowledge is poor. Me.txtIncrNum.Enabled = True Me.txtIncrNum.SetFocus Me.txtIncrNum.Text = Nz(DMax("[IncrNum]", "[tblincidents]", _ "Year([datefi...

the first
-- zdx840626��sina.com ...

Uncollapse Grouped Rows on a Proctected worksheet
I have grouped rows on a worksheet then protected the worksheet to prevent users from inserting rows. I would like to collapse the rows so the user only sees the group descriptions. I would then like them to uncollapse the group they wish to enter in. Any suggestions? Thanks. This setting isn't remembered between closing/reopening the workbook. (So Auto_open is a nice spot for it.) Option Explicit Sub auto_open() Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks .Protect Password:="hi", userinterfaceonly:=True .Enabl...

Summary Query based on First Occurrence
I've got a list of invoices for businesses, some which have multiple invoices on a given day. I want to be able to return ONLY the First Day a customer was invoiced AND the TOTAL for just that day. Here's what the data looks like: Account InvoiceDate TotBill A B C Corp. 20041231 3500.00 A B C Corp. 20041231 9500.00 A B C Corp. 20050131 1500.00 A B C Corp. 20050131 2000.00 BARBERS INC. 20041431 1500.00 BARBERS INC. 20041431 12000.00 BARBERS INC. 20050131 300.00 BARBERS INC. 20050131 470.00 Ideally, my Qu...

Outlook 2003 slow opening first html msg
Anyone else noticed a slowdown recently when opening the first html formatted message in Outlook 2003? It might have occurred during the recent MS updates, or it might be where I upgraded to IE7. That all happened about the same time. On my Pentium 800 with .5gb of ram, the message frame appears in about 20 seconds and the contents in another 20 seconds. This seems to only happen on the first e-mail to be opened. The next opens instantly. Dave ...

Toolbar doesn't react on first click.
Hello everyone. I create program with one toolbar and few CWnd objects in CFrameWnd. Only one window (CWnd) is shown at time. When I start some action on this window (OnLButtonUp and OnlButtonDown) program makes few expensive calculation, next he hides window (SW_HIDE), and shows another (SW_SHOW). But sometimes when I make double-click on window, everything show OK, but toolbar freeze (doesn't react on first click and I must click him once more). I try to override OnLButtonDblClk but my application never goes in to. I thing the problem are messages that are pushed to queue when pr...

Use the number part
Sometimes I have data which I enter as : <0.05 How do I get Excel to use the cell's entry as 0.05 for further calculations? On Dec 21, 9:49 pm, Rudy <R...@discussions.microsoft.com> wrote: > Sometimes I have data which I enter as : <0.05 > How do I get Excel to use the cell's entry as 0.05 for further calculations? When in A1, maybe... =VALUE(MID(A1,2,255)) Ken Johnson If Say you wanted to add it to the value in D4 then try: =D4+(--SUBSTITUTE(D12,"<","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place ...

Transforming first n nodes
I have a large xml (35MB) and I need to transform its top 200 rows using an xsl (in ASP.Net web app). Is this possible to do it in an efficient manner? Thanks. You can subclass XmlTextReader and make a custom one that only return first N elements. Then you can use it to load data into XPathDocument and do a transfer. Yan "Salman" <salman_z_g@hotmail.com> wrote in message news:Oa7kJoiMEHA.1192@TK2MSFTNGP11.phx.gbl... >I have a large xml (35MB) and I need to transform its top 200 rows using an > xsl (in ASP.Net web app). > Is this possible to do it in an efficient...

Concatenate first:last! worksheet
I need to concatenate 150 rows in 50 different worksheets for a totals page, and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets ...

How to determine number of pages linked to current page
Hello, I'm using FP 2003, and was not able to find anything in the help file. I'm trying to find out if it's possible for FP to determine the number of htm pages (and their name) that refer to any one htm page. So I have my current page opened in FP (lets call it A), and I'd like to know how many of the pages in the structure refer to this page A. Any way of doing this?? Thanks a lot!! Louis First, close the page. Them with the page highlighted in Folder View, select Use the Hyperlinks tab at the bottom of your workspace, -- ~ Kathleen And...

Locating first blank cell
I am looking for a macro that will select the first blank (empty) cell in a given column please. Thanks, Mike Hi Mike Sub test() Dim GivenColumn As Long GivenColumn = 1 Columns(GivenColumn).Find(What:="", _ After:=Cells(1, GivenColumn), _ LookAt:=xlWhole).Select End Sub HTH. Best wishes Harald "Mike W" <mike@ehb-docks.fsbusiness.co.uk> skrev i melding news:dcq6va$97d$1@newsg4.svr.pol.co.uk... > I am looking for a macro that will select the first blank (empty) cell in a > given column please. > > Thanks, > > Mike > > Brilliant...