Suppose a survey is done in which a participant indicates their name and gender, the countries to which they have traveled, and their rating of each. Suppose the Excel file has data looking like this (the commas indicating a separate column value and each line break being a different row):
Joe Smith, M, Germany, Above Average
Joe Smith, M, Italy, Excellent
Joe Smith, M, Australia, Average
Jane Jones, F, Canada, Below Average
Jack Green, M, USA, Below Average
Jack Green, M, Mexico, Excellent
Jack Green, M, Brazil, Above Average
Jack Green, M, Austria, Excellent
Jack Green, M, Switzerland, Average
Megan Brown, F, Ireland, Below Average
Megan Brown, F, Ukraine, Above Average
In order to do my analysis, I need each so that each row reflects the data of one person, looking like this:
Joe Smith, M, Germany, Above Average, Italy, Excellent, Australia, Average,
Jane Jones, F, Canada, Below Average
etc.
With a small data set like this, I can simply manually do it. However, this dataset has about 5000 rows with about 400 names, some with one row to their name and others with upwards of 5 to 6. This would take many hours to do.
My question is this: Is there a shortcut way where I can merge rows based on a value (like name)?
It's easy;
you need to generate a new column with=
=A1&", "& A2&", "&A3&", "&"A4 "
When are ready, then
copy many times like rules you have.
That's all.
- Badges
- Science method