Merging UnEven Names in Power Query

montyfern

Board Regular
Joined
Oct 12, 2017
Messages
65
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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Dear sandy666, will do. Asked client for requisite file; please stay tuned. Do you want me to email you or use DropBox? Thanks!
 
Upvote 0
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.
 
Last edited:
Upvote 0
Ops, I was blind about title :)

is that what you want?

LastFirstSIC CodeSIC $Weighted RCRPubs without RCR Mean RCRMedian RCRNo. of PIIDs
AaaBba003
551471​
119.4256061​
6​
1.706080088​
1.176092505​
1​
AabBbb001
274451​
152.7387133​
8​
1.909233916​
1.402388453​
1​
AacBbc
0​
66.35654759​
10​
2.3698767​
1.347082734​
1​
AadBbd003
1287739​
241.0645792​
10​
2.274194143​
0.670348793​
1​
AbeJun-Ichi
169.2167406​
6​
1.581464865​
1.219055414​
1​
AbelKristina
0​
0​
1​
CcaDda003
82500​
0​
0​
1​
CcbMargret001
354085​
2.657554865​
0​
1.328777432​
1.328777432​
1​
CccErin003
77886​
100.3861262​
4​
1.930502427​
1.621542573​
1​
CccJoella001
44044​
5.333346404​
4​
0.761906629​
0.339095891​
1​
CccLisa003
303688​
15.6703706​
0​
0.746208124​
0.440338343​
1​
CccRobert
0​
1165.528692​
12​
4.332820417​
0.972700894​
2​
EeaBrandon005
33184​
13.19227229​
0​
1.319227229​
0.663325042​
1​
DdaIrwin
0​

next time try to prepare real names not aaa aac etc... I got a squint :rolleyes:
 
Last edited:
Upvote 0
I realized that I made a small mistake in the previous table so here is proper table (I hope)

LastFirstSIC CodeSIC $IndexWeighted RCRPubs without RCR Mean RCRMedian RCRNo. of PIIDs
AaaBba003
551471​
0​
119.4256061​
6​
1.706080088​
1.176092505​
1​
AabBbb001
274451​
1​
152.7387133​
8​
1.909233916​
1.402388453​
1​
AacBbc
0​
2​
66.35654759​
10​
2.3698767​
1.347082734​
1​
AadBbd003
1287739​
3​
241.0645792​
10​
2.274194143​
0.670348793​
1​
AbeJun-Ichi
4​
169.2167406​
6​
1.581464865​
1.219055414​
1​
AbelKristina
5​
0​
0​
1​
CcaDda003
82500​
6​
0​
0​
1​
CcbMarge001
354085​
7​
2.657554865​
0​
1.328777432​
1.328777432​
1​
CccErin003
77886​
8​
100.3861262​
4​
1.930502427​
1.621542573​
1​
CccJoella001
44044​
9​
5.333346404​
4​
0.761906629​
0.339095891​
1​
CccLisa003
303688​
10​
15.6703706​
0​
0.746208124​
0.440338343​
1​
CccRobert
0​
11​
1165.528692​
12​
4.332820417​
0.972700894​
2​
DdaIrwin
0​
12​
EeaBrandon005
33184​
13​
13.19227229​
0​
1.319227229​
0.663325042​
1​

Code:
[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]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top