[Junaid169]

Excel 2007:
I want to be able to compare TWO (2) Columns or two spread sheet.

For Example

I have a Column of students and corresponding records; Then
I have another column similar to the first one but essentially more/less students.

I want to be able to compare both columns and get rows that are not similar/duplicate.

Or in another scenario if these columns sit in one sheet one following the other can it be done this way.

Can some body please help with this!!

[JBeaucaire]

You need to be more clear about WHAT you want to happen as a result of this "comparison". Can you mock up a sample workbook with a few fake names in two lists, then show what you want the result of this comparison to be?

Are you talking about "highlighting" duplicates?
Flagging "match" in another column?
Extend the larger list by finding the new entries in the smaller list?

What? What's the goal here?

[Junaid169]

What I want is something in Data Base system would be the minus in the joining of two tables if talking in terms of union of two tables.
Okay I'll give you an example.

Spread sheet one
Col A Col B
Smith, Black $ 50.00
William, Sue $ 25.00
Henry, Joe $30.00
Arther, Richard $ 50.00

Spread Sheet two
Col A Col B
Smith, Black $ 50.00
Raiz, Ali $ 35.00
William, Sue $ 25.00
Henry, Joe $30.00
Arther, Richard $ 50.00
Powell, Ricardo $ 23.00


Okay I think that's enough Data...

I want to compare and I want the result to be
Col A Col B

Raiz, Ali $ 35.00
Powell, Ricardo $ 23.00

[mdosh01]

Check the following link from Microsoft:

How to compare data in two columns to find duplicates in Excel

To use this method, you will need to concatenate the two columns of data and then do the compare (assuming both columns must match). Then, instead of returning blank on error, you can have it return the value that doesn't match.

This can be done across sheets as well. You will also need to take into account that your columns have different amounts of data as well.

[JBeaucaire]

To do this simply requires a couple of helper cells. First we need to flag items in the long sheet that are unique from the short sheet, in column C:
=COUNTIF(Sheet1!$A$1:$A$4,Sheet2!A1)
... copied down the whole set to get an array of 1s and 0s.

Then a count of how many zeros we got, in D1:
=COUNTIF($C$1:$C$6,0)

Then the new table. I apologize for the array formula required to do something appearing as simple as this, but a formula has to work pretty hard to make a dynamic list like this happen. It's doable, but it's a lot of hoops to run through.

The first formula is this, entered with CTRL-SHIFT-ENTER to activate the array, do not just press ENTER or it won't work.
=IF(ROW($C$1:$C$6)<=$D$1,INDEX($A$1:$A$6,SMALL(IF( $C$1:$C$6=0,ROW($C$1:$C$6),999),ROW())),"")

Anyway, here you go, take a look. Attached is a mockup sheet showing how it's laid out.

[Nostradamus_pk]

To do this simply requires a couple of helper cells. First we need to flag items in the long sheet that are unique from the short sheet, in column C:
=COUNTIF(Sheet1!$A$1:$A$4,Sheet2!A1)
...copied down the whole set to get an array of 1s and 0s.

Then a count of how many zeros we got, in D1:
=COUNTIF($C$1:$C$6,0)

Then the new table. I apologize for the array formula required to do something appearing as simple as this, but a formula has to work pretty hard to make a dynamic list like this happen. It's doable, but it's a lot of hoops to run through.

The first formula is this, entered with CTRL-SHIFT-ENTER to activate the array, do not just press ENTER or it won't work.
=IF(ROW($C$1:$C$6)<=$D$1,INDEX($A$1:$A$6,SMALL(IF( $C$1:$C$6=0,ROW($C$1:$C$6),999),ROW())),"")

Anyway, here you go, take a look. Attached is a mockup sheet showing how it's laid out.

You want me to flag couple of hundred records every day!!

[JBeaucaire]

I'm not sure I understand the question. The helper columns I've added have a formula in them. How long does it take to copy one formula down 700 cells? 3 seconds? That sounds easy to me.

And the count formula in D1 can be changed to count the entire C column as long as nothing else is going to be in that column:

=COUNTIF($C:$C,0)
.. so that won't ever have to be changed.

And calm down. You asked us if it was possible, which it is. Once this array is on your sheet, you should be able to drop any data into columns A and B you want and the list just "appears". There is minimal work, if any, to using this once it's set up.

We don't know your full work process, but we are showing you how a sheet can produce the results you asked for.

Here's the sheet again with the modification noted above and a search range that now goes down the ABC columns 700 rows. Is that enough to get the job done for you?

Keep the permanent list on Sheet1 and drop in the data to "check" on Sheet2 to get a new list of new items.

[mdosh01]

I'm not sure I understand the question. The helper columns I've added have a formula in them. How long does it take to copy one formula down 700 cells? 3 seconds? That sounds easy to me.

<snip>

Careful. It wasn't Junaid169, who asked the original question, that questioned your response.

[Nostradamus_pk]

Careful. It wasn't Junaid169, who asked the original question, that questioned your response.

I had the same problem!
So I posted my own thing in there

[JBeaucaire]

The request to "get unique" entries comparing two lists is simplest with an Advanced Filter. It's what it was designed for. To get the unique names from two lists:

1. Copy the two lists into a single column
2. Highlight the entire set
3. Click on Data > Filter > Advanced Filter
4. Copy to another location
5. Set the Copy To: destination
6. Check the Unique records only box
7. OK

Since you put the two lists into one column, you created a situation wherein there were duplicates. Running the steps above finds the ones that DON'T have duplicates and gives you a list.

That technique is simple. But people continually want a "dynamic table" that updates itself, so the very heavy lifting in the SMALL(IF)ARRAY method exists. But it's a lot to wrap your head around.

I use the Advanced Filter for on-demand stuff when I need it. I use the SMALL(IF)ARRAY method for small dynamic lists, so they both have their uses.