08-23-2019 09:39 AM
08-23-2019 09:39 AM
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).
09-05-2019 05:27 AMSolution
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.
09-05-2019 07:33 AM
@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)!