Re-arranging products codes, description and UOMs in one row

[Michael Makotore]

Hi Guys.

Please help. I need a formula to help me arrange UOMs of the same product in one row but different columns. The order of UOMs in columns does not matter. See the file attached.

Thank you in advance.

Michael

 

[bosco_yip]

Try....

1] In G2, formula copy down :

=IFERROR(INDEX(A$2:A$12,MATCH(0,INDEX(COUNTIF(G$1:G1,A$2:A$12),0),0)),"")

2] In H2, formula copy down :

=IF(G2="","",VLOOKUP(G2,A$2:B$12,2,0))

3] In I2, formula copy across and down :

=IF($G2="","",IFERROR(INDEX($C$1:$C$12,AGGREGATE(15,6,ROW($C$1:$C$12)/($A$1:$A$12=$G2),COLUMNS($A$1:A$1))),""))

Regards
Bosco

 

[mithil1]

Hello Bosco,

Can you please explain how formula 1 & 3 is working ? I know its not my post but I found it quite interesting so wanted to understand more. I tried use Evaluate formula but I don't understand that.

 

[Michael Makotore]

Hi Bosco

This is interesting. The formula is working perfectly though challenging to grasp.
Thumps up man!!!!

Michael