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:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl73, width: 64"][/TD]
[TD="class: xl71, width: 64"]A[/TD]
[TD="class: xl71, width: 64"]B[/TD]
[TD="class: xl71, width: 64"]C[/TD]
[TD="class: xl71, width: 64"]D[/TD]
[/TR]
[TR]
[TD]16C[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD]51C[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD="class: xl72"]17A[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD]39A[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD]24C[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD]16A[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD]26C[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD]27A[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD]33A[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD]12C[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD]20C[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl70, width: 64"]A[/TD]
[TD="class: xl70, width: 64"]B[/TD]
[TD="class: xl70, width: 64"]C[/TD]
[TD="class: xl70, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl70"]26C[/TD]
[TD="class: xl70"]16C[/TD]
[TD="class: xl70"]51C[/TD]
[TD="class: xl70"]17A[/TD]
[/TR]
[TR]
[TD="class: xl70"]20C[/TD]
[TD="class: xl70"]39A[/TD]
[TD="class: xl70"]16A[/TD]
[TD="class: xl70"]24C[/TD]
[/TR]
[TR]
[TD="class: xl70"][/TD]
[TD="class: xl70"]33A[/TD]
[TD="class: xl70"]27A[/TD]
[TD="class: xl70"]12C[/TD]
[/TR]
</tbody>[/TABLE]

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.