Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Compare Information
I have a database of names & addresses. I am sent a revised database
monthly. The headings are the same, however some of the names & addresses may change. Is there an easy way of compariing the 2 for duplicates or up-dated information and saving as a new up-dated sheet? ie Current data Title First Name Surname Mr Andrew Aalders-Dunthorne Ms Carolyn Abbott Dr David Abbott Cllr James Abbott Mr Ben Abbotts Mr Mohammed Abdel-Haq Mr Mel ab Owain New data Title First Name Surname Mr Andrew Aalders-Dunthorn Ms Carolyn Abbott Dr David Abbott Cllr James Abbott Mr Ben Abbotts Mr Mohammed Abdel-Haq Mr Mel ab Owain |
#2
|
|||
|
|||
One way to try ..
Assuming: Current data is in Sheet1, cols A to C, data from row2 down to row100 New data is in Sheet2, cols A to C, data from row2 down In Sheet2 ----------- Using an empty col to the right, say col K? Put in K2, and array-enter (Press CTRL+SHIFT+ENTER): =IF(ISNA(MATCH(TRIM(B2)&"_"&TRIM(C2),TRIM(Sheet1!$ B$2:$B$100)&"_"&TRIM(Sheet 1!$C$2:$C$100),0)),ROW(),"") Copy K2 down as many rows as there is new data in cols A to C In a new Sheet3 --------------- Put in A1: =IF(ISERROR(SMALL(Sheet2!$K:$K,ROWS($A$1:A1))),"", INDEX(Sheet2!A:A,MATCH(SMA LL(Sheet2!$K:$K,ROWS($A$1:A1)),Sheet2!$K:$K,0))) (Normal ENTER) Copy A1 across to C1 (or across as many cols as you have actual data to compare) Fill down by as many rows as was done in Sheet2's col K Sheet3 will return (if any) all the rows of new data in Sheet2 which does not match with the current data in Sheet1 at the top, bunched together. The matching is done using a concat of the First Name and Surname in cols B & C. We could then select the row(s) returned in Sheet3 and do a copy paste special as values (and format) over in Sheet1 just below the last row of data as updates to Sheet1 Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "ASBiss" wrote in message ... I have a database of names & addresses. I am sent a revised database monthly. The headings are the same, however some of the names & addresses may change. Is there an easy way of compariing the 2 for duplicates or up-dated information and saving as a new up-dated sheet? ie Current data Title First Name Surname Mr Andrew Aalders-Dunthorne Ms Carolyn Abbott Dr David Abbott Cllr James Abbott Mr Ben Abbotts Mr Mohammed Abdel-Haq Mr Mel ab Owain New data Title First Name Surname Mr Andrew Aalders-Dunthorn Ms Carolyn Abbott Dr David Abbott Cllr James Abbott Mr Ben Abbotts Mr Mohammed Abdel-Haq Mr Mel ab Owain |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic update of information in a spreadsheet | Excel Discussion (Misc queries) | |||
Compare two spreadsheets to find differences. | Excel Discussion (Misc queries) | |||
Is there a way to compare 2 spreadsheets with Excel? | Excel Discussion (Misc queries) | |||
Sharing information between Access and Excel | Excel Discussion (Misc queries) | |||
Duplicating Cell Information | Excel Worksheet Functions |