Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting remaining entries | Excel Discussion (Misc queries) | |||
Count Unique Entries | Excel Worksheet Functions | |||
Eliminate Overlapping Entries | Excel Worksheet Functions | |||
Table to pick out most common entries and count occurences of each | Excel Worksheet Functions | |||
How do I change multi-line entries to single line entries in Exce. | Excel Worksheet Functions |