Transpose, Data cleaning methods

Natalie Hamilton
Natalie Hamilton used Ask the Experts™
on
I need help with data cleaning. I get these excel sheets (sorry unable to share) one column will have multiple entries in it like 11111-EXAM, 2222- EXAM, 333-EXAM so I do a text to column to break it out but then I need to put it back int o a singular column, so transpose again. The issue is the adjacent columns, I need to fill down the rows with the information that corresponds to that entry.

Rows look like this:
11111-EXAM, 2222- EXAM, 333-EXAM  |   example entry | more information | etc.

and I need it to look like this without the pain of having to copy and paste over and over:
11111-EXAM | example entry | more information | etc.
2222- EXAM | example entry | more information | etc.
333-EXAM | example entry | more information | etc.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may use Power Query to transform the data in the desired format. That seems to be an easy task in Power Query, all you need is to split the column with comma delimiter and split it into Rows.
Tom FarrarConsultant

Commented:
If you are familiar with Power Query (aka Get and Transform) functionality in Excel you can do it rather easily.  See example file.
EE.xlsx
Natalie Hamilton

Author

Commented:
Each cell has a different amount of entries like:

11111-EXAM, 2222- EXAM, 333-EXAM  |   example entry | more information | etc.
11111-EXAM, 2222- EXAM, 333-EXAM  2222- EXAM, 333-EXAM |   example entry | more information | etc
11111-EXAM |   example entry | more information | etc

So some columns will be blank when it's split and that impacts how to put them back into rows and filling correctly the adjacent cells
Natalie Hamilton

Author

Commented:
I'm in Power Query, but having difficulty understanding how to the columns back into rows and to fill with the correct adjacent field?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may follow the steps I took to transform the data in the desired format by watching this short demo.
PQ_Demo.mp4
PQ_Example.xlsx
Natalie Hamilton

Author

Commented:
Awesome! Thank you so much, this was a time saver.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Natalie! Glad it worked as desired.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial