Hello, I have some data here that I would like to transpose based on the same value in between underscore of column D:
Based on the data above, this is how I would like the transpose. It start from column F to O in which I will need this region converted to CSV for DataMerging in InDD:
I want to mention that number of rows from the first data (Column A-D) is always changed. And I am planning to build this into VBA for frequent use. Hopefully someone can help with formula or VBA or any other excel features has to offer. Also I would like to mention that I am using Microsoft Excel:
Version 1908 (Build 11929.20648)
Office 365 MSO (16.0.11929.20436)
Semi-annual Channel.
I hope it makes sense. Thank you very much in advance for all your help and attention in advance.
| Book2 | ||||||
|---|---|---|---|---|---|---|
| A | C | D | ||||
| 1 | Number | Order Number | @Photo | |||
| 2 | 1111 | 1 | 1_1111_xxx.jpg | |||
| 3 | 12345 | 2 | 2_12345_xxx.jpg | |||
| 4 | 12345 | 3 | 3_12345_yyy.jpg | |||
| 5 | 2222 | 4 | 4_2222_xxx.jpg | |||
| 6 | 2222 | 5 | 5_2222_yyy.jpg | |||
| 7 | 2222 | 6 | 6_2222_zzz.jpg | |||
| 8 | 33333 | 30 | 30_33333_xxx.jpg | |||
| 9 | 33333 | 31 | 31_33333_yyy.jpg | |||
| 10 | 4321 | Pkg1 | Pkg1_4321_xxx.jpg | |||
| 11 | 4321 | Pkg2 | Pkg2_4321_yyy.jpg | |||
| 12 | 4321 | Pkg3 | Pkg3_4321_zzz.jpg | |||
| 13 | 4321 | Pkg4 | Pkg4_4321_aaa.jpg | |||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A13 | A2 | =SUBSTITUTE(MID(D2,FIND("_",D2,1)+1,5),"_","") |
| C2:C13 | C2 | =LEFT(D2,FIND("_",D2)-1) |
Based on the data above, this is how I would like the transpose. It start from column F to O in which I will need this region converted to CSV for DataMerging in InDD:
| Book2 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | C | D | E | F | G | H | I | J | K | L | M | N | O | ||||
| 1 | Number | Order Number | @Photo | Item | Order1 | Order2 | Order3 | Image1 | Image2 | Image3 | @Photo1 | @Photo2 | @Photo3 | ||||
| 2 | 1111 | 1 | 1_1111_xxx.jpg | 1111 | 1 | 1_1111_xxx.jpg | |||||||||||
| 3 | 12345 | 2 | 2_12345_xxx.jpg | 12345 | 2 | 3 | 2_12345_xxx.jpg | 3_12345_yyy.jpg | 2_12345_xxx.jpg | 3_12345_yyy.jpg | |||||||
| 4 | 12345 | 3 | 3_12345_yyy.jpg | 2222 | 4 | 5 | 6 | 4_2222_xxx.jpg | 5_2222_yyy.jpg | 6_2222_zzz.jpg | 4_2222_xxx.jpg | 5_2222_yyy.jpg | 6_2222_zzz.jpg | ||||
| 5 | 2222 | 4 | 4_2222_xxx.jpg | 33333 | 30 | 31 | 30_33333_xxx.jpg | 31_33333_yyy.jpg | 30_33333_xxx.jpg | 31_33333_yyy.jpg | |||||||
| 6 | 2222 | 5 | 5_2222_yyy.jpg | 4321 | Pkg1 | Pkg2 | Pkg3 | Pkg1_4321_xxx.jpg | Pkg2_4321_yyy.jpg | Pkg3_4321_zzz.jpg | Pkg1_4321_xxx.jpg | Pkg2_4321_yyy.jpg | Pkg3_4321_zzz.jpg | ||||
| 7 | 2222 | 6 | 6_2222_zzz.jpg | 4321 | Pkg4 | Pkg4_4321_aaa.jpg | Pkg4_4321_aaa.jpg | ||||||||||
| 8 | 33333 | 30 | 30_33333_xxx.jpg | ||||||||||||||
| 9 | 33333 | 31 | 31_33333_yyy.jpg | ||||||||||||||
| 10 | 4321 | Pkg1 | Pkg1_4321_xxx.jpg | ||||||||||||||
| 11 | 4321 | Pkg2 | Pkg2_4321_yyy.jpg | ||||||||||||||
| 12 | 4321 | Pkg3 | Pkg3_4321_zzz.jpg | ||||||||||||||
| 13 | 4321 | Pkg4 | Pkg4_4321_aaa.jpg | ||||||||||||||
Sheet1 | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A13 | A2 | =SUBSTITUTE(MID(D2,FIND("_",D2,1)+1,5),"_","") |
| C2:C13 | C2 | =LEFT(D2,FIND("_",D2)-1) |
I want to mention that number of rows from the first data (Column A-D) is always changed. And I am planning to build this into VBA for frequent use. Hopefully someone can help with formula or VBA or any other excel features has to offer. Also I would like to mention that I am using Microsoft Excel:
Version 1908 (Build 11929.20648)
Office 365 MSO (16.0.11929.20436)
Semi-annual Channel.
I hope it makes sense. Thank you very much in advance for all your help and attention in advance.
