Forum Discussion
Combine and align columns of data
Hi,
I have multiple worksheets, each with two columns of data. The first column on the left is names of students. The second column is their grade. I would like to combine all worksheets into one, maintaining the left column of student names and adding their three grade columns in the next columns over. I can't simply copy and paste them into one the names of students aren't aligned. I've tried the consolidate function, but the SUM function adds all their grades up into one column. I want to maintain the separate columns of grades but have that data aligned to the appropriate student name.
clamb2060 Easiest with Power Query (PQ). Are you familiar with that?
The attached workbook demonstrates a PQ solution. It's quick-and-dirty and far from perfect. Created structured tables from each of the score lists and queried them.
Your data is not clean as it contains scores from (presumably) the same persons but with their names spelled slightly different.
For example:
Alexis R ( Alexis R ) vs. AlexisR ( Alexis R )
Elliot is verified ( Elliot L ) vs. Elliott is verified ( Elliot L )
and there are many more.
As the name between brackets seemed to be consistent, I used these to extract player names and create the table (in sheet Query1) with scores for each player.
As said, far from perfect, but I hope it gives you an idea of what is possible with PQ, in case you want to pursue this further. Otherwise, VLOOKUP or XlOOKUP will work as well, but you need to check and clean your data, and add a column with the V(X)LOOKUP formula, for every new score table you add. With PQ that goes automatic, provided all is set-up correctly.
3 Replies
clamb2060 Easiest with Power Query (PQ). Are you familiar with that?
The attached workbook demonstrates a PQ solution. It's quick-and-dirty and far from perfect. Created structured tables from each of the score lists and queried them.
Your data is not clean as it contains scores from (presumably) the same persons but with their names spelled slightly different.
For example:
Alexis R ( Alexis R ) vs. AlexisR ( Alexis R )
Elliot is verified ( Elliot L ) vs. Elliott is verified ( Elliot L )
and there are many more.
As the name between brackets seemed to be consistent, I used these to extract player names and create the table (in sheet Query1) with scores for each player.
As said, far from perfect, but I hope it gives you an idea of what is possible with PQ, in case you want to pursue this further. Otherwise, VLOOKUP or XlOOKUP will work as well, but you need to check and clean your data, and add a column with the V(X)LOOKUP formula, for every new score table you add. With PQ that goes automatic, provided all is set-up correctly.
Riny_van_Eekelen Solved! Thank you for your time.
