Filling in incomplete cells with correct data

reporting_95

New Member
Joined
May 7, 2019
Messages
30
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello,
I have two worksheets with client id and names, but one of them has missing client data. Sheet A has complete client ID and full name. Sheet B has client first names and no IDs. Also, there are multiple clients in both sheets with the same FIRST name, but different client IDs which makes it difficult to match. What I am looking to do is fill in the correct missing data in sheet B, specifically the client ID. I tried using a vlookup but that only works with if sheet B has the client full name. I also tried vlookup with just the first name, but that would label all clients with the same name in sheet B wit the same client ID, and that would be incorrect. I will post an example below.

Sheet A
Book4
AB
1Client IDName
240921Bill Withers
317664Ethel Mertz
440754Bobby Simmons
574851Bobby Flay
679481Chris Tucks
798540Jamie Dimon
899788Mike Piazza
924787Tommy John
1052178Jamie Vazques
1177485Victoria Johnson
1254881Victora Plate
1385103Victoria McLean
1488475Daniel Lewis
1587412Daniel Murphy
1665241MikeTyson
A


Sheet B
Book4
AB
1Client IDName
240921Bill Withers
3Bill
4Bill
517664Ethel Mertz
640754Bobby Simmons
7Bobby
8Bobby
9Bobby
1098540Jamie Dimon
11Jaime
12Jaime
1377485Victoria Johnson
14Vicki
15Victoria
16Victoria
1799788Mike Piazza
18Michael P
19Mike
20Daniel
21Daniel
22Mike
B



Essentially, sheet B should have the correct client ID next to the correct client name. I think I'm missing a field of criteria, but please let me know if there is more information needed. Thanks!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
please post expected results using the same data as above
 
Upvote 0
like this?
with duplicates
Client IDName
17664Ethel Mertz
24787Tommy John
40754Bobby Simmons
40754Bobby Simmons
40754Bobby Simmons
40754Bobby Simmons
40921Bill Withers
40921Bill Withers
40921Bill Withers
52178Jamie Vazques
54881Victora Plate
65241Mike Tyson
65241Mike Tyson
65241Mike Tyson
74851Bobby Flay
74851Bobby Flay
74851Bobby Flay
74851Bobby Flay
77485Victoria Johnson
77485Victoria Johnson
77485Victoria Johnson
79481Chris Tucks
85103Victoria McLean
85103Victoria McLean
85103Victoria McLean
87412Daniel Murphy
87412Daniel Murphy
88475Daniel Lewis
88475Daniel Lewis
98540Jamie Dimon
99788Mike Piazza
99788Mike Piazza
99788Mike Piazza
or
Client IDName.1Name.2Client ID.1Name.1.1Name.2.1
17664EthelMertz17664EthelMertz
24787TommyJohn
40754BobbySimmons40754BobbySimmons
40921BillWithers40921BillWithers
52178JamieVazques
54881VictoraPlate
65241MikeTyson
74851BobbyFlay
77485VictoriaJohnson77485VictoriaJohnson
79481ChrisTucks
85103VictoriaMcLean
87412DanielMurphy
88475DanielLewis
98540JamieDimon98540JamieDimon
99788MikePiazza99788MikePiazza
 
Last edited:
Upvote 0
like this?
with duplicates
Client IDName
17664Ethel Mertz
24787Tommy John
40754Bobby Simmons
40754Bobby Simmons
40754Bobby Simmons
40754Bobby Simmons
40921Bill Withers
40921Bill Withers
40921Bill Withers
52178Jamie Vazques
54881Victora Plate
65241Mike Tyson
65241Mike Tyson
65241Mike Tyson
74851Bobby Flay
74851Bobby Flay
74851Bobby Flay
74851Bobby Flay
77485Victoria Johnson
77485Victoria Johnson
77485Victoria Johnson
79481Chris Tucks
85103Victoria McLean
85103Victoria McLean
85103Victoria McLean
87412Daniel Murphy
87412Daniel Murphy
88475Daniel Lewis
88475Daniel Lewis
98540Jamie Dimon
99788Mike Piazza
99788Mike Piazza
99788Mike Piazza
or
Client IDName.1Name.2Client ID.1Name.1.1Name.2.1
17664EthelMertz17664EthelMertz
24787TommyJohn
40754BobbySimmons40754BobbySimmons
40921BillWithers40921BillWithers
52178JamieVazques
54881VictoraPlate
65241MikeTyson
74851BobbyFlay
77485VictoriaJohnson77485VictoriaJohnson
79481ChrisTucks
85103VictoriaMcLean
87412DanielMurphy
88475DanielLewis
98540JamieDimon98540JamieDimon
99788MikePiazza99788MikePiazza
Yes, the results you posted would be what I am looking for. I'm guessing I need another piece of information to make that complete?
 
Upvote 0
I assume left table will be ok, so try this
Power Query:
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split1 = Table.SplitColumn(Source1, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Split2 = Table.SplitColumn(Source2, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    Join = Table.NestedJoin(Split2,{"Name.1"},Split1,{"Name.1"},"Table",JoinKind.FullOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Client ID", "Name.1", "Name.2"}, {"Client ID.1", "Name.1.1", "Name.2.1"}),
    Filter = Table.SelectRows(Expand, each ([Client ID.1] <> null)),
    Sort = Table.Sort(Filter,{{"Client ID.1", Order.Ascending}}),
    TSC = Table.SelectColumns(Sort,{"Client ID.1", "Name.1.1", "Name.2.1"}),
    TCC = Table.CombineColumns(TSC,{"Name.1.1", "Name.2.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Name"),
    Ren = Table.RenameColumns(TCC,{{"Client ID.1", "Client ID"}})
in
    Ren
note: I usually delete solutions after 2-3 days and I don't need to remember how I did it so it's best to reply asap, any answer is better than nothing :cool:
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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