Merging UnEven Names in Power Query

[montyfern]

Greetings!

Would anyone out there be so kind as to tell me how to merge uneven names?
For example:
File1 has 3000 rows or names.
File2 has 4000 rows of names. Customer wants the last name and all its' records to merge to one file but there's an uneven amount of names like this:

Attached are two tables. Not including the heading, File-Summary contains 3042 records, and File-Unique contains 2658 records. Basically, I’m trying to combine both sheets into a 3 sheet by Last Name and First Name. However, the names may not be in the same forms, although it’s the same person. Can excel match in this type of situation?

Examples.

	Last	First
PAT_Summary sheet 5-6-19	Adu-Mohsen
Pat_Unique PIs 5-6-19	ADU MOHSEN
PAT_Summary sheet 5-6-19		Ala Le G
Pat_Unique PIs 5-6-19		ALA'LE G.

<tbody>
</tbody>

I've tried merging, appending, merging columns, custom columns, importing from a folder so I can expand/combine/load/edit but am failing. Maybe this is vlookup territory? Please don't say macros...not real great with those.

Many thanks!

 

[sandy666]

post link(s) to representative example shared files and expected result.

 

[montyfern]

Dear sandy666, will do. Asked client for requisite file; please stay tuned. Do you want me to email you or use DropBox? Thanks!

 

[sandy666]

Dropbox, Onedrive, Googledrive , whatever you want

 

[montyfern]

https://netorg834590-my.sharepoint....be3c74c56648c3a6cab75e33b89ea6&csf=1&e=b1hOls

 

[montyfern]

https://netorg834590-my.sharepoint....NIpsq3XjO4nqYB5OmtbAEx585Sdjqc2ubwUw?e=qbWQkx

 

[montyfern]

Dropbox, Onedrive, Googledrive , whatever you want

Pls. let me know if this link didn't work. Thanks~

 

[sandy666]

links are to the same file and both are ok
So...
you want proper last and first name only or with the data?

btw. I don't use formula but PowerQuery (Get&Transform) so you need to check your excel contain it.

 

[sandy666]

