Consolidate Multiple Rows with Match FirstName,MiddleName and LastName with a Unique ID Number

[pab]

New CIFKEY CIFKEY CIFCRTDT Branch LNAME TITLE BIRTHDT
1 00001-0003 00001 29-Oct-2013 0003 Ariola, Genesis G. ARIOLA, GENESIS G. 1 09-Jul-1981
5 00132-0003 00132 10-Nov-2013 0003 Macalanggan, Jammirah S. MACALANGGAN, JAMMIRAH S. 2 03-May-1984
6 00133-0003 00133 10-Nov-2013 0003 Manzano, Joel S. MANZANO, JOEL S. 1 30-Nov-1987
7 00134-0003 00134 10-Nov-2013 0003 Ramos, Lilia B. RAMOS, LILIA B. 2 17-Jun-1965
8 00001-0003 00001 19-Apr-2014 0002 Ariola, Genesis G. ARIOLA, GENESIS G. 1 09-Jul-1981
9 00002-0002 00002 19-Apr-2014 0002 Cambay, Matt Sainon O. CAMBAY, MATT SAINON O. 1 12-Jul-2007

 

[Marc L]

Hey ! What did you expect ?‼​

 

[pab]

Hi Sir,

I expect a consolidated data under a master worksheet, all matching first,middle and last name under one unique ID - all data attached to that will also be copied on the master.

Thanks,

 

[Deepak]

Dear pab,

Would you pls upload a sample file with desired result.

 

[pab]

Dear pab,

Would you pls upload a sample file with desired result.

Hi Sir,

Can I email it straight to you? 1.15 Mb is the size

Thanks,

 

[pab]

Dear pab,

Would you pls upload a sample file with desired result.

Hi Dear Deepak,

I have multiple sheets - each sheet has Client Data, check for duplicates filtering First Name,Middle Name and Last Name across all sheets and assign a unique ID Number on a master worksheet.

Master Data
ID Old ID First Name Middle Name Last Name
00001-0001 00002 John Hayes Doe
00001-0001 00003 John Hayes Doe

Worksheet 1
Old ID First Name Middle Name Last Name
00002 John Hayes Doe

Worksheet 2
Old ID First Name Middle Name Last Name
00003 John Hayes Doe

Thanks,

 

[Deepak]

Check This..

 

[pab]

Hi Sir, the analysis is correct but the New ID will be independent. It will not be based on the OLD ID NO.

Thanks,

 

[pab]

The lookup value for new CIF will be on another worksheet - everytime it hits the same First Name, Middle Name Last name it will re use the Unique ID which will be seen at the New CIF.

Thanks Very Much

 

[Deepak]

The lookup value for new CIF will be on another worksheet - everytime it hits the same First Name, Middle Name Last name it will re use the Unique ID which will be seen at the New CIF.

Thanks Very Much

Didn't get this point.

 

[pab]

Didn't get this point.

I will generate a CIF Key on another worksheet - say 00001-0001 to 00001-1000
This generated key will be used by the Master Worksheet to duplicate First Name,Middle Name and Last Name found at Branch worksheet.
Everytime it hits the duplicate it will get the assigned pre generated CIF Key

 

[Deepak]

@ pab

Pls make a final sample copy of desired output & there upload it here or any cloud storage & share the link.

 

[pab]

Hi Sir,

I will send you an output file.

Thanks,

 

[pab]

Hi Sir,

Here is the sample file.
http://jmp.sh/j7dEQwv

Thanks,

 

[pab]

@ pab

Pls make a final sample copy of desired output & there upload it here or any cloud storage & share the link.

Hi Sir,

http://jmp.sh/j7dEQwv

Thanks,

 

[Deepak]

Check this.

 

[pab]

Check this.

Hi Sir,

Almost there, but the CIF key mapping is incorrect it goes down one cell lower.

Thanks,

 

[pab]

Check this.

Sir,

Why is the mapping goes one cell down - it didnt match.

Thanks,

 

[Deepak]

Sir,

Why is the mapping goes one cell down - it didnt match.

Thanks,

Sorry, Pls change this line.

.Cells(i, 1) = Sheet4.Cells(Application.Match(m, Sheet4.Columns(5), 0), 1)

 

[pab]

Sir,

Hi Sir,

The code is perfect SIR. THanks so much.

If i have 30 worksheets coming from branches - how will it adjust the code.

Thanks,

 

[Deepak]

Pls share a sample workbook containing all sheets.

 

[pab]

Pls share a sample workbook containing all sheets.

Hi Sir,

Same data will be seen on other sheets - e.g. Branch 1, Branch 2, to Branch 30.

Thanks,

 

[pab]

Hi Sir,

Hi Sir,

Same data will be seen on other sheets - e.g. Branch 1, Branch 2, to Branch 30.

Thanks,

 

[Deepak]

Sorry, i just missed it.

I will loop & update you by eod.

 

[Deepak]

Sorry, i just missed it.

I will loop & update you by eod.

Hope!
This would work as you need!.