Merging UnEven Names in Power Query

montyfern

New Member
Joined
Oct 12, 2017
Messages
35
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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

montyfern

New Member
Joined
Oct 12, 2017
Messages
35
Dear sandy666, will do. Asked client for requisite file; please stay tuned. Do you want me to email you or use DropBox? Thanks!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,444
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,444
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,444
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,786
Messages
5,470,752
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top