Concatenating according to row colours

[PaulaSpinola]

I have a original pdf file with individual level information. When converting data from pdf into excel, some rows concerning the same individual were mistakenly broken into two rows (e.g. rows 13 & 14, rows 22 & 23, ...). Thankfully adjacent rows with information on the same individual have same colour (thus I can somehow use Interior.Color inside a macro). So basically I need to concatenate information in adjacent rows which have the same colour into a unique row. Note that the way that rows should be concatenated depend on how the information was broken down. For instance, information on row 14 should be added in row 13 and information in row 22 should be added in row 23. Ultimately, rows 14 and 22 should be deleted after the information is added in the respective adjacent rows. Finally, the spreadsheet also contains empty rows which I would like to delete (e.g. rows 31, 75). I would be grateful if someone could help me out?

Please find the spreadsheet attached (as well as the original pdf file).

Many thanks

Paula

[#2]

@PaulaSpinola This is very easy using Data, From Table.

[#3 · Accepted solution]

Hello @Jan Karel Pieterse I am afraid there are issues in the names of the institutions. For instance, the first one is "FUNDAÇÃO HOSPITAL ADRIANO JORGE UNIVERSIDADE DO ESTADO DO
AMAZONAS" and in the file you sent bellow it is "FUNDAÇÃO HOSPITAL ADRIANO JORGE UNIVERSIDADE DO ESTADO DO AMAZONAS ONCOLOGIA DO ESTADO DO AMAZONAS".

I think I managed to get around with a VBA code that identifies the colours of each row and some subsequent formulas. Please find attached.

[#4]

Perhaps this one does work? (I didn't take the trouble of renaming the column names)

[#5]

@Jan Karel Pieterse it still does not solve the issue. For instance, in cell F6 it says "FUNDAÇÃO CENTRO DE CONTROLE DE" whereas it should say "FUNDAÇÃO CENTRO DE CONTROLE DE ONCOLOGIA DO ESTADO DO AMAZONAS" which was split in 2 rows in the original spreadsheet.

The issue is more complicated than it seems :\

But don't worry, I already managed to get around (see spreadsheet sent in my last post)! 

[#6]

@PaulaSpinola  OK, in that case I won't try a new version. I'm sorry my solution was inaccurate, shouldn't have been!