Unique list out of non-unique row

[tabibito]

Hello everyone!

mrexcel has been my top resource when it comes to Excel-related questions. Great to be a member now.

What follows is a tricky combination (for me, that is) of
- starting from several rows named "A", "B", ... with blank cells, zeroes and values larger than zero in columns named "1", "1.5", ...
- transposing them into a continuous vertical list of "row name - column name" pairs (e.g. "A" "1")
- leaving out all the blank cells and zeroes.


[TABLE="class: grid, width: 507, align: center"]
<tbody>[TR]
[TD]　[/TD]
[TD]1
[/TD]
[TD]1,5[/TD]
[TD]2[/TD]
[TD]2,5[/TD]
[TD]3[/TD]
[TD]3,5[/TD]
[TD]4[/TD]
[TD]4,5[/TD]
[TD]5[/TD]
[TD]5.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]A
[/TD]
[TD]2.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]　[/TD]
[TD]4[/TD]
[TD]　[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]5.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]3.5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]4.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]2.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]3
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]1.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　
[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]2.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]　
[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]4.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


What I've tried so far:
(1) Created a helper table with the full range of 1 , 1.5 , 2 , ... , 5 , 5.5, displaying "" for all blanks or zeroes in the upper left chart.
(2) From that, tried to create another helper table displaying only the unique values of the helper table created in (1). This did not work out because the array formula below does not accept formula ""s:
=IFERROR(INDEX($helpertablerange,SMALL(IF(ISBLANK($helpertablerange),"",COLUMN($helpertablerange)-MIN(COLUMN($helpertablerange))+1),COLUMN(C1))),"")
CTRL+SHIFT+ENTER
(3) From that, planned on using INDEX/MATCH combined with the TRANSPOSE function to create the unique list of "ROW LETTER" x "COLUMN NUMBER" x "VALUE" pairs.

I assume this is only one hint for a possible solution to the problem and certainly not the one to the most efficient one.

Any hints leading me towards solving this quiz would be greatly appreciated.

Thank you in advance.

Tabibito

 

[tabibito]

Apologies for the very delayed response and thank you so much, hiker95.

Today, I finally had the macro checked on other PCs and it worked perfectly fine on each of them.
I much appreciate all the effort - Thank you once more!

Best wishes,

tabibito

 

[hiker95]

tabibito,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.