Help with organising table

[staceyalana]

Hi,
I have a raw data table that has been exported from our system. What I need to do is organise the emergency contacts from this.
The raw data is as follows:
Column A = surname
Column B = first name
Column I = Emergency Contact (listed as 1st Emergency, 2nd Emergency, etc.)
Column J = emergency first name
Column K = emergency last name.

If the client has multiple contacts for the above, the client is duplicated.
Bloggs Joe, 1st Emergency, Joanne Bloggs
Bloggs Joe, 2nd Emergency, Joy Bloggs

What I need to do is create a new spreadsheet from the above in the following manner:
Rows headings:
Last name, first name, 1st Emergency Contact, 2nd Emergency Contact, etc.

and have the clients names in columns A and B, emergency contact names i.e.:

Surname First name 1st Emergency 2nd Emergency
Bloggs Joe Joanne Bloggs Joy Bloggs

I think I should be using an IF and/or VLOOKUP, but I cannot see to get the formula right.

I would appreciate any help with this.

 

[KRice]

I would do this with Power Query to merge the emergency contact names (first & last) and then pivot the table so that the emergency contacts appeared on the same row as the client. Once this is setup, changes in the source table will be reflected in the results table by executing Data > Refresh All > Refresh All. The following M code performs the transformation. This code is already set up in the file available below, although if column headings change the code will need some minor revisions.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Surname", "First Name", "Emergency Contact", "Emergency First Name", "Emergency Last Name"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Emergency First Name", "Emergency Last Name"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Emerg Contact Name"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"Emergency Contact"]), "Emergency Contact", "Emerg Contact Name"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1st Emergency", "1st Emergency Contact"}, {"2nd Emergency", "2nd Emergency Contact"}, {"Surname", "Last Name"}})
in
    #"Renamed Columns"

...producing this result:

Last Name	First Name	1st Emergency Contact	2nd Emergency Contact
Bloggs	Joe	Joanne Bloggs	Joy Bloggs
Jones	John	Judy Jones	Jessica Jones
Smith	Leo	John Garcia

...from this source table:

MrExcel_20220912_staceyalana.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Surname	First Name	Column1	Column2	Column3	Column4	Column5	Column6	Emergency Contact	Emergency First Name	Emergency Last Name
2	Bloggs	Joe							1st Emergency	Joanne	Bloggs
3	Bloggs	Joe							2nd Emergency	Joy	Bloggs
4	Jones	John							1st Emergency	Judy	Jones
5	Jones	John							2nd Emergency	Jessica	Jones
6	Smith	Leo							1st Emergency	John	Garcia
Raw

If you are interested in exploring this option, the file I used is available here:

MrExcel_20220912_staceyalana.xlsx

Shared with Dropbox

www.dropbox.com

Let me know if you have any questions or need some help getting this running on your system.

 

[staceyalana]

I would do this with Power Query to merge the emergency contact names (first & last) and then pivot the table so that the emergency contacts appeared on the same row as the client. Once this is setup, changes in the source table will be reflected in the results table by executing Data > Refresh All > Refresh All. The following M code performs the transformation. This code is already set up in the file available below, although if column headings change the code will need some minor revisions.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Surname", "First Name", "Emergency Contact", "Emergency First Name", "Emergency Last Name"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Emergency First Name", "Emergency Last Name"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Emerg Contact Name"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"Emergency Contact"]), "Emergency Contact", "Emerg Contact Name"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1st Emergency", "1st Emergency Contact"}, {"2nd Emergency", "2nd Emergency Contact"}, {"Surname", "Last Name"}})
in
    #"Renamed Columns"

...producing this result:

Last Name	First Name	1st Emergency Contact	2nd Emergency Contact
Bloggs	Joe	Joanne Bloggs	Joy Bloggs
Jones	John	Judy Jones	Jessica Jones
Smith	Leo	John Garcia

...from this source table:

MrExcel_20220912_staceyalana.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Surname	First Name	Column1	Column2	Column3	Column4	Column5	Column6	Emergency Contact	Emergency First Name	Emergency Last Name
2	Bloggs	Joe							1st Emergency	Joanne	Bloggs
3	Bloggs	Joe							2nd Emergency	Joy	Bloggs
4	Jones	John							1st Emergency	Judy	Jones
5	Jones	John							2nd Emergency	Jessica	Jones
6	Smith	Leo							1st Emergency	John	Garcia
Raw

If you are interested in exploring this option, the file I used is available here:

MrExcel_20220912_staceyalana.xlsx

Shared with Dropbox

www.dropbox.com

Let me know if you have any questions or need some help getting this running on your system.

Thank you so much for your help, I would never have come up with that, and it was a lot easier as well.

I will have to explore this new option further if I have any more queries on the like.

Stacey

 

[KRice]

You're welcome...I'm happy to help.