Excel Compare Question

Microsoft Office 2007 enterprise
February 4, 2010 at 16:42:59
Specs: Windows XP
I have seen some posts about comparing items in Excel and I might have missed the one that talks about what I am trying to do.

What I am looking to do is compare the items in two cells and only extract the non duplicates into a new column. Row 1 compared to Row 2 and non duplicate items in Row 3.

Thanks for the help!!!!

See More: Excel Compare Question

Report •

February 4, 2010 at 17:52:17
I'm a bit confused.

re: "compare the items in two cells and only extract the non duplicates into a new column."

If Cell1 doesn't match Cell2, you will have 2 "non-duplicates". Do you really want them both in the same "new column"?

re: "Row 1 compared to Row 2 and non duplicate items in Row 3."

How does Row 3 enter into this? I thought you wanted the non-duplicates in a new column?

Report •

February 4, 2010 at 21:27:03
In row 3, you want to enter a formula like =if(A1=B1,"",A1) and in row 4 enter a formula like =if(A1=B1,"",B1)
If the values are the same you will have an empty cell. If the values are different, row 3 will have the value of row 1 and row 4 will have the value of row 2.

Report •

February 5, 2010 at 03:59:49

Not sure what you are trying to do, so this is just another possible option.

This formula allows you to compare the value in each cell in Column A, with all the cells in Column B, and place the value from column A in the same row in Column C if that value is not present in any cell (any row) in column B.

Data in columns A & B - see example below.
This formula in cell C2:

Note the $ signs.
Drag this formula to extend it down to the last row with data in column A.


	A	B	C
1	List 1	List 2	Not present
2	Name A	Name M	Name A
3	Name B	Name P	Name B
4	Name C	Name T	
5	Name D	Name Z	Name D
6	Name E	Name O	
7	Name F	Name Q	Name F
8	Name G	Name C	
9	Name H	Name L	Name H
10	Name I	Name W	Name I
11	Name J	Name E	
12	Name K	Name J	Name K
13	Name L	Name G	
14	Name M		
15	Name N		Name N
16	Name O		
17	Name P		
18	Name Q		
19	Name R		Name R
20	Name S		Name S
21	Name T		
22	Name U		Name U
23	Name V		Name V
24	Name W		
25	Name X		Name X
26	Name Y		Name Y
27	Name Z		

Report •

Related Solutions

February 5, 2010 at 08:15:03
Humar that is along the lines of what I am looking for. Sorry everyone if I was not clear in my explanation. Was trying to type it at work, while the wife was telling me about my daughter breaking her finger on my phone! Just one of those days.

The only thing different that I am looking for is I would want the non-duplicates pulled from List B once they were compared to List A. Where you have it you have the non-duplicates from List A once they were compared to List B.

Basically what I am doing is a have an existing data set located in my "List A" position and I am going to get a new data set that I want to extract just the non-duplicate pieces of data from. I will place the new data in my "List B" position and I want to compare "B" to "A". Once they are compared I would like for any items that are not on "A", but are on "B" to be produced in a new "List C". That will allow me to just add on any new additions from "List B" to my original "List A" data set.

See... clear as mud! AHAHAH Thanks again for the help everyone!

Report •

February 18, 2010 at 08:57:34
Thank you for your help Humar but I am going to add to this now...

You had helped me with this issue a few weeks ago, and I wanted to see if you could help me expand this Excel search I am trying to do a little farther.


The question I have is when doing my compare search, is it possible to tweak the follow formula to do the following steps as well?

- Do the compare from one sheet to another? (ex. column A on sheet 1 and column A on sheet 2, then post the compare results on column A sheet 3?

- Next what I would like to do is take additional data that is on the rows over as well. (ex. row 2 is not a matching result when compared so the formula lists it as a result once run. But row 2 has additional line data that goes from column A to column J lets say. Can we make the compare formula bring that data over as well to the results even though I am ONLY looking to compare the data in column A?)

Here is my current formula that I am doing a single column comparison of A to B with the results being posted on column C of the same sheet.


I have 4333 entries in column A and I have 8362 entries in column B and my formula is sitting in column C2

Thank you for your help with this!!!!

Report •

February 18, 2010 at 11:20:20

Using the same data as before with the first name of List 1 on Sheet1 column A Row 3
and the first name of List 2 on Sheet2 column A Row 3

Put this formula in Cell A3 on Sheet3
- change $A$14 as required.
Drag the formula down to extend it as before.

You will get the same results.

On sheet3 to get the 'additional data' use a further VLOOKUP()
In cell B3 put this:
- change $J$28 as required.
This first tests to see if there is an unmatched name in column A.
If there is, you know it must appear in column A of Sheet1, so we can use a simple VLOOKUP() to return data from columns to the right of the name.

VLOOKUP() returns data from a column to the right of the found item. As we want to use a series of consecutive columns, I have used COLUMN() in place of the number - it saves typing in 2, 3, 4 etc. COLUMN() will have the value 2 in column B and 10 in column J

As usual note the $ signs, to allow this formula in B3 to be dragged right, out to say cell J3 and then drag B3 to J3 down 8000 or so rows.


Report •

February 18, 2010 at 11:28:09
Thank you so much for your help. I will give this a try and let you know my results.

Thank you again!

Report •

February 18, 2010 at 16:56:54
Worked like a charm. Thank you once again for your help!!!

Report •

February 18, 2010 at 18:21:02
re: ...the wife was telling me about my daughter breaking her finger on my phone!

How did your daughter break her finger on your phone?

My daughter text's a lot but so far, no major injuries.

Report •

Ask Question