Natalie Hamilton
asked on
Transpose, Data cleaning methods
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.
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.
Last Comment
Subodh Tiwari (Neeraj)
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.
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
EE.xlsx
ASKER
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
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
ASKER
I'm in Power Query, but having difficulty understanding how to the columns back into rows and to fill with the correct adjacent field?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS 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
Awesome! Thank you so much, this was a time saver.
You're welcome Natalie! Glad it worked as desired.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
