HI there
I have 2 sets of data and the only unique identifiers I have in both data sets is:
FirstName
LastName
DOB
I am trying to match on these 3 criterias and also return their Address, Suburb, State, POst Code.
This is a very large data set and I am only expecting a small amount of matches however when I use this formula in excel {INDEX(CIDoB,MATCH($AQ2&$AR2&Q2,CIClientFName & CIClientLName & CIDoB,0)))} it just times out and takes FOREVER to update. SO i wanted to create a macro to loop through the data sets, identify the matches and return the names, DOB and addresses.
Address1
Suburb
State
Postcode
I want to do an IF statement that loops through over 400,000 rows that if FIRSTNAME, LASTNAME & DOB match from within my 2 named ranges, return all the data from sheet1 and append to the records on sheet 2 new.
Here is a sample of the Spreadsheet i am trying to loop through. I'm using named ranges for DOB FName LName for both worksheets. To get the one match in records, this is the Excel code i used
Q2 = Firstname on sheet 2
R2 = LastName on Sheet 2
P2 = DOB on Sheet 2
DOB = {INDEX(CADOB,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
Add= {INDEX(CAAdd,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
Suburb = {(INDEX(CASub,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CIDoB,0)))) }
PC= {INDEX(CAPC,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
State = {INDEX(CAState,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
I can't work out how to upload my excel spreadsheet any hints?
Here's a screen shot
<tbody>
</tbody>
Sheet 2
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Many thanks for all your help,
Nina
I have 2 sets of data and the only unique identifiers I have in both data sets is:
FirstName
LastName
DOB
I am trying to match on these 3 criterias and also return their Address, Suburb, State, POst Code.
This is a very large data set and I am only expecting a small amount of matches however when I use this formula in excel {INDEX(CIDoB,MATCH($AQ2&$AR2&Q2,CIClientFName & CIClientLName & CIDoB,0)))} it just times out and takes FOREVER to update. SO i wanted to create a macro to loop through the data sets, identify the matches and return the names, DOB and addresses.
Address1
Suburb
State
Postcode
I want to do an IF statement that loops through over 400,000 rows that if FIRSTNAME, LASTNAME & DOB match from within my 2 named ranges, return all the data from sheet1 and append to the records on sheet 2 new.
Here is a sample of the Spreadsheet i am trying to loop through. I'm using named ranges for DOB FName LName for both worksheets. To get the one match in records, this is the Excel code i used
Q2 = Firstname on sheet 2
R2 = LastName on Sheet 2
P2 = DOB on Sheet 2
DOB = {INDEX(CADOB,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
Add= {INDEX(CAAdd,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
Suburb = {(INDEX(CASub,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CIDoB,0)))) }
PC= {INDEX(CAPC,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
State = {INDEX(CAState,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
I can't work out how to upload my excel spreadsheet any hints?
Here's a screen shot
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col span="3"></colgroup><tbody> </tbody> | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
<tbody>
</tbody>
Sheet 2
Mger Code | Manager | MgerLastName | ClientID | ClientGivenName | ClientLastName | EntityName | IsPrimary | AddressLine1 | AddressLine2 | SuburbName | PostCode | State | Country | DateOfBirth | Client2FirstName | Client2LastName | Client1 Match on DOB | Address1 | Suburb | Postcode | State | |
111222 | Paul | Jones | 5554443 | Joel | Smith | Yes | 222 Fake Street | PHESSANT | 2000 | NSW | AUSTRALIA | 5/10/1984 | Joel | Smith | ||||||||
111222 | Paul | Jones | 5554443 | Julie | Broidy | Yes | 32 Lee Place | ST IVES | 4022 | QLD | AUSTRALIA | 17/10/1943 | Julie | Broidy | ||||||||
111222 | Paul | Jones | 5554443 | Cherish | Laman | Yes | 12 Leonard Place | MARRICKVILLLE | 2122 | NSW | AUSTRALIA | yes@yahoo.com | 20/01/1986 | Cherish | Laman | |||||||
111222 | Paul | Jones | 5554443 | Alisha | Hayswoth | Yes | 77 Smith Drive | APPIN | 2560 | NSW | AUSTRALIA | 7/03/1971 | Alisha | Hayswoth | 7/03/1971 | 77 Smith Dr | APPIN | 2560 | NSW | |||
111222 | Paul | Jones | 5554443 | MARY | HA | Yes | 54 COLLEGE AVENUE | QUAKERS HILL | 2529 | NSW | AUSTRALIA | 12/03/1959 | MARY | HA | ||||||||
111222 | Paul | Jones | 5554443 | WENDY | HANS | No | 5 BRISBANE STREET | BRISBANE | 4000 | QLD | AUSTRALIA | 13/03/1967 | WENDY | HANS | ||||||||
111222 | Paul | Jones | 5554443 | WENLI | HA | No | 54 JONES AVE | BLACKBUTT | 2529 | NSW | AUSTRALIA | 13/03/1967 | WENLI | HA | ||||||||
111222 | Paul | Jones | 5554443 | DANNY WAI KAI | CATANIA | Yes | 10 POLARIS PLACE | REEVSBY | 2222 | NSW | AUSTRALIA | 9/09/1949 | DANNY | CATANIA | ||||||||
111222 | Paul | Jones | 5554443 | JOHNEE | CATA | Yes | 10 UPPER BEACH Street | Balgowlah | 2766 | NSW | AUSTRALIA | 9/09/1949 | JOHNEE | CATA |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Many thanks for all your help,
Nina