Excel - checking if first five digits of last name and first five digits of address1 are equal


I have a file in Excel sorted by address1 (Column G) and last name (Column C).  If the first five positions of column g are equal and the first five positions of column c are equal, I would like to add 1 to column V.  If not I would like to make column V = 1.

This is used to identify members that are possibly in the same household and delete all but the first member.

Can someone give me the code for Column V.

The file will be presorted by Column  G (Address 1) Column C (last name) and lastly column F (descending age).
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
CompProbSolvConnect With a Mentor Commented:
It is clearer now!
In V2, use the following:
=if(left(c2,5)=left(g2,5), v1+1,1)
copy that to all the cells below it

In V1 just put the digit 1.
"if the first five positions..."  are you referring to g1 through g5 being identical and c1 through c5 being identical?  What about subsequent rows (g6 through g10, etc.)?

If there are, say, four matches, you want a 1 in V?

Which row in V do you want to increment if they match or do you just want a count of how many matches there are?

Also, it is tricky (though not impossible) to increment V (v1?)
I'm having a hard time understanding your statement:

If the first five positions of column g are equal (to what?)
and the first five positions of column c are equal (to what?)
I would like to add 1 to column V
If not I would like to make column V = 1
(isn't it the same thing????)

to my logic:
IF (5 first positions of column G) AND (5 first positions of colum C) are equal
THEN Add number 1 to column V
ELSE Add number 0 to column V

...if I got that right, you can try the following in cell V1 that will do the checking between cell C1 and G1

=IF (LEFT(C1;5)=LEFT(G1;5); 1; 0)

(on my Excel version I use semi-colon, on some other Excel versions use just comma, modify if necessary)
reading your original statement again, I'm even more puzzled: first five positions, in which direction, in the cell, or the subsequent position? horizontally? vertically?
moriniaAdvanced Analytics AnalystAuthor Commented:
I am sorry I wasn't clearer.  I was not clear in the question.   The file is sorted by address1 and then last name and then age ( descending).

If the first 5 position of the address on the prior record are equal to the first 5 postions on the following record, I want to add 1 to column V.  If they are not equal I want to make column V = 1.  

The first 5 positions are used because most differences in typing the address are St vs Street or ave vs avenue etc.

Any records that have a value greater than 1 in column V will most likely be deleted.  Hopefully the sort is putting those in the same household together.  The file will still have to be eyeballed but hopfully the process is faster.

See example below:
                                                                               Column V
John  Doe     45 West  First  St                                     1
Jane Doe       45 West  First  St                                    2
Bob Jones      12 East 9th St                                        1
Jane Thomas   65 Broadway                                       1
Lisa Thomas    65 Broadway                                       2
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.