combining 2 sets of data, deleting uncommon names

[annelayton]

I'm new to this forum. I've copied and pasted 2 sets of data from Crystal Reports into a single worksheet. I now have 2 columns with names (Column D and E), but not all the names are in Column E. I want to delete those names in D that are not in Column E. I also have address and zip in Column A, B, and C that I need to keep matched up to the names in Column E. In other words, if the name isn't in Column D, I want to get rid of them in A,B,C, and D. Thanks for any help you can give.

[JBeaucaire]

1) In an empty column, put this formula in row2:

=ISNUMBER(MATCH($D2, $E:$E, 0))

2) Copy that downward so that it goes down as far as there is data in column D.

3) Highlight the new column

4) Select Data > Filter to turn on the AutoFilter

5) Filter that column for FALSE

6) Select all the visible rows in A:D

7) Select Edit > Delete > Shift Up

8) Turn off the Autofilter and clear the column you added

[annelayton]

I think I erred in my first post. I meant "In other words, if the name isn't in Column E, I want to get rid of them in A,B,C, and D. So I have over 7000 names in Column D with corresponding addresses in A,B, and C, and I need to match them to the approx. 3000 names in column E. I tried the solution above but it did not work, obviously because I gave incorrect information. Thanks.

[ron2k_1]

I believe if you turn on AutoFilter would solve your problem,

1. Click on you your header row
2. Select Data > Filter to turn on the AutoFilter
3. Select header for Col E, and select non-blanks

This will not delete the other rows but at least they will not show for blank records found on Col E. This recommendation is assuming that when you mention "...but not all the names are in Column E..." that there are names on columns E that will match to column D, however, Column E has lots of blank cells.

Another way to do it is by sorting by Col E, and then deleting all those records that are blank on Column E, and the resorting by whatever order you want them.

EDIT: If this doesn't take care of what you want. Take the time to produce us a sample file similar to your workbook representing the very same issues you're facing and telling us what results you want.

[annelayton]

I don't have any blanks in the spreadsheet except below the last entry in Col E-Col P. I have about 2000 names/data in Col A-Col D, but only 1000 names in Col E. If the name isn't in E, I want to remove it from Col D, and it's associated address info from Col A-C, so that the remaining names and address match what's in Col E.

[JBeaucaire]

Based on your repeated description in post #5, the step by step instructions provided in post #2 do what you want. It can't get any better than that unless I come to your house. (nudge)

[annelayton]

OK, I created a duplicate worksheet but changed the names and addresses. I'd appreciate any help you could give. Thanks!!

[JBeaucaire]

The only detail you left out is the these were not "exact match" strings. You've got other junk in column D that has to be removed for the match test to work. So the corrected instructions would be:


1) In an empty column, put this formula in row2:

=IF(ISNUMBER(SEARCH("(", $D2)), ISNUMBER(MATCH(TRIM(LEFT($D2, FIND("(",$D2)-1)),$E:$E, 0)))

2) Copy that downward so that it goes down as far as there is data in column D.

3) Highlight the new column

4) Select Data > Filter to turn on the AutoFilter

5) Filter that column for FALSE

6) Select all the visible rows in A:D

7) Select Edit > Delete > Shift Up

8) Turn off the Autofilter and clear the column you added



BTW, you've also got stray "spaces" hidden in the text strings in column E creating false FALSE results. Like Mecrazy, Shameka should match, but the hidden trailing space in E15 keeps it from working. You've got some data cleanup to do.

[annelayton]

I cleaned up the data in Column D and deleted the hidden text strings. I followed the steps in both Post #2 and Post #8, but everything in the new column returns FALSE. Once I get to Step 7 in Post #8, how do I select Edit? I don't see an Edit tab other than Home>Editing, but there's no Delete.

Thanks

[JBeaucaire]

Highlight the visible rows in A:D that you want to delete, right-click the selected range and select Delete > Shift Up

[annelayton]

I'm going to try this again. I copied and pasted Col A-D from a Crystal Report. It contains, in order A: City, State, Zip, B: Zip, C: Street Address, D: Patient Name. There are 6000+ entries which represent every patient we have ever seen.

From another Crystal Report, I have Col E-N which represents only patients seen in the past 3 years. Those columns contain other Patient information, including the Patient Name in Col E, but it does not contain any address information. There are only about 3000+ entries in these columns.

I want to eliminate the Patient information in Col A-D where the Patient name is not in Col. E. Then I need to sort by zip code to find patients in a particular geographic area for marketing purposes. I know how to clean up the the addresses after that.

Thanks

[JBeaucaire]

I didn't see a smaller excerpted workbook demonstrating all that...be sure to attach that.

[annelayton]

OK, I've attached another test document. I changed the names to protect the innocent. I also deleted a few columns, including one that showed date of birth. Thanks so much for your help.

[JBeaucaire]

1) Insert an empty column in column E, splitting your two datasets by the new empty column

2) Put this formula in E2:

=OR(ISNUMBER(MATCH(TRIM(D19),F:F, 0)),ISNUMBER(MATCH(D19,F:F, 0)))

2) Copy that downward so that it goes down as far as there is data in column D.

3) Highlight column E

4) Select Data > Filter to turn on the AutoFilter

5) Filter that column for FALSE

6) Select all the visible rows in A:D

7) Select Edit > Delete > Shift Up

8) Turn off the Autofilter and clear column E

9) Sort the data by column B.

[annelayton]

When I filter for False, everything returns as False. If I filter for True, everything returns as True.

Thanks,
Anne

[JBeaucaire]

When I did that on the sample sheet above it worked, so let's see yours.

[annelayton]

When I copy the formula into the newly created E2 and copy it down, it shows TRUE and FALSE all the way down the column before I click on Filter. Also, I've manually worked down thru the name Colang...., but entries above and below that name seem to be random True or False. I can't attache it because it has patient information. I don't have to have the information in columns after E if that helps any. Thanks

[annelayton]

Also, even though Rows 24, 63, 143, 255, and a few others are a match, they say FALSE before I click on Data > Filter.

[JBeaucaire]

I can't attache it because it has patient information.

It shouldn't take you more than 5 minutes to desensitize a copy of your workbook.

In the name field, change the first name to Name1, the grab the lower right corner of that cell to "drag-down" the value and it will put Name2, Name3, etc going down the list.

Repeat the with phone numbers, or SSNs, or any field that needs to remain in the sample workbook to demonstrate the issue properly. Any other columns could simply be cleared (all except the row1 with your titles, leave that...)

You get the idea. A few minutes work on your part should easily create a non-sensitive example workbook that we can look at together to discover the problem you're having.