Jack has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Word Advanced course
Taking Minutes course
Office 365 End User course
Introduction to Management course
Merging Cells and data
Hi people,
Hoping someone can help me out here. Essentially, I have a spreadsheet exported from software which allows me to look at the peer review scores recorded for academic applications - the format of the data exported is across the same column headings, with each score (of three separate scores) separated into a new row.
I am am looking for a way to more speedily do what I have already done manually for a couple of the applications- migrate the scores in the 'Recorded Score' column to respective Score 1/Score 2 / Score 3 columns. The real spreadsheet is of considerable length and it will take me a long time to do this manually.
J
RE: Merging Cells and data
Hi Jack,
Thank you for the forum question.
You can use Excel’s Power Query or a combination of formulas to efficiently rearrange your data. Here’s a step-by-step guide using both methods:
Method 1: Using Power Query
Load Your Data into Power Query:
Select your data range and go to the Data tab.
Click on From Table/Range to load it into Power Query.
Transform the Data:
In Power Query, select the column with the scores.
Use the Group By feature:
Group by the unique identifier for each application (e.g., Application ID).
Create new columns for Score 1, Score 2, and Score 3, using the
All Rows option.
Expand the grouped rows to separate the scores into individual columns.
Load the Data Back:
Once you have the scores in the desired format, click Close & Load to bring the transformed data back into Excel.
Method 2: Using Excel Formulas
If you prefer not to use Power Query, you can use formulas:
Assuming Your Data is Structured Like This:
| Application ID | Recorded Score |
|----------------|----------------|
| 1 | 85 |
| 1 | 90 |
| 1 | 78 |
| 2 | 88 |
| 2 | 92 |
| 2 | 80 |
Create New Columns:
In a new sheet or next to your data, create headers for Score 1, Score 2, and Score 3.
Use the Following Formulas:
For Score 1 (assuming your data starts in A2):
=IFERROR(INDEX($B$2:$B$100, MATCH(1, ($A$2:$A$100=A2)*($C$2:$C$100=1), 0)), "")
For Score 2:
=IFERROR(INDEX($B$2:$B$100, MATCH(1, ($A$2:$A$100=A2)*($C$2:$C$100=2), 0)), "")
For Score 3:
=IFERROR(INDEX($B$2:$B$100, MATCH(1, ($A$2:$A$100=A2)*($C$2:$C$100=3), 0)), "")
Drag Down the Formulas:
Adjust the ranges as necessary and drag the formulas down to fill in the scores for all applications.
Final Touches
Make sure to adjust the ranges in the formulas to match your actual data.
If you have a large dataset, Power Query is usually more efficient.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Merging Cells and data
Hi Jack,
Thank you for the forum question.
You can use Excel’s Power Query or a combination of formulas to efficiently rearrange your data. Here’s a step-by-step guide using both methods:
Method 1: Using Power Query
Load Your Data into Power Query:
Select your data range and go to the Data tab.
Click on From Table/Range to load it into Power Query.
Transform the Data:
In Power Query, select the column with the scores.
Use the Group By feature:
Group by the unique identifier for each application (e.g., Application ID).
Create new columns for Score 1, Score 2, and Score 3, using the
All Rows option.
Expand the grouped rows to separate the scores into individual columns.
Load the Data Back:
Once you have the scores in the desired format, click Close & Load to bring the transformed data back into Excel.
Method 2: Using Excel Formulas
If you prefer not to use Power Query, you can use formulas:
Assuming Your Data is Structured Like This:
| Application ID | Recorded Score |
|----------------|----------------|
| 1 | 85 |
| 1 | 90 |
| 1 | 78 |
| 2 | 88 |
| 2 | 92 |
| 2 | 80 |
Create New Columns:
In a new sheet or next to your data, create headers for Score 1, Score 2, and Score 3.
Use the Following Formulas:
For Score 1 (assuming your data starts in A2):
=IFERROR(INDEX($B$2:$B$100, MATCH(1, ($A$2:$A$100=A2)*($C$2:$C$100=1), 0)), "")
For Score 2:
=IFERROR(INDEX($B$2:$B$100, MATCH(1, ($A$2:$A$100=A2)*($C$2:$C$100=2), 0)), "")
For Score 3:
=IFERROR(INDEX($B$2:$B$100, MATCH(1, ($A$2:$A$100=A2)*($C$2:$C$100=3), 0)), "")
Drag Down the Formulas:
Adjust the ranges as necessary and drag the formulas down to fill in the scores for all applications.
Final Touches
Make sure to adjust the ranges in the formulas to match your actual data.
If you have a large dataset, Power Query is usually more efficient.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
