Avatar of Natalie Hamilton
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.
Microsoft Excel* data cleaningMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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.
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

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
Avatar of Natalie Hamilton
Natalie Hamilton

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
Avatar of Natalie Hamilton

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of Natalie Hamilton

ASKER

Awesome! Thank you so much, this was a time saver.
You're welcome Natalie! Glad it worked as desired.
Microsoft Excel
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo