Please help me with Excel

June 18, 2009 at 04:53:17
Specs: Windows Vista
I appreciate any help, thank you in advance.
Here is the problem I got. In Excel I got two columns - both consist of numbers... First column consist of over 700,000 (thousand) numbers, second - about 1,500 numbers... I need somehow to check if the numbers from 2nd column are repeated in first column... I don't want to be repeated but they might be... How do I do that?

Again thank you for any advice.


See More: Please help me with Excel

Report •


#1
June 18, 2009 at 05:48:18
In third column use =COUNTIF(B1,A:A)

Drag the formula down adjacent to the second column (assuming it is column B and first column is column A and the numbers start in the first row; otherwise adjust the formula)

Once done any item with a "1" next to it appeas in the first column.

HTH
Bryan


Report •

#2
June 18, 2009 at 06:01:00
We'd be glad to help.

First, just a tip about posting questions in a forum like this.

After you post, re-read your question and see if it is as clear as it could be. Try to read it as if you are seeing it for the first time and see if there might be more than one way to intepret it.

What do you mean by "repeated"? Are you asking for a way to determine if a given entry from the short list is in the long list or are you asking for a way to determine if a given entry from the short list is in the long list more than once, i.e. repeated in the long list.

In either case, take a look at COUNTIF()

=COUNTIF($A$1:$A$700000,B1)

Drag this down to B1500.

The resulting numbers will tell you how many times each item in Column B is in found A1:A700000.

You can dress that up a bit if you want, and that is where the "repeated" question comes in.

To simply tell you if it's in the list:

=IF(COUNTIF($A$1:$A$5,B1)>0,"It's in the long list", "It's not in the long list")

To tell you if it's in the list more than once:

=IF(COUNTIF($A$1:$A$5,B1)>1,"It's in the long list more than once", "It's not in the long list more than once")


Report •

#3
June 18, 2009 at 06:03:10
@Bryco

Did you test your suggestion? <g>


Report •

Related Solutions

#4
June 18, 2009 at 11:30:25
No, I got a call so did it in a hurry. It is backwards.
It should have read =COUNTIF(A:A,B1)

Regards,
Bryan


Report •

#5
June 18, 2009 at 11:46:34
Thank you, Bryco and DerbyDad03.
=COUNTIF worked perfectly, this is exactly what I needed. Sorry if my question was a bit confusing, English is my second language so it can be challenging once in a while.

Again, thank you for help


Report •


Ask Question