Summarising data into one row

[Tim2773]

Hi

Hopefully someone can help with this one.

I have a list of inspection data that details unit name, date and result. This is run over a designated time period and the output displays all inspections that fall within that time period, row by row (sheet 1)

I would like the data summarised to be able to see the last three inspections by unit number, as a summary on one row. (sheet 2)

Any ideas on what formula i need to accomplish this?

Many thanks
Tim.

[AliGW]

Is this still for Excel 2016, or something newer?

[Tim2773]

Hi AliGW

Yes, still 2016

Thanks
Tim

[sandy666]

with Power Query

Unit	Date.1	Result.1	Date.2	Result.2	Date.3	Result.3
1	11/07/2022	PASS	15/12/2019	PASS	28/04/2019	WARNING
2	09/02/2020	FAIL	15/12/2019	WARNING	06/10/2019	PASS
3	02/11/2022	FAIL	28/07/2022	PASS	20/12/2021	WARNING

[Tim2773]

Thanks Sandy666

I'm sure that will do it, never even heard of Power Query!

I've just had a quick play, any pointers on how to move the data into the format you have, i don't know where to start!

The data i will be using has around 1000 units (not just the 3 in the example).

Many thanks
Tim

[sandy666]

on XL2016 it is Get&Transform

as you can see I changed your source data into Excel Table
then Data tab - From Table
it will open Power Query Editor where you can do what you want, transform, filter, join, appen and so on...

if you want to see in my file just Data tab - Show Queries and double click on the table you will see on the right side (it will open Power Query Editor), then you can check each step (again on the right side) by single click on each step

Edit:
for more info about Power Query: Learn Power Query

[Tim2773]

Sandy666

That's great, exactly what i'm after.

Many thanks.
Tim

[sandy666]

My pleasure