Excel manipulation question 1

[erikhertzel]

I have a spreadsheet of data with some numbers associated with names. The primary number should be associated with only one name, but it currently is not.

So, I am trying to figure out how to seperate the primary number out and then issue a new number to the rest of them (mostly husband and wife share the saem Acct Num). Here is an example

Num FirstN MidI LastN Mailing Name
A B C D E

123 Joe C Bob Joe C Bob
123 Jane D Bob Joe C Bob

I need to figure out how taht if the mailing name = the other name field when it's put together (B-D) then drop the record so I only have the secondary name left (that which does not equal the mailing name).

Any ideas?

 

[SkipVought]

Hi,

"then drop the record so I only have the secondary name left (that which does not equal the mailing name)."

Finding the duplicates is not a problem. How do you determine which of the group to keep?



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[erikhertzel]

The primary name = the Mailing Name so it's kind of two steps
1. Match the F/M/L name to the mailing name
2. Find which records with the same name match the mailing name then drop that record (so all I have left are secondary names taht will not match the mailing name)

So, in the example, above Joe C Bob needs to match Joe C Bob and then drop it or sort it so I can drop it and I am left with a Jane D Bob record.

 

[SkipVought]

just add a column F
[tt]
F2: =B2&" "&C2&" "&D2=F2
[/tt]
Then filter on TRUE in column F, using the AutoFilter.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[erikhertzel]

Skip, this is workign great, but I do not see the True option in autofilter. How do I compare the two columns using a true statement?

 

[erikhertzel]

Never mind, I just got it, see what you mean. Thanks sir.

 

[erikhertzel]

One final question, I am using this technique to match the two fields (mailing name vs. combined name)
=IF(AT82=V82,"Yes","No")

The problem is if they are just a tad bit different (space, or slight deviation of the first name) then the "match" does not happen. Example:

Joe C Bob and Joey B Bob
or
Joe C Bob and Joe C Bob (extra space)

Any way to make the compare a little cleaner to identify the bulk of the records I want to?

 

[SkipVought]

Clean up your data by doing an Edit > Find & Replace looking for TWO SPACES, replacing with ONE SPACE.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[erikhertzel]

Yeah, I did that and it helps, but what about those with a little slight variation in their first name (i.e. Joe vs. Joey or Tim vs. Timmy). Any ideas here?

 

[SkipVought]

Nope. That's just another instance of bad data that must be cleaned up, IMHO.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[erikhertzel]

OK, just checking.....and thanks.