Complicated Excel issue for me

[BuffaloBrian]

I'm attempting to format an excel sheet to be able to upload information to a program. In the extract I've pulled, if a person has more than one emergency contact it creates a second row with almost all of the same information as the previous row except the name of the contact, phone number, and email address. I would like to pull the secondary contact, phone number, and email address and add it to the end of the first row, essentially creating three new rows. I would then like to delete any rows that I have pulled that information from.  Is this possible with formula or two. I can't manually do this because the file has over 24,000 rows.

Any assistance would be helpful.

[#2]

@BuffaloBrian 

Perhaps Power Query could help, implementation slightly depends on do you always have one secondary row, or that could be no one or few of them. Idea is to generate ID (index) which will be the same for main and secondary row(s), after that group on it or merge table with all main rows with the table which is filtered for secondary rows only.

[#3]

There is not always a secondary row. If the person does not have a secondary contact the next row is the next person in my spreadsheet.

[#4]

@BuffaloBrian 

That's solvable. Main question do you consider Power Query as an option or not. If yes, better if you submit small sample file with different variants for the secondary rows.

[#5]

@BuffaloBrian 

Another option could be VBA, but that's not my territory.

[#6]

@BuffaloBrian  I think you can create a new list on another sheet using a set of formulas:

first pull the UNIQUE() rows based on the original persons name and information (everything up to the emergency contact info)

next use a LOOKUP function to pull the 1st row of emergency contact info 

last use a combination of COUNTIF (determine if there are 2 rows) and INDEX()-MATCH()+1 to find the secondary Emergency contact info.  

This would be easier to demo in an actual sheet with your non-personal info in it but I have attached a sample I created.

Note I used "Address" as a unique value in the list but if you don't have a unique field (i.e. SS#, ID#, etc...) you could create a temp field where you concatenate multiple fields to create that unique field (example shown in sheet) or create a more complicated formula.

[#7]

@mtarler 

Maybe, with assumption that source data as in your sample

My guess it looks like

but better to have the sample