asked on
Vba Macro to transform Table data format
Dear Experts,
Anyone can help me for a macro code in excel to transform data like below
Name Detail1 Detail2 Detail3
ABC A A1,A2 B1
DEF A C1 C2,C3
GHI C D1 D2
to below
Name Detail1 Detail2 Detail3
ABC A A1 B1
ABC A A2 B1
DEF A C1 C2
DEF A C1 C3
GHI C D1 D2
Thank you
Regards,
Fauzie
Anyone can help me for a macro code in excel to transform data like below
Name Detail1 Detail2 Detail3
ABC A A1,A2 B1
DEF A C1 C2,C3
GHI C D1 D2
to below
Name Detail1 Detail2 Detail3
ABC A A1 B1
ABC A A2 B1
DEF A C1 C2
DEF A C1 C3
GHI C D1 D2
Thank you
Regards,
Fauzie
VBAMicrosoft ExcelMicrosoft Office
Last Comment
Fauzie Bachri
ASKER
Hi Martin,
Thank you for the solution, apology i missed to mention that in my real data, the data with comma separated is flexible
currently your code will only separate the first 2 data, so for data like below
will be transformed to this
while it should be like this
Really appreciate if your code can be modified so it can transform the data correctly
Thank you in advance for your help
Regards,
Fauzie
Thank you for the solution, apology i missed to mention that in my real data, the data with comma separated is flexible
currently your code will only separate the first 2 data, so for data like below
| Name | Detail1 | Detail2 | Detail3 |
| N1 | A1 | A1, A2, A3 | A1, A2, A3 |
| N2 | A1 | A1 | A2 |
will be transformed to this
| Name | Detail1 | Detail2 | Detail3 |
| N1 | A1 | A1 | A1 |
| N1 | A1 | A1 | A2 |
| N1 | A1 | A2 | A1, A2, A3 |
| N2 | A1 | A1 | A2 |
while it should be like this
| Name | Detail1 | Detail2 | Detail3 |
| N1 | A1 | A1 | A1 |
| N1 | A1 | A1 | A2 |
| N1 | A1 | A1 | A3 |
| N1 | A1 | A2 | A1 |
| N1 | A1 | A2 | A2 |
| N1 | A1 | A2 | A3 |
| N1 | A1 | A3 | A1 |
| N1 | A1 | A3 | A2 |
| N1 | A1 | A3 | A3 |
| N2 | A1 | A1 | A2 |
Really appreciate if your code can be modified so it can transform the data correctly
Thank you in advance for your help
Regards,
Fauzie
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you very much Martin, your VBA code works flawlessly. its save a lot of time to transform my data :)
You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Distinguished Expert in Excel 2018
Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
Experts Exchange Top Expert VBA 2018 to 2020
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Distinguished Expert in Excel 2018
Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
Experts Exchange Top Expert VBA 2018 to 2020
ASKER
Thank you
29214632.xlsm