Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 11th 05, 04:52 PM
accessgrits
 
Posts: n/a
Default 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   Report Post  
Old May 11th 05, 07:31 PM
Biff
 
Posts: n/a
Default

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   Report Post  
Old May 11th 05, 07:56 PM
accessgrits
 
Posts: n/a
Default

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   Report Post  
Old May 11th 05, 11:07 PM
Biff
 
Posts: n/a
Default

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   Report Post  
Old May 12th 05, 03:36 PM
accessgrits
 
Posts: n/a
Default

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   Report Post  
Old May 12th 05, 06:32 PM
Biff
 
Posts: n/a
Default

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!










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sort column data with hidden columns - excel 2003 nanimadrina Excel Discussion (Misc queries) 2 April 26th 05 08:27 PM
change data in rows to be viewed in columns CHET Excel Discussion (Misc queries) 1 March 10th 05 09:16 PM
Merging columns of data Frank D. Nicodem, Jr. Excel Discussion (Misc queries) 2 March 4th 05 08:29 PM
Merging columns of data Frank D. Nicodem, Jr. Excel Discussion (Misc queries) 0 March 4th 05 08:16 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 05:55 PM


All times are GMT +1. The time now is 09:01 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017