Find values in table and return row/column name

[Fluxx]

Hi,

I am really desperately looking for help with this Excel problem I have.

I have a large 2-dimensional table, that contains unique IDs in the row and
column headers.The data in the table is a largely zeros, with a few values in
between. The structure is basically as follows:

ID A B C D E ....
1 0 0 0 0 20
2 20 0 0 0 80
3 0 0 0 0 0
4 100 0 0 0 0

In a second sheet, I have copied the first column of the table, i.e. I there
have a list of all vertical IDs. Now I am looking for a way to return for
each row the letter of the column that has a value >0 (which could be
multiple ones).

The result should be something similar to this (anything close would be
helpful, too):
1 E
2 A E
3
4 A

Any ideas? Thanks a lot in advance for your help - much appreciated!

Best,
Frank

 

[Domenic]

For simplicity, let's assume that A1:F5 contains the source table, and
H2:H5 contains ID numbers 1, 2, 3, and 4. Then try...

I2, copied down:

=COUNTIF(INDEX($B$2:$F$5,MATCH($H2,$A$2:$A$5,0),0),">0")

J2, confirmed with CONTROL+SHIFT+ENTER, and copied across and down:

=IF(COLUMNS($J2:J2)<=$I2,INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$5,MATCH($
H2,$A$2:$A$5,0),0)>0,COLUMN($B$1:$F$1)-COLUMN($B$1)+1),COLUMNS($J2:J2))),
"")

 

[Fluxx]

Hi Domenic,

many thanks for your quick reponse. Unfortunately I am failing to get the
second and more complex formula you provided to work ("Error in the
formula").

I changed all the "," to ";", and all brackets seem to be fine, but there
seems to be some other problem with the formula. Could you help out once more?

=IF(COLUMNS($J2:J2)<=$I2;INDEX($B$1:$F$1;SMALL(IF(INDEX($B$2:$F$5;MATCH($
H2;$A$2:$A$5;0);0)>0;COLUMN($B$1:$F$1)-COLUMN($B$1)+1);COLUMNS($J2:J2)));
"")

Many Thanks!
Frank

 

[Domenic]

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER. In other words, after typing the formula, press down both the
CONTROL and SHIFT keys, then while both keys are pressed down, press the
ENTER key. If done correctly, Excel will automatically place curly
braces {...} around the formula.

 

[Fluxx]

Yes, I tried that, of course.

It's not that the formula is producting wrong results or something - I
cannot even enter it in the first place - I always get "The formula you typed
contains errors".

Apparently there is some problem with the formula, that I do not see...

 

[Excel Curious]

I've had to do this myself too.

I used the following method:

1. Highlight the data set and do a Find/Replace on all the zeros and replace
with nothing
2. Highlight each column (one at a time) and do a Find/Replace using "*" and
replace with a formula referencing the column header (ie =$B$1)
3. Highlight the entire data set and select the GoTo Special and select blanks
4. With the blanks highlighted right click on one of them and choose delete
5. Select shift cells left from the deletion options

Not as fancy as a formula, but get the job done.

 

[David Biddulph]

You've snipped the message to which you're replying, and all the previous
content.

If you are looking at either
=IF(COLUMNS($J2:J2)<=$I2,INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$5,MATCH($
H2,$A$2:$A$5,0),0)>0,COLUMN($B$1:$F$1)-COLUMN($B$1)+1),COLUMNS($J2:J2))),
"")
or
=IF(COLUMNS($J2:J2)<=$I2;INDEX($B$1:$F$1;SMALL(IF(INDEX($B$2:$F$5;MATCH($
H2;$A$2:$A$5;0);0)>0;COLUMN($B$1:$F$1)-COLUMN($B$1)+1);COLUMNS($J2:J2)));
"")
you'll need to get rid of the line break in the middle of the cell reference
$H2.

To get rid of the reported error, it should be
=IF(COLUMNS($J2:J2)<=$I2,INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$5,
MATCH($H2,$A$2:$A$5,0),0)>0, COLUMN($B$1:$F$1)-COLUMN($B$1)+1),
COLUMNS($J2:J2))),
"")

 

[Don Guillett]

I used columns a:e on sheet 11 as the source and col e of the active sheet
as the list.
Sub GETCOLLTRS()
Dim mc, i, c, j As Long
mc = 5 'col E
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
c = mc + 1
With Sheets("sheet11")' change to suit
For j = 2 To .Cells(i, Columns.Count) _
..End(xlToLeft).Column
If .Cells(i, j) > 0 Then
'Cells(i, c) = .Cells(i, j)
Cells(i, c) = Chr(j + 64)
c = c + 1
End If
Next j
End With
Next i
End Sub

 

[Fluxx]

Thanks a lot for all of your input - you helped me a lot!

Best,
Frank