THE BORE
General => The Superdeep Borehole => Topic started by: Tieno on April 29, 2010, 03:50:49 PM
-
I got an excel list that's build like this
Workshop 1
1, Name Lastname
2. Name Lastname
etc
Workshop 2
3 Name Lastname (name and lastname are different cells)
etc
Workshop 3
etc etc
I need to reformat it to this
1. Name Lastname Workshop 1 Workshop 2 Workshop 3 Workshop 4
2. Name Lastname Workshop 1 Workshop 4
...
All different cells (could live with name and lastname not being different cells)
Caveat, people can attend multiple workshops so I need to get rid of this in the final list.
Anyone know how I can speed this up, instead of doing it manually. I don't know a way past the 'duplicates'.
-
Are they actually called "Workshop 1", etc., or are you using that as a placeholder? if a placeholder, are those the only rows with only one cell?
Also, do the "1." and "2." get their own cells or did you put that in for illustration?
"Caveat, people can attend multiple workshops so I need to get rid of this in the final list."
not quite sure what you mean here -- it looks like your reformatted example accounts for multiple workshops?
-
Make the first column of every unique name.*edit:Next to the columns for each workshop, copy the name of the workshop down next to the names of the attendees. Then do a vlookup of each name in a separate column for each workshop. This will tell you who signed up for what. Insert new columns next to each of the lookup columns that you just created, and make a logical statement like so: if(A2=A1,Workshop1," "). Do this for all of the columns. Create a final column that concatenates the column with all of the unique names with the column that has the logical statement. (easier done than said :teehee)
-
huh? vlookup for each name in a seperate column, after I copied down the workshop name, doesn't that just make two columns with the same workshop name?
don't understand
-
If you have the list like this:
Workshop 1
1, Name Lastname
2. Name Lastname
etc
Simply copy "Workshop 1" down the cells to the right of the names that are in that workshop. This is your return value for the vlookup that I described.