09-05-2020 06:45 AM
09-05-2020 06:45 AM
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.
09-05-2020 06:55 AM
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.
09-05-2020 07:05 AM
09-05-2020 01:38 PM
@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.