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.

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

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.repeatedin 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")

@Bryco Did you test your suggestion? <g>

No, I got a call so did it in a hurry. It is backwards.

It should have read =COUNTIF(A:A,B1)Regards,

Bryan

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

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History