Excel Question: Merging Columns Between Sheets?

Question

I have a WorkBook with 5400 Names, Addresses, and various other info.
There are duplicate addresses
I have a column with header "Status"
This Column 'B' has four cell values of Expired, Withdrawn, Active, or Active Contingent
I need to do a Mail Merge and don't want to have duplicates.
I do not need to mail to addresses with Rows that have Status as 'Active' or 'Active Contingent' these ones I cut from Sheet 1 and pasted into sheet 2.
There are duplicate addresses where the same address is listed as both active, expired, and withdrawn, or any combination of the three.
Addresses are in Coumn I on both sheets
Now I need a way to merge Column I on both sheet 1 and 2 and have Excel transfer data to sheet three.
Sheet three would only have Expired or Withdrawal Listings (Column B) and no duplicates of any addresses as well as no 'Active' status'
I hope this makes sense it really is not that complicated I am just missing it.

Answer 1

You will need to 1) create a clean list with no duplicates, 2) find the Expired and Withdrawn, and 3) put those on Sheet 3:

1) create a clean list
- it sounds like you have to bring back the active/active contingent rows from sheet2 to sheet1; if you would rather leave sheet1 as is, you could put all the rows from sheet1 and sheet2 on a new sheet (sheet4?)
- add a new column that counts the occurrences of an address (put this at row 2, and then copy down)
=COUNTIF(I:I, I2)
- this counts how many times the address in I2 appears in col. I
- this will assume that when there are duplicate addresses, they are exactly identical; if there will be addresses that are different only because of typos, etc, then you may want to sort the addresses and visually search for those that look the same
- sort all of your data by the new column with the counts: select all of your data, then do Data - Sort, and Sort By the column with the counts
- instead of sorting, you could also do a filter on the new column: Data-Filter-Autofilter, and pick the ones with 2 or more occurences
- look through the rows with 2 or more occurences and delete the ones that are wrong

2) once you have a clean list, put on a filter on column B to find the Expired or Withdrawn:
- select column B
- Data-Filter-Autofilter
- click the little arrow and pick (Custom)
- put equals - Expired in the first criteria
- pick Or
- put equals - Withdrawn in the second criteria
- this will hide all the rows where the status is not Expired or Withdrawn

3) Move Expired/Withdrawn to Sheet3
- Select all the rows; the hidden ones will not be selected
- Copy and Paste to Sheet3

Let us know if you need more info.

Answer 2

Here's what I tend to do when I have a problem similar to yours.

Step 1: Sort the list. Data > Sort...
This puts similar values close together.

Step 2: Filter the data by selecting all of the data (normally I do so by selecting the grey block between A and 1 in the top right corner) then select. Data > Filter > AutoFilter.

Step 3: Use a formula to determine if the cell above or below is the same. Since your Addresses are in Column I, let's put an Excel formula in the next colum to the right to evaluate the cell row below. For example enter the formula below in cell J2:

=IF(I2 = I1, "Duplicate", "Not Duplicate")

The IF formula works like this:

First let's break it down a bit into Part A, Part B, Part C.

= IF(Part A, Part B, Part C)

Part A - you use to compare two or more things and get a True or False answer.

Part B - is what Excel displays in the cell if the comparison evaluates as True

Part C - is what is displayed if the comparison evaluates as False


Your first row should be a header. So after you enter that, cell J2 should state "Not Duplicate". Copy that formula down into the rest of the cells for that column. And it will list everything as to whether it is a duplicate or not.

Step 4: Filter out the duplicates. Go to the top of column J and select the little filter arrow for a drop down list. You can now either do one of two things. Have it show the duplicates and delete out the duplicates. Or have it show the non-duplicates and copy them over to the other sheet.

Step 5: Delete out the column with the formula in it.

Excel normally will only copy the rows that are visible. However, sometimes it does like to be a pain and wants to copy everything. If it does copy the hidden rows there is something you can do to stop that. After you select the data, go to Edit on the Menu Bar and select

Edit > Go To... > Special > Visible cells only > OK

This selects only the visible cells and rows. Then copy and paste your data.



When you get good at this in Excel, you should be able to eliminate the repeated values on a list that long in under a minute or so.