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

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))),
"")

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Fluxx wrote:

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

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" wrote:

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))),
"")

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Fluxx wrote:

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

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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Fluxx wrote:

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" wrote:

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))),
"")

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Fluxx wrote:

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

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...

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.

"Fluxx" wrote:

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

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))),
"")
--
David Biddulph

Fluxx wrote:
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...

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Fluxx" wrote in message
...
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

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

Best,
Frank