Dear Friends,
I create a summary data vertical/column but source is row style.
Need your advise which formula I need.
details as per attached file.
Thanks in advance for your help.
Regards,
Wie
Dear Friends,
I create a summary data vertical/column but source is row style.
Need your advise which formula I need.
details as per attached file.
Thanks in advance for your help.
Regards,
Wie
if the layout is that consistent then one option would be:
D2: =INDEX(SHIP!$C$2:$H$4,CEILING(ROWS(A$2:A2)/6,1),1+(MOD(ROWS(A$2:A2)-1,6)))
copied down
D2=INDEX(SHIP!$C$2:$I$1000,MATCH($A2&LEFT($B2,LEN($B2)-3),INDEX(SHIP!$A$2:$A$1000&SHIP!$B$2:$B$1000,0),0),MATCH(RIGHT($B2,2),INDEX(TEXT(SHIP!$C$1:$I$1,"00"),0),0))
OR
D2=AGGREGATE(14,6,SHIP!$C$2:$I$1000/((SHIP!$B$2:$B$1000&TEXT(SHIP!$C$1:$I$1,"-00")=$B2)*(SHIP!$A$2:$A$1000=$A2)),1)
Try the above, copy and paste towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Another way. In D2 and filled down.Formula:Please Login or Register to view this content.
Dave
Hi, to all!
Another option could be:
=VLOOKUP(A2,SHIP!A$2:H$4,1+RIGHT(B2),)
Blessings!
Here is an alternate solution. With Power Query (which is an addin for Excel 2010 at no charge from MS), bring your table into the PQ Editor. Highlight the first two columns and Unpivot the remaining columns. Merge the second and third column with a dash (-) separator. Here is the Mcode that results from those steps.
Excel 2016 (Windows) 32 bitPlease Login or Register to view this content.
A B C 1P. O. NO Merged Value 2 1234AAAA-3 3 3 1234AAAA-4 3 4 1234AAAA-5 3 5 2345BBB-2 1 6 2345BBB-4 2 7 2345BBB-5 2 8 2345BBB-7 2 9 3456CCC-2 2 10 3456CCC-3 2 11 3456CCC-4 2 12 3456CCC-5 2 13 3456CCC-6 2 14 3456CCC-7 2
Sheet: Sheet1
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks all for your advise. this is work
You are welcome. Thank you for the feedback and marking your thread Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks