Excel - checking if first five digits of last name and first five digits of address1 are equal
Experts,
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).
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).
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)
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?)