# Comparing lists to find unique items

#### Tangaroo

##### New Member
I have 2 spreadsheets, one with 2006 data and one with 2007. In the 2007 data, I would like to insert a field that compares lines and tells me whether or not each line is "new" (does not appear in the 2006 sheet). Also, I am not simply comparing one cell, but several different ones, for example:

SCENARIO #1:
2006 data
Cell A1: X
Cell B1: X
Cell C1: X

2007 data (this scenario would return the value "OLD")
Cell A1: X
Cell B1: X
Cell C1: X
---------------------

SCENARIO #2:
2006 data
Cell A1: X
Cell B1: X
Cell C1: X

2007 data (this scenario would return the value "NEW")
Cell A1: X
Cell B1: X
Cell C1: Y

I'm assuming it's some sort of crazy VLOOKUP function, but please let me know what you think. Also, the lists are in no particular order, not sorted a specific way, etc., so each item is out of place.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### iggydarsa

##### Well-known Member
you mean something like this?

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
A
1book
2pen
3paper
4chair
5frame
2006data

<!-- ######### End Created Html Code To Copy ########## -->

</body></html>

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
AB
1computerNEW
2bookOLD
3tissueNEW
4chairOLD
5clockNEW
2007data

<!-- ######### End Created Html Code To Copy ########## -->

</body></html>

Last edited:

#### DonkeyOte

##### MrExcel MVP
Depending on the size of your range you don't really want to use an array formula x times ... I'd suggest if you can create a new value in each workbook, namely

="a:"&Ax&"b:"&Bx&"c:"&Cx

where x = row

assuming this in column D you could just use plain old MATCH function based on D value

Last edited:

#### Tangaroo

##### New Member
Thanks! I think that will work, but I also want to compare more than one field to qualify for new/old status, for example:

Take "book" - suppose in 2006, the cell next to it says "science" and in 2007, it says "math" next to it. Then in the 2007 sheet, I want to it say "NEW" b/c not all the fields match up. Thoughts?

#### DonkeyOte

##### MrExcel MVP
you have multiple criteria so a VLOOKUP solution won't do it for you I'm afraid... you need to use a SUMPRODUCT type arrangement... however if you have x1000 rows that's a LOT of sumproducts... so I'm not convinced that's the way you should go.

To use a standard VLOOKUP/MATCH solution I would suggest creating a unique reference / concatenation of the values you want to match in each workbook and then use a bog standard VLOOKUP/MATCH formula to see if the same Concatenation appears in the other file... if it doesn't it's new, if it does it isn't.

Replies
0
Views
246
Replies
2
Views
289
Replies
0
Views
421
Replies
6
Views
2K
Replies
0
Views
460

1,190,580
Messages
5,981,766
Members
439,734
Latest member
hmopheim

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back