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

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