This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi everyone,
I have a spreadsheet consisting of about 150 rows. It contains the full names of the people in Column F and each of the rows have either a “billing address”, “home address”, “work address” or “PO address”. The spreadsheet is sorted by column F (Full name) in alphabetical order so that you can clearly see the duplicate names. In fact, the duplicates in column F were identified in another workbook and copied into the current separate one so that duplicate customer records could be removed in preparation for a mailout.
In most instances there are only two duplicates for any particular customer but in some cases there could be 3, 4 or even 5. The goal of the exercise is to keep the customer row that has a home address as that is the preferable address to send to. If there’s no home address, then keep the billing address. If no billing address, then keep the work address. If no work address keep the PO box address.
My first attempt at this de-duping was to sort by home address, then by billing address, then by work address, then by PO box address. Even though this helped identify which entries had a home address, it still hasn’t solved the problem as it hasn’t de-duped as I would like and as I described in the previous paragraph.
Can anyone tell me if this can be done in excel and if yes, how I would go about it? Or would I would need to get some vba code written?
Would really appreciate any advice.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi, did you see my previous post where I gave you the workbook name, sheet name and range? I mentioned that I had everything in one excel file called "test.xlsx". Is that ok or do I need to have them in separate excel documents?
for each workbook, I will need the:
workbook name
sheet name
range, i.e. A1:AR7
so I will know how to reference it into the formula.
example:
VLOOKUP($A1,[Home.xlsx]Sheet1!A1:AR100,1,FALSE)
THE REAL FORMULA I AM CREATING LOOKS SOMETHING LIKE THIS:
IF(ISNA(VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))
IF(ISNA(VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))
and that blank worksheet with the names in it we will be formatting into something that looks like this:
see those numbers below the heading labels?
we will use them to reference the column numbers into the formula and paste the formula into each of the cells to populate all of them, then we will copy all the populated cells, and paste them as value.
Hi, I think the information below is what you were after:
test.xlsx
Home
A1:AS61
test.xlsx
PO Box
A1:AS11
test.xlsx
Work
A1:AS33
test.xlsx
Billing
A1:AS118
They're all in one file called test.xlsx and then it shows the name of the sheet and the range within the sheet.
As also mentioned, there is a fifth sheet called "names only" which contains all the names in column A.
Is that ok?
for each workbook, I will need the:
workbook name
sheet name
range, i.e. A1:AR7
so I will know how to reference it into the formula.
example:
VLOOKUP($A1,[Home.xlsx]Sheet1!A1:AR100,1,FALSE)
THE REAL FORMULA I AM CREATING LOOKS SOMETHING LIKE THIS:
IF(ISNA(VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))
IF(ISNA(VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))
and that blank worksheet with the names in it we will be formatting into something that looks like this:
for each workbook, I will need the:
workbook name
sheet name
range, i.e. A1:AR7
so I will know how to reference it into the formula.
example:
VLOOKUP($A1,[Home.xlsx]Sheet1!A1:AR100,1,FALSE)
THE REAL FORMULA I AM CREATING LOOKS SOMETHING LIKE THIS:
IF(ISNA(VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))
IF(ISNA(VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))