Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
From array find corresponding column/row value
This seems so simple but...
I have an array (zero means blank in this example): A B C D E 1 0 1 0 0 3 2 1 1 2 5 8 3 0 0 0 0 5 etc How can I find the first non-blank cell in each row and return the column heading as the result? e.g. Row 1 = B Row 2 = A Row 3 = E |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
From array find corresponding column/row value
I put this in F2:
=IF(COUNTA(A2:E2)=0,"none!",INDEX($A$1:$E$1,MATCH( TRUE,A2:E2<"",0))) And dragged down the column. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Steve-in-austin wrote: This seems so simple but... I have an array (zero means blank in this example): A B C D E 1 0 1 0 0 3 2 1 1 2 5 8 3 0 0 0 0 5 etc How can I find the first non-blank cell in each row and return the column heading as the result? e.g. Row 1 = B Row 2 = A Row 3 = E -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
From array find corresponding column/row value
assuming your data starts from second row and your titles are in first row:
=INDEX($A$1:$E$1,1,MATCH(SMALL($A2:$E2,COUNTIF($A2 :$E2,0)+1),$A2:$E2,0)) "Steve-in-austin" wrote: This seems so simple but... I have an array (zero means blank in this example): A B C D E 1 0 1 0 0 3 2 1 1 2 5 8 3 0 0 0 0 5 etc How can I find the first non-blank cell in each row and return the column heading as the result? e.g. Row 1 = B Row 2 = A Row 3 = E |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
From array find corresponding column/row value
Thanks Dave
I was working the index/match route but could not get the synatax right. Is there a super Excel class where one can learn these tricks? I can't take much more of the help files--I spent an hour before caving in and asking the discussion group. "Dave Peterson" wrote: I put this in F2: =IF(COUNTA(A2:E2)=0,"none!",INDEX($A$1:$E$1,MATCH( TRUE,A2:E2<"",0))) And dragged down the column. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Steve-in-austin wrote: This seems so simple but... I have an array (zero means blank in this example): A B C D E 1 0 1 0 0 3 2 1 1 2 5 8 3 0 0 0 0 5 etc How can I find the first non-blank cell in each row and return the column heading as the result? e.g. Row 1 = B Row 2 = A Row 3 = E -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
From array find corresponding column/row value
Hang around in the .worksheet.functions newsgroup and you'll pick up lots of
stuff. It's scary how much those people can do. Steve-in-austin wrote: Thanks Dave I was working the index/match route but could not get the synatax right. Is there a super Excel class where one can learn these tricks? I can't take much more of the help files--I spent an hour before caving in and asking the discussion group. "Dave Peterson" wrote: I put this in F2: =IF(COUNTA(A2:E2)=0,"none!",INDEX($A$1:$E$1,MATCH( TRUE,A2:E2<"",0))) And dragged down the column. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Steve-in-austin wrote: This seems so simple but... I have an array (zero means blank in this example): A B C D E 1 0 1 0 0 3 2 1 1 2 5 8 3 0 0 0 0 5 etc How can I find the first non-blank cell in each row and return the column heading as the result? e.g. Row 1 = B Row 2 = A Row 3 = E -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find all matches in an array | Excel Discussion (Misc queries) | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
How do I find a value in an array (VLOOKUP? HLOOKUP?) | New Users to Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |