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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

"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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.