Excel query

[NittanSethi365]

I have downloaded the below file (no formulae exist within this, values only) but need to quickly change this to just display all of the information in one row. The first five columns need to still show the same information (Physical exam, procedure, study team, 1, 1) but the rest of the columns need to show the consolidated values, eg column six to show a value of £1,224.

Can someone please help ?

Thanks

Physical examination	Procedure	Study Team	1.00	1	£1,111.00	N/A	£222.20	£341.57	£1,674.77
Physical examination	Procedure	Study Team	1.00	1	£   113.00	N/A	£  22.60	£  34.74	£   170.34

[#2 · Accepted solution]

@NittanSethi365 I suspect that your actual file will come with many more rows and not always two row that belong together. And probably not even sorted neatly.

In that case, it would be worth looking into Power Query (PQ).  The attached workbook contains a (quick-and-dirty) working example based on your data but slightly modified to demonstrate the power of PQ.

[#3]

@Riny_van_Eekelen 

Hi Riny,

Thanks for taking the time to reply, appreciate it and you are absolutely right about the file. It is always downloaded containing several sets of repeated rows which need to be converted into single rows but with the cells containing £ values added together before I can then start using it. I haven't really used Power Query before but suspected that this may be the answer, so will look into it. I'd be grateful for any other responses too

Thanks

Nittan