look for multiple entries
 

Hi: I have a worksheet with these columns:

D = first name
E= last name
F = sandwich order
G= date
There are many duplicate D and E entries: that person may have come in many
times, and ordered many different sandwiches.

I've pasted my entire customer list into A, B, and C:
A= customer number
B= first name
C=last name

There are both duplicates and non-matches: many customers haven't been in,
or ever ordered a sandwich.

How do I attach the correct customer number to the D/E record? In other
words, i need it to read through columns, A, B, and C, match the names to D
and E, and then enter the customer number from A into some new column, H, so
I can delete A, B, and C, and have my info with correct customer numbers. If
they were the same line, I'd certainly know how to carry over if names were
equal, but the fact that the match may be on any line confuses me.

Seems like this should be easy. Help?
Thanks.

look for multiple entries
 

in column H:

=INDEX(Sheet2!$C$2:$C$1000,MATCH(1,(Sheet1!$D2=She et2!$A$2:$A$1000)*(Sheet1!$E2=Sheet2!$B$2:$B$1000) ,0))

Enter with Ctrl+Shift+Enter and copy down as required.

Sheet2 contains your A,B and C columns, starting row 2

Change ranges to your requirements.

HTH

"DC" wrote:

Hi: I have a worksheet with these columns:

D = first name
E= last name
F = sandwich order
G= date
There are many duplicate D and E entries: that person may have come in many
times, and ordered many different sandwiches.

I've pasted my entire customer list into A, B, and C:
A= customer number
B= first name
C=last name

There are both duplicates and non-matches: many customers haven't been in,
or ever ordered a sandwich.

How do I attach the correct customer number to the D/E record? In other
words, i need it to read through columns, A, B, and C, match the names to D
and E, and then enter the customer number from A into some new column, H, so
I can delete A, B, and C, and have my info with correct customer numbers. If
they were the same line, I'd certainly know how to carry over if names were
equal, but the fact that the match may be on any line confuses me.

Seems like this should be easy. Help?
Thanks.

look for multiple entries
 

Awesome. I got it to work for the first row. How do i get it in the rest.
"Fill" and "copy/paste" don't seemt o work.

Thanks!

"Toppers" wrote:

in column H:

=INDEX(Sheet2!$C$2:$C$1000,MATCH(1,(Sheet1!$D2=She et2!$A$2:$A$1000)*(Sheet1!$E2=Sheet2!$B$2:$B$1000) ,0))

Enter with Ctrl+Shift+Enter and copy down as required.

Sheet2 contains your A,B and C columns, starting row 2

Change ranges to your requirements.

HTH

"DC" wrote:

Hi: I have a worksheet with these columns:

D = first name
E= last name
F = sandwich order
G= date
There are many duplicate D and E entries: that person may have come in many
times, and ordered many different sandwiches.

I've pasted my entire customer list into A, B, and C:
A= customer number
B= first name
C=last name

There are both duplicates and non-matches: many customers haven't been in,
or ever ordered a sandwich.

How do I attach the correct customer number to the D/E record? In other
words, i need it to read through columns, A, B, and C, match the names to D
and E, and then enter the customer number from A into some new column, H, so
I can delete A, B, and C, and have my info with correct customer numbers. If
they were the same line, I'd certainly know how to carry over if names were
equal, but the fact that the match may be on any line confuses me.

Seems like this should be easy. Help?
Thanks.

look for multiple entries
 

Select cell in first row, place cursor on black square on bottom right (wiil
change to cross) and drag down as far as required.

"DC" wrote:

Awesome. I got it to work for the first row. How do i get it in the rest.
"Fill" and "copy/paste" don't seemt o work.

Thanks!

"Toppers" wrote:

in column H:

=INDEX(Sheet2!$C$2:$C$1000,MATCH(1,(Sheet1!$D2=She et2!$A$2:$A$1000)*(Sheet1!$E2=Sheet2!$B$2:$B$1000) ,0))

Enter with Ctrl+Shift+Enter and copy down as required.

Sheet2 contains your A,B and C columns, starting row 2

Change ranges to your requirements.

HTH

"DC" wrote:

Hi: I have a worksheet with these columns:

D = first name
E= last name
F = sandwich order
G= date
There are many duplicate D and E entries: that person may have come in many
times, and ordered many different sandwiches.

I've pasted my entire customer list into A, B, and C:
A= customer number
B= first name
C=last name

There are both duplicates and non-matches: many customers haven't been in,
or ever ordered a sandwich.

How do I attach the correct customer number to the D/E record? In other
words, i need it to read through columns, A, B, and C, match the names to D
and E, and then enter the customer number from A into some new column, H, so
I can delete A, B, and C, and have my info with correct customer numbers. If
they were the same line, I'd certainly know how to carry over if names were
equal, but the fact that the match may be on any line confuses me.

Seems like this should be easy. Help?
Thanks.

look for multiple entries
 

Yay! It worked! Thank you!

"Toppers" wrote:

Select cell in first row, place cursor on black square on bottom right (wiil
change to cross) and drag down as far as required.

"DC" wrote:

Awesome. I got it to work for the first row. How do i get it in the rest.
"Fill" and "copy/paste" don't seemt o work.

Thanks!

"Toppers" wrote:

in column H:

=INDEX(Sheet2!$C$2:$C$1000,MATCH(1,(Sheet1!$D2=She et2!$A$2:$A$1000)*(Sheet1!$E2=Sheet2!$B$2:$B$1000) ,0))

Enter with Ctrl+Shift+Enter and copy down as required.

Sheet2 contains your A,B and C columns, starting row 2

Change ranges to your requirements.

HTH

"DC" wrote:

Hi: I have a worksheet with these columns:

D = first name
E= last name
F = sandwich order
G= date
There are many duplicate D and E entries: that person may have come in many
times, and ordered many different sandwiches.

I've pasted my entire customer list into A, B, and C:
A= customer number
B= first name
C=last name

There are both duplicates and non-matches: many customers haven't been in,
or ever ordered a sandwich.

How do I attach the correct customer number to the D/E record? In other
words, i need it to read through columns, A, B, and C, match the names to D
and E, and then enter the customer number from A into some new column, H, so
I can delete A, B, and C, and have my info with correct customer numbers. If
they were the same line, I'd certainly know how to carry over if names were
equal, but the fact that the match may be on any line confuses me.

Seems like this should be easy. Help?
Thanks.