Excel help needed.

[.Mark]

Hope someone can help or I have the next week to look forward to staring at columns of numbers!
Using Excel (2002 or XP, not sure but think XP) I have 2 columns only several thousand lines long and need to know how many of column B are represented in column A and if possible highlight any in column B that do not appear in column A - did that make sense?

TIA.

[whoozit]

Is this a one-off analysis or will the data be changing?

[.Mark]

I bloomin' hope a one off!

[DustyC]

How about using the autofilter to show only the blanks or no blanks in column A and then go from there.

[.Mark]

DustyC said:

How about using the autofilter to show only the blanks or no blanks in column A and then go from there.

Lost me already Adam.

[DustyC]

Yeah, that should do it, just tried and its possible.

1. Switch on autofilter (in menubar/data/filter..)
2. selected non blanks from the drop down list in column A
3. Highlight all those shown in column B#

[DustyC]

Yeah, that should do it, just tried and its possible.

1. Switch on autofilter (in menubar/data/filter..)
2. selected non blanks from the drop down list in column A
3. Highlight all those shown in column B

As for how many occurances of this there are Im not yet sure. Hang on, yes I am!
After doing the above;
1.Select all the cells in column A (when non blanks is selcted as above)
2. Copy them to a new spreadsheet
3. Go to the bottom row of the column and the number of the row will represent the amount of occurances in which column A occurs.

[DustyC]

Ah, just read the original post back again, thought it seemed to easy! Looks like its more specific than I first thought.

Im now guessing you need to see how many times an item of specific data from B occurs in A. Is that correct?

ie; if "XYZ123" occurs in a cell in column B then how many times does it occur in column A. Is that what you need to do.

If so you could still use the autofilter to do this, or perhaps the COUNTIF formula

[DustyC]

Blimey, I think that was my 5000th post in less than a year. First posted on March 25th 2003.

(Posts are not updated automatically now I believe).

[whoozit]

DustyC said:

If so you could still use the autofilter to do this, or perhaps the COUNTIF formula

Yup, Countif is what sprang to mind, that will give you a count of zero for all items in B that aren't in A as well as counting the other numbers of occurrences. Only works if the items in A are identical to the occurrences in B.

[m-five]

.Mark said:

Hope someone can help or I have the next week to look forward to staring at columns of numbers! Using Excel (2002 or XP, not sure but think XP) I have 2 columns only several thousand lines long and need to know how many of column B are represented in column A and if possible highlight any in column B that do not appear in column A - did that make sense? TIA.

Let me get this straight...

You have two columns, thousands of lines long, and you want to see if the item in cell B1 occurs anywhere in column A, then see if the item in cell B2 occurs anywhere in column A, ad infinitum?

If so you can do a simple VLOOKUP once the data in column A is sorted in ascending order.

Format is:
=VLOOKUP(lookup_value,table_array,column_index)

where:
lookup_value is cell B1, B2, B3, etc
table_array is A:A
column_index is 1 (returns value from column A)

Then you can do a conditional format on the formula column along the lines of 'if cell=0 then make cell red' as anything that isn't found will result in a value of 0 - this only works if your list does not contain 0 values of course.

[DustyC]

Alternately when you run out of fingers and toes lets us know and we'll start counting. There is 30,000 of us now so should be no problem

(Excuse me, having a funny five)

[.Mark]

Right. OK, thanks all. I'll er...get started.

[.Mark]

Can't do it. I don't understand. Can someone go through it in language a numpty could understand?

[DustyC]

I'll try and help...
do you know what auto filter is?

(I would call but its awkward today).

[atom290]

you have mail

[.Mark]

atom290 said:

you have mail

With the expert help of Jon I have this sorted. Many, many thanks mate - I owe you one



also thanks to Julian who sent me some very interesting stuff that wouldn't actually have solved this problem but will certainly help me out with some other matters.

The power of PistonHeads strikes again!

[DustyC]

Glad you got it sorted.
Had I been not so busy I would have phoned you. (Especially since you are a homie!)

if that is not the correct spelling of the phrase short for "home boy" than I apologise. I guess im just not "down with it".

[.Mark]

DustyC said:

Glad you got it sorted. Had I been not so busy I would have phoned you. (Especially since you are a homie!) if that is not the correct spelling of the phrase short for "home boy" than I apologise. I guess im just not "down with it".

Nurse!

[atom290]

.Mark said:

With the expert help of Jon I have this sorted. Many, many thanks mate - I owe you one

Well I'm just glad you got it sorted. The problem wasnt something an on screen function would have been able to cope with, but a little macro and off you go!!!

I look forward to the beer one day