Copy and Paste Binary Matrix Values in a specific order with FORMULA or VBA

[DaveMars]

Hello,

this is my first thread but I have been reading and learning since months if not years.

Now I'm stuck with this problem and I can't find a solution on my own.

I have a binary table like this:

	A	B	C	D
16C	0	1	0	0
51C	0	0	1	0
17A	0	0	0	1
39A	0	1	0	0
24C	0	0	0	1
16A	0	0	1	0
26C	1	0	0	0
27A	0	0	1	0
33A	0	1	0	0
12C	0	0	0	1
20C	1	0	0	0

<tbody>
</tbody>

And I'd like to build an output table which tells me, for each column header, the row label which is =1.

The number of occurences of 1 for each column is <= 3, so I would like to get for each column, at max, the first three labels which have 1 in the matrix.

Example:

A	B	C	D
26C	16C	51C	17A
20C	39A	16A	24C
	33A	27A	12C

<tbody>
</tbody>

I've tried with:

- Nested IF
- SMALL function
- MATCH
- INDEX

but I really can't figure out a solution.

Could you please help me?

Thank you

 

[jkpieterse]

This matrix formula seems to do the trick:

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(B$2:B$12=1,ROW(B$2:B$12)-1,90),{1;2;3})),"")

Select three cells beneath each other and enter the formula using control+shift+enter. Then copy to the right.

 

[DaveMars]

Thank you for answering, but I'm getting an error and excel highlights this part of the formula:

$A$12,SMALL

I don't know if I'm doing something wrong or how to correct.

Could you please help me?

 

[jkpieterse]

Replacing all comma's in the formula I gave with semi-colons ; will probably help.

 

[DaveMars]

Ouch, my bad!

Thank you, your help is very appreciated.

The code works perfectly.