Ops, I was blind about title

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Last[/td][td=bgcolor:#70AD47]First[/td][td=bgcolor:#70AD47]SIC Code[/td][td=bgcolor:#70AD47]SIC $[/td][td=bgcolor:#70AD47]Weighted RCR[/td][td=bgcolor:#70AD47]Pubs without RCR [/td][td=bgcolor:#70AD47]Mean RCR[/td][td=bgcolor:#70AD47]Median RCR[/td][td=bgcolor:#70AD47]No. of PIIDs [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Aaa[/td][td=bgcolor:#E2EFDA]Bba[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

551471​

[/td][td=bgcolor:#E2EFDA]

119.4256061​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

1.706080088​

[/td][td=bgcolor:#E2EFDA]

1.176092505​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Aab[/td][td]Bbb[/td][td]001[/td][td]

274451​

[/td][td]

152.7387133​

[/td][td]

8​

[/td][td]

1.909233916​

[/td][td]

1.402388453​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Aac[/td][td=bgcolor:#E2EFDA]Bbc[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

66.35654759​

[/td][td=bgcolor:#E2EFDA]

10​

[/td][td=bgcolor:#E2EFDA]

2.3698767​

[/td][td=bgcolor:#E2EFDA]

1.347082734​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Aad[/td][td]Bbd[/td][td]003[/td][td]

1287739​

[/td][td]

241.0645792​

[/td][td]

10​

[/td][td]

2.274194143​

[/td][td]

0.670348793​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Abe[/td][td=bgcolor:#E2EFDA]Jun-Ichi[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

169.2167406​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

1.581464865​

[/td][td=bgcolor:#E2EFDA]

1.219055414​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Abel[/td][td]Kristina[/td][td][/td][td][/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Cca[/td][td=bgcolor:#E2EFDA]Dda[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

82500​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ccb[/td][td]Margret[/td][td]001[/td][td]

354085​

[/td][td]

2.657554865​

[/td][td]

0​

[/td][td]

1.328777432​

[/td][td]

1.328777432​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ccc[/td][td=bgcolor:#E2EFDA]Erin[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

77886​

[/td][td=bgcolor:#E2EFDA]

100.3861262​

[/td][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]

1.930502427​

[/td][td=bgcolor:#E2EFDA]

1.621542573​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ccc[/td][td]Joella[/td][td]001[/td][td]

44044​

[/td][td]

5.333346404​

[/td][td]

4​

[/td][td]

0.761906629​

[/td][td]

0.339095891​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ccc[/td][td=bgcolor:#E2EFDA]Lisa[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

303688​

[/td][td=bgcolor:#E2EFDA]

15.6703706​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

0.746208124​

[/td][td=bgcolor:#E2EFDA]

0.440338343​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ccc[/td][td]Robert[/td][td][/td][td]

0​

[/td][td]

1165.528692​

[/td][td]

12​

[/td][td]

4.332820417​

[/td][td]

0.972700894​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Eea[/td][td=bgcolor:#E2EFDA]Brandon[/td][td=bgcolor:#E2EFDA]005[/td][td=bgcolor:#E2EFDA]

33184​

[/td][td=bgcolor:#E2EFDA]

13.19227229​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

1.319227229​

[/td][td=bgcolor:#E2EFDA]

0.663325042​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dda[/td][td]Irwin[/td][td][/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

next time try to prepare real names not aaa aac etc... I got a squint

 

[sandy666]

I realized that I made a small mistake in the previous table so here is proper table (I hope)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Last[/td][td=bgcolor:#70AD47]First[/td][td=bgcolor:#70AD47]SIC Code[/td][td=bgcolor:#70AD47]SIC $[/td][td=bgcolor:#70AD47]Index[/td][td=bgcolor:#70AD47]Weighted RCR[/td][td=bgcolor:#70AD47]Pubs without RCR [/td][td=bgcolor:#70AD47]Mean RCR[/td][td=bgcolor:#70AD47]Median RCR[/td][td=bgcolor:#70AD47]No. of PIIDs [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Aaa[/td][td=bgcolor:#E2EFDA]Bba[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

551471​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

119.4256061​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

1.706080088​

[/td][td=bgcolor:#E2EFDA]

1.176092505​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Aab[/td][td]Bbb[/td][td]001[/td][td]

274451​

[/td][td]

1​

[/td][td]

152.7387133​

[/td][td]

8​

[/td][td]

1.909233916​

[/td][td]

1.402388453​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Aac[/td][td=bgcolor:#E2EFDA]Bbc[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

66.35654759​

[/td][td=bgcolor:#E2EFDA]

10​

[/td][td=bgcolor:#E2EFDA]

2.3698767​

[/td][td=bgcolor:#E2EFDA]

1.347082734​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Aad[/td][td]Bbd[/td][td]003[/td][td]

1287739​

[/td][td]

3​

[/td][td]

241.0645792​

[/td][td]

10​

[/td][td]

2.274194143​

[/td][td]

0.670348793​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Abe[/td][td=bgcolor:#E2EFDA]Jun-Ichi[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]

169.2167406​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

1.581464865​

[/td][td=bgcolor:#E2EFDA]

1.219055414​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Abel[/td][td]Kristina[/td][td][/td][td][/td][td]

5​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Cca[/td][td=bgcolor:#E2EFDA]Dda[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

82500​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ccb[/td][td]Marge[/td][td]001[/td][td]

354085​

[/td][td]

7​

[/td][td]

2.657554865​

[/td][td]

0​

[/td][td]

1.328777432​

[/td][td]

1.328777432​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ccc[/td][td=bgcolor:#E2EFDA]Erin[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

77886​

[/td][td=bgcolor:#E2EFDA]

8​

[/td][td=bgcolor:#E2EFDA]

100.3861262​

[/td][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]

1.930502427​

[/td][td=bgcolor:#E2EFDA]

1.621542573​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ccc[/td][td]Joella[/td][td]001[/td][td]

44044​

[/td][td]

9​

[/td][td]

5.333346404​

[/td][td]

4​

[/td][td]

0.761906629​

[/td][td]

0.339095891​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ccc[/td][td=bgcolor:#E2EFDA]Lisa[/td][td=bgcolor:#E2EFDA]003[/td][td=bgcolor:#E2EFDA]

303688​

[/td][td=bgcolor:#E2EFDA]

10​

[/td][td=bgcolor:#E2EFDA]

15.6703706​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

0.746208124​

[/td][td=bgcolor:#E2EFDA]

0.440338343​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ccc[/td][td]Robert[/td][td][/td][td]

0​

[/td][td]

11​

[/td][td]

1165.528692​

[/td][td]

12​

[/td][td]

4.332820417​

[/td][td]

0.972700894​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Dda[/td][td=bgcolor:#E2EFDA]Irwin[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

12​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Eea[/td][td]Brandon[/td][td]005[/td][td]

33184​

[/td][td]

13​

[/td][td]

13.19227229​

[/td][td]

0​

[/td][td]

1.319227229​

[/td][td]

0.663325042​

[/td][td]

1​

[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Last", type text}, {"First", type text}, {"SIC Code", type text}}),
    Capitalize = Table.TransformColumns(#"Changed Type",{{"Last", Text.Proper, type text}, {"First", Text.Proper, type text}}),
    Trim = Table.TransformColumns(Capitalize,{{"Last", Text.Trim, type text}, {"First", Text.Trim, type text}}),
    Index = Table.AddIndexColumn(Trim, "Index", 0, 1)
in
    Index

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Last", type text}, {"First", type text}, {"Weighted RCR", type number}, {"Pubs without RCR ", Int64.Type}, {"Mean RCR", type number}, {"Median RCR", type number}, {"No. of PIIDs ", Int64.Type}}),
    Capitalize = Table.TransformColumns(Type,{{"Last", Text.Proper, type text}, {"First", Text.Proper, type text}}),
    Trim = Table.TransformColumns(Capitalize,{{"Last", Text.Trim, type text}, {"First", Text.Trim, type text}}),
    Index = Table.AddIndexColumn(Trim, "Index", 0, 1)
in
    Index

// Merge1
let
    Source = Table.NestedJoin(Table1,{"Index"},Table2,{"Index"},"Table2",JoinKind.FullOuter),
    Expand = Table.ExpandTableColumn(Source, "Table2", {"Last", "First", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}, {"Last.1", "First.1", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}),
    IF1 = Table.AddColumn(Expand, "Custom", each if [Last] = [Last.1] then [Last] else if [Last] = null then [Last.1] else [Last]),
    IF2 = Table.AddColumn(IF1, "Custom.1", each if [First] = [First.1] then [First] else if [First] = null then [First.1] else [First]),
    RC = Table.RemoveColumns(IF2,{"Last", "First", "Last.1", "First.1"}),
    Extract = Table.TransformColumns(RC, {{"Custom.1", each Text.BeforeDelimiter(_, "_"), type text}}),
    Reorder = Table.ReorderColumns(Extract,{"Custom", "Custom.1", "SIC Code", "SIC $", "Index", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}),
    Ren = Table.RenameColumns(Reorder,{{"Custom", "Last"}, {"Custom.1", "First"}})
in
    Ren[/SIZE]