Can't Remove Duplicate Rows from a combined list

[caunyd]

Hi guys. I have 2 lists, A and B. The lists were Obtained by different methods. However the information is now static and It seems that they have compatible information and format. But when I cut and paste A to B to make a single list, and I try to delete duplicate rows in the single list, I'm unable to do so. What can I do? Attached are the lists. Thanks guys.

[GC Excel]

Hi,
the problem comes from the data in column E (City St Zip).
On one line, the space between the state and the zip code is ascii code 160 while on the other line, it is ascii code 32. That is why you can't delete the duplicate rows.

Here's how to solve your problem.
- In I2, type =SUBSTITUTE(E2,CHAR(160),CAR(32)) and copy down all rows.
- Copy paste the values from column I to column E
- Delete column I
- Use the advanced filter to remove all duplicate rows.

[caunyd]

I'm going to try this GC. Thanks!

[caunyd]

GC, I tried this, but in I2, after pasting the formula and copying down, it shows #NAME? I also tried to see if it was the space in column D. I did this with: =SUBSTITUTE(D2,CHAR(160),CAR(32)) . I don't know if this is correct or not, but it also shows #NAME? Thanks.

[newdoverman]

The values in column I were obtained by formula. In order to use those values in column E, you have to eliminate the formulae. To do this, select column I and copy then click on Paste Values (column I still selected) into column I. Now you can copy column I and paste into column E.

You may also have problems with column H. Some of the numbers are "text" numbers. If they cause problems, enter a 1 in a vacant cell and copy it. Select column H and Paste Special, Multiply. This will change all the numbers to real numbers.

Delete the 1 that you copied.

[caunyd]

Newdoverman, I don't understand your instructions completely. In the combined list that I previously attached, there is currently no Column I. I understand what you said about some values being formula-derived, but it appears that column E (City, State Zip) is static. It does seem that some of column G (State) and column H (Zip Code) are formula-derived while others are not. To simply things, I completely eliminated column G (state). Then I went onto the individual lists A and B, for both lists, I copied column H (Zip Code) and pasted them into blank column I. After this, if I highlight all of the zip codes in column I and point to the top box, there's a little diamond box with an exclamation mark in it. If I click on the !, there is an option to convert to numbers, so I did that. Then I just deleted column H, and cut and pasted the values in column I back into column H and deleted column I.

Although I don't understand your instructions completely, I think that what I did eliminated both the formula derived values and also the "text" numbers. However, I'm still unable to delete duplicate rows. I attached the Combined List after doing these things. What am I doing wrong? Am I supposed to be doing what GC posted too? This is very baffling.

[newdoverman]

There is a "bad" character in the entries in column E. It is char(160) where it should be a space. In column I enter the formula =SUBSTITUTE(E2,CHAR(160)," ") Copy this down column I. This will change all instances of CHAR(160) to a space. To eliminate the formula in column I but keep the values, select and copy column I. With column I still selected, click on Paste, Values. This will leave only the values in column I. Copy the values in column I and Paste into column E (Paste values) this will replace the contents of column E with the new values that you created with the SUBSTITUTE formula. Now, select all your data and on the Data tab click on Remove Duplicates. About 600 duplicates will be removed.

There are however some entries that have differences in the street addresses. These will require you to choose which is the correct address. I highlighted one such instance where the street addresses don't entirely match.

The reduced list is enclosed.

[caunyd]

Awesome! Thanks newdoverman! and GC! Thanks for all of your time and help. The members of this board have made my life so much less complicated

[newdoverman]

Thank you for the feedback. It is always great to get a workable solution.