selective transpose and reduce rows

[shanmuganandan]

Cant post picture here, so i give the link.

I have a data like below,

[TABLE="width: 64"]
[TR]
[TD="class: xl81, width: 64"]http://website-ipoh.com/before.JPG[/TD]
[/TR]
[/TABLE]
[TABLE="width: 890"]
[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]
[/TR]
[/TABLE]
i also post the data here ( picture will be clearer. see link ).

[TABLE="width: 890"]
[TR]
[TD]container id[/TD]
[TD]SITE2[/TD]
[TD]OUTLET[/TD]
[TD]GENERIC[/TD]
[TD]article[/TD]
[TD]SEASON[/TD]
[TD]YEAR[/TD]
[TD]ARTICLE DESCRIPTION[/TD]
[TD]COLOR[/TD]
[TD]TYPE[/TD]
[TD]SIZE[/TD]
[TD]qty[/TD]
[/TR]
[TR]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] KHAKI[/TD]
[TD]MEN[/TD]
[TD]L[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205002[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] KHAKI[/TD]
[TD]MEN[/TD]
[TD]M[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205002[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] KHAKI[/TD]
[TD]MEN[/TD]
[TD]S[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205002[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] KHAKI[/TD]
[TD]MEN[/TD]
[TD]XL[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4010009281993[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531002[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD] LT BLUE[/TD]
[TD]LADIES[/TD]
[TD]4[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]4010009281993[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531003[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD] LT BLUE[/TD]
[TD]LADIES[/TD]
[TD]6[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]4010009282006[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531001[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD] LT BLUE[/TD]
[TD]LADIES[/TD]
[TD]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4010009282006[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531004[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD] LT BLUE[/TD]
[TD]LADIES[/TD]
[TD]8[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]4010009282006[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531005[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD] LT BLUE[/TD]
[TD]LADIES[/TD]
[TD]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]4010009282242[/TD]
[TD="align: right"]2044[/TD]
[TD]QUEENSBAY MALL[/TD]
[TD]PA20161710[/TD]
[TD]PA20161710001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN S/PANTS[/TD]
[TD] DK GREY[/TD]
[TD]MEN[/TD]
[TD]FREE[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD] OFF WHITE[/TD]
[TD]BOY [/TD]
[TD]2-3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754002[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD] OFF WHITE[/TD]
[TD]BOY [/TD]
[TD]4-5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754003[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD] OFF WHITE[/TD]
[TD]BOY [/TD]
[TD]6-7[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754004[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD] OFF WHITE[/TD]
[TD]BOY [/TD]
[TD]8-9[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754005[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD] OFF WHITE[/TD]
[TD]BOY [/TD]
[TD]10-11[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754006[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD] OFF WHITE[/TD]
[TD]BOY [/TD]
[TD]12-13[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]6

[/TD]
[/TR]
[/TABLE]


in the above table, size and their quantity in vertical format. thats why same container ID and other details repeating vertically. i need the size and quantity in horizontal format.


need to convert the data like below;

see data below ( alternatively can see picture in this link [TABLE="width: 64"]
[TR]
[TD="class: xl81, width: 64"]http://website-ipoh.com/after.JPG[/TD]
[/TR]
[/TABLE]

[TABLE="width: 1523"]
[TR]
[TD]
[TABLE="width: 1274"]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FREE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]XL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2-3[/TD]
[TD]4-5[/TD]
[TD]6-7[/TD]
[TD]8-9[/TD]
[TD]10-11[/TD]
[TD]12-13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]container id[/TD]
[TD]SITE2[/TD]
[TD]OUTLET[/TD]
[TD]GENERIC[/TD]
[TD]article[/TD]
[TD]SEASON[/TD]
[TD]YEAR[/TD]
[TD]ARTICLE DESCRIPTION[/TD]
[TD]COLOR[/TD]
[TD]TYPE[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] KHAKI[/TD]
[TD]MEN[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]4010009281993[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531002[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD] LT BLUE[/TD]
[TD]LADIES[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]4010009282242[/TD]
[TD="align: right"]2044[/TD]
[TD]QUEENSBAY MALL[/TD]
[TD]PA20161710[/TD]
[TD]PA20161710001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN S/PANTS[/TD]
[TD] DK GREY[/TD]
[TD]MEN[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD] OFF WHITE[/TD]
[TD]BOY [/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD] ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[/TABLE]
[TABLE="width: 1274"]
[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]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
[/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][/TD]
[TD][/TD]
[/TR]
[/TABLE]
[TABLE="width: 1274"]
[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]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
in this result, container id and their related details not repeating. quantity of the size's arranged horizontally. i dont need the size appear in the result. it is just an information on how to arrange the data.

sizes are

FREE

S,M,L,XL

2,4,6,8,10

2-3,4-5,6-7,8-9,10-11,12-13

28,29,30,31,32,33,34,36

thanks in advance

 

[NiMip]

I've based the lookup on the "Generic" column as that seems to be consistent for the article, whereas there are two different container IDs for the Pavilion ladies jeans.

Formula in K32 is =SUM(IF(($D$2:$D$24=$D32)*((ROW($K$2:$K$24)-1)=IFERROR(TRANSPOSE(MATCH(K$27:K$31,$K$2:$K$24,0)),0)),$L$2:$L$24))
You have to press CONTROL+SHIFT+ENTER for it to work - you should see {} appear around the formula if done correctly. You can then fill the formula across the other cells.


[TABLE="class: grid, width: 1279"]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]container id[/TD]
[TD]SITE2[/TD]
[TD]OUTLET[/TD]
[TD]GENERIC[/TD]
[TD]article[/TD]
[TD]SEASON[/TD]
[TD]YEAR[/TD]
[TD]ARTICLE DESCRIPTION[/TD]
[TD]COLOR[/TD]
[TD]TYPE[/TD]
[TD]SIZE[/TD]
[TD]qty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]KHAKI[/TD]
[TD]MEN[/TD]
[TD]L[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205002[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]KHAKI[/TD]
[TD]MEN[/TD]
[TD]M[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205002[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]KHAKI[/TD]
[TD]MEN[/TD]
[TD]S[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205002[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]KHAKI[/TD]
[TD]MEN[/TD]
[TD]XL[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]4010009281993[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531002[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD]LT BLUE[/TD]
[TD]LADIES[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]4010009281993[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531003[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD]LT BLUE[/TD]
[TD]LADIES[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]4010009282006[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531001[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD]LT BLUE[/TD]
[TD]LADIES[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]4010009282006[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531004[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD]LT BLUE[/TD]
[TD]LADIES[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]4010009282006[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531005[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD]LT BLUE[/TD]
[TD]LADIES[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]4010009282242[/TD]
[TD="align: right"]2044[/TD]
[TD]QUEENSBAY MALL[/TD]
[TD]PA20161710[/TD]
[TD]PA20161710001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN S/PANTS[/TD]
[TD]DK GREY[/TD]
[TD]MEN[/TD]
[TD]FREE[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD]OFF WHITE[/TD]
[TD]BOY[/TD]
[TD]2-3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754002[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD]OFF WHITE[/TD]
[TD]BOY[/TD]
[TD]4-5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754003[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD]OFF WHITE[/TD]
[TD]BOY[/TD]
[TD]6-7[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754004[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD]OFF WHITE[/TD]
[TD]BOY[/TD]
[TD]8-9[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754005[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD]OFF WHITE[/TD]
[TD]BOY[/TD]
[TD]10-11[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754006[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD]OFF WHITE[/TD]
[TD]BOY[/TD]
[TD]12-13[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/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][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26[/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][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FREE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]XL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2-3[/TD]
[TD]4-5[/TD]
[TD]6-7[/TD]
[TD]8-9[/TD]
[TD]10-11[/TD]
[TD]12-13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]container id[/TD]
[TD]SITE2[/TD]
[TD]OUTLET[/TD]
[TD]GENERIC[/TD]
[TD]article[/TD]
[TD]SEASON[/TD]
[TD]YEAR[/TD]
[TD]ARTICLE DESCRIPTION[/TD]
[TD]COLOR[/TD]
[TD]TYPE[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]4010009260820[/TD]
[TD="align: right"]2048[/TD]
[TD]EAST COAST MALL[/TD]
[TD]PA20144205[/TD]
[TD]PA20144205001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]KHAKI[/TD]
[TD]MEN[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]4010009281993[/TD]
[TD="align: right"]2041[/TD]
[TD]PAVILION[/TD]
[TD]PA20163531[/TD]
[TD]PA20163531002[/TD]
[TD]P011[/TD]
[TD="align: right"]2017[/TD]
[TD]LADIES S/JEANS[/TD]
[TD]LT BLUE[/TD]
[TD]LADIES[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]4010009282242[/TD]
[TD="align: right"]2044[/TD]
[TD]QUEENSBAY MALL[/TD]
[TD]PA20161710[/TD]
[TD]PA20161710001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN S/PANTS[/TD]
[TD]DK GREY[/TD]
[TD]MEN[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]4010010953476[/TD]
[TD="align: right"]1099[/TD]
[TD]BUKIT MERTAJAM[/TD]
[TD]PA20164754[/TD]
[TD]PA20164754001[/TD]
[TD]P010[/TD]
[TD="align: right"]2017[/TD]
[TD]KIDS BOY POLO TEE[/TD]
[TD]OFF WHITE[/TD]
[TD]BOY[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]4010009345046[/TD]
[TD="align: right"]2037[/TD]
[TD]BUKIT TINGGI[/TD]
[TD]PA20144204[/TD]
[TD]PA20144204001[/TD]
[TD]P007[/TD]
[TD="align: right"]2017[/TD]
[TD]MEN CAP[/TD]
[TD]ARMY GREEN[/TD]
[TD]MEN[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[/TABLE]

 

[shanmuganandan]

Hi
thanks for the formula.

this formula works only for few lines. Please check the file here.

http://website-ipoh.com/TRANSPOSE SIZE-2.xlsb

 

[NiMip]

Ah, you didn't have any repeats in the SIZE column in the original data you posted. I'll need to change the formula.

 

[NiMip]

This will do the job, but it's a bit heavy on the computer... takes a lot of calculation time.

=SUM(IF((Data!$L$2:$L$10383=$J6)*(ROW(Data!$P$2:$P$10383)-1)*(Data!$P$2:$P$10383=TRANSPOSE(K$1:K$5)),Data!$K$2:$K$10383))

hitting CONTROL+SHIFT+ENTER as before.

 

[shanmuganandan]

Hi NiMip,

it works. Thanks a lot.