If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to compare data in two columns...
I have two columns of data, one for serial numbers I have sent out, and one
where extended warranties that were purchased. Instead of always using the find feature, is there a way to automate it so that the first column can search the second to see if an extended warranty was purchased? Thanks in advance for your help! |
#2
|
|||
|
|||
Hi!
There are many ways to do this but what exactly do you mean by have one column search another? How do you designate that an extended warranty has been purchased? If the warranty was not purchased is the cell left blank? What result do want if the warranty was or was not purchased? Are the serial numbers unique? Biff "accessgrits" wrote in message ... I have two columns of data, one for serial numbers I have sent out, and one where extended warranties that were purchased. Instead of always using the find feature, is there a way to automate it so that the first column can search the second to see if an extended warranty was purchased? Thanks in advance for your help! |
#3
|
|||
|
|||
Thanks for responding, what I'm trying to do is take a list of about 8,000
7-digit numberical serial numbers (column A) and a list of about 2,000 serial numbers that have been purchased for extended warranty (column), so some of the numbers in column B will also be in column A. Instead of going through every serial number in column A and manually searching column B to see if it was a serial that also had extended warranty purchased on it, I was hoping for a formula or some means, that would take all the serial numbers in column A and see if there is a duplicate of that same number in column B, indicating it has an extended warranty purchased on it. Does that make sense? "Biff" wrote: Hi! There are many ways to do this but what exactly do you mean by have one column search another? How do you designate that an extended warranty has been purchased? If the warranty was not purchased is the cell left blank? What result do want if the warranty was or was not purchased? Are the serial numbers unique? Biff "accessgrits" wrote in message ... I have two columns of data, one for serial numbers I have sent out, and one where extended warranties that were purchased. Instead of always using the find feature, is there a way to automate it so that the first column can search the second to see if an extended warranty was purchased? Thanks in advance for your help! |
#4
|
|||
|
|||
Hi!
OK, that's much clearer. Very easy to do: Insert a new column between column A and B or, just move column B over so that you can put a formula next to the values in column A. Assume your list in column A is from A1:A8000. The values in column B have now been moved over to column C in the range C1:C2000. In B1 enter this formula: =IF(COUNTIF(C$1:C$2000,A1),"Yes","") Double click the fill handle to copy the formula down to A8000. Done! Any serial numbers in column A that also appear in column B will be identified with a "Yes". Another thing that you could do to make those dupes easier to see is to sort them so that all the dupes with a "Yes" are on the top or bottom of the list. Select both columns A and B Goto DataSort Sort by column B descending to put the dupes at the top of the list. Biff "accessgrits" wrote in message ... Thanks for responding, what I'm trying to do is take a list of about 8,000 7-digit numberical serial numbers (column A) and a list of about 2,000 serial numbers that have been purchased for extended warranty (column), so some of the numbers in column B will also be in column A. Instead of going through every serial number in column A and manually searching column B to see if it was a serial that also had extended warranty purchased on it, I was hoping for a formula or some means, that would take all the serial numbers in column A and see if there is a duplicate of that same number in column B, indicating it has an extended warranty purchased on it. Does that make sense? "Biff" wrote: Hi! There are many ways to do this but what exactly do you mean by have one column search another? How do you designate that an extended warranty has been purchased? If the warranty was not purchased is the cell left blank? What result do want if the warranty was or was not purchased? Are the serial numbers unique? Biff "accessgrits" wrote in message ... I have two columns of data, one for serial numbers I have sent out, and one where extended warranties that were purchased. Instead of always using the find feature, is there a way to automate it so that the first column can search the second to see if an extended warranty was purchased? Thanks in advance for your help! |
#5
|
|||
|
|||
You have been so helpful, this worked!!! THANK YOU!!!
"Biff" wrote: Hi! OK, that's much clearer. Very easy to do: Insert a new column between column A and B or, just move column B over so that you can put a formula next to the values in column A. Assume your list in column A is from A1:A8000. The values in column B have now been moved over to column C in the range C1:C2000. In B1 enter this formula: =IF(COUNTIF(C$1:C$2000,A1),"Yes","") Double click the fill handle to copy the formula down to A8000. Done! Any serial numbers in column A that also appear in column B will be identified with a "Yes". Another thing that you could do to make those dupes easier to see is to sort them so that all the dupes with a "Yes" are on the top or bottom of the list. Select both columns A and B Goto DataSort Sort by column B descending to put the dupes at the top of the list. Biff "accessgrits" wrote in message ... Thanks for responding, what I'm trying to do is take a list of about 8,000 7-digit numberical serial numbers (column A) and a list of about 2,000 serial numbers that have been purchased for extended warranty (column), so some of the numbers in column B will also be in column A. Instead of going through every serial number in column A and manually searching column B to see if it was a serial that also had extended warranty purchased on it, I was hoping for a formula or some means, that would take all the serial numbers in column A and see if there is a duplicate of that same number in column B, indicating it has an extended warranty purchased on it. Does that make sense? "Biff" wrote: Hi! There are many ways to do this but what exactly do you mean by have one column search another? How do you designate that an extended warranty has been purchased? If the warranty was not purchased is the cell left blank? What result do want if the warranty was or was not purchased? Are the serial numbers unique? Biff "accessgrits" wrote in message ... I have two columns of data, one for serial numbers I have sent out, and one where extended warranties that were purchased. Instead of always using the find feature, is there a way to automate it so that the first column can search the second to see if an extended warranty was purchased? Thanks in advance for your help! |
#6
|
|||
|
|||
You're welcome! Thanks for the feedback.
Biff "accessgrits" wrote in message ... You have been so helpful, this worked!!! THANK YOU!!! "Biff" wrote: Hi! OK, that's much clearer. Very easy to do: Insert a new column between column A and B or, just move column B over so that you can put a formula next to the values in column A. Assume your list in column A is from A1:A8000. The values in column B have now been moved over to column C in the range C1:C2000. In B1 enter this formula: =IF(COUNTIF(C$1:C$2000,A1),"Yes","") Double click the fill handle to copy the formula down to A8000. Done! Any serial numbers in column A that also appear in column B will be identified with a "Yes". Another thing that you could do to make those dupes easier to see is to sort them so that all the dupes with a "Yes" are on the top or bottom of the list. Select both columns A and B Goto DataSort Sort by column B descending to put the dupes at the top of the list. Biff "accessgrits" wrote in message ... Thanks for responding, what I'm trying to do is take a list of about 8,000 7-digit numberical serial numbers (column A) and a list of about 2,000 serial numbers that have been purchased for extended warranty (column), so some of the numbers in column B will also be in column A. Instead of going through every serial number in column A and manually searching column B to see if it was a serial that also had extended warranty purchased on it, I was hoping for a formula or some means, that would take all the serial numbers in column A and see if there is a duplicate of that same number in column B, indicating it has an extended warranty purchased on it. Does that make sense? "Biff" wrote: Hi! There are many ways to do this but what exactly do you mean by have one column search another? How do you designate that an extended warranty has been purchased? If the warranty was not purchased is the cell left blank? What result do want if the warranty was or was not purchased? Are the serial numbers unique? Biff "accessgrits" wrote in message ... I have two columns of data, one for serial numbers I have sent out, and one where extended warranties that were purchased. Instead of always using the find feature, is there a way to automate it so that the first column can search the second to see if an extended warranty was purchased? Thanks in advance for your help! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo | jbsand1001 | General Discussion | 1 | April 28th, 2005 10:42 AM |
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | foofoo | General Discussion | 1 | April 2nd, 2005 12:02 AM |
Sorting data in various columns | carolmcg | General Discussion | 2 | October 14th, 2004 02:25 PM |
How to compare Multiple Rows and Columns to get data | Lee Li [MSFT] | Setting up and Configuration | 0 | September 15th, 2003 09:09 AM |