Excel help - comparing lists

[CtownCyclone]

Need some help with Excel (we're running 2010).

I'm trying to compare two lists that should have the same items in them (auditing contractors, yay). I'm finding that the number of items in each list are different. Is there a way that I can have Excel look at the lists and tell me what is different in the lists? Basically, I want to be able to tell what is missing from one list or the other.

I've got the lists on separate tabs. I was thinking if I could get the items without a home shown on a third tab to be able to present to the folks who need to know.

There's about 1000 lines, so it's not something that I can quickly visualize.

Thanks, I'll hang up and listen.

 

[bpmdu]

vlookup each list against the other, that way you can see if any cells are missing from either list.
you could also use conditional formatting to highlight duplicate values between the two lists.

 

[azhuth09]

Create a new column in the list, then if( column x= column y, 1, 0)

 

[Tony]

All those methods work, but the conditional formatting is the quickest and cleanest imo. Plus it won't matter if the values you are comparing are on different rows or columns.

 

[cowgirl836]

vlookup each list against the other, that way you can see if any cells are missing from either list.
you could also use conditional formatting to highlight duplicate values between the two lists.

this as long as the names are identically formatted in both lists or can be easily made to do so. So as long as it says Smith, John on both tabs and not Smith, John and John A. Smith, you can do this very quickly.

 

[fatkid1974]

This link should be able to help.

 

[Agclone91]

All those methods work, but the conditional formatting is the quickest and cleanest imo. Plus it won't matter if the values you are comparing are on different rows or columns.

Conditional formatting is the easiest. Highlight the two columns and select the function to "Highlight duplicate values". Anything that isn't highlighted would be your number that only appears once.

 

[Tony]

Example applied:

 

[cowgirl836]

All those methods work, but the conditional formatting is the quickest and cleanest imo. Plus it won't matter if the values you are comparing are on different rows or columns.

how to you get this to work across multiple tabs?

 

[Tony]

how to you get this to work across multiple tabs?

I didn't read thoroughly, my mistake, this method won't work across multiple tabs.

 

[cowgirl836]

I didn't read thoroughly, my mistake, this method won't work across multiple tabs.

darn, I was going to use it too, but I wasn't seeing how when I tried.

 

[CtownCyclone]

vlookup each list against the other, that way you can see if any cells are missing from either list.
you could also use conditional formatting to highlight duplicate values between the two lists.

I'll have to refresh myself on vlookup (haven't used that in about 5 years).

I didn't read thoroughly, my mistake, this method won't work across multiple tabs.

I suppose for my purposes I could copy the columns I want to compare into a separate tab.

Thanks all (except you, fatkid).

 

[Tony]

That sounds like the best alternative, good luck!

As I like applied examples, I'm just going to go ahead and leave this here:

 

[besserheimerphat]

This should work across worksheets:

Compare ranges by using an array formula

In Excel, you can compare the cells in two ranges with an array formula. For
instance, to see if all of the values in A1:A100 are identical to those in
B1:B100, type this array formula:
=SUM(1*(A1:A100<>B1:B100))
Note: This is an array formula and it must be entered using
Ctrl-Shift-Enter.

The formula will return the number of corresponding cells that are different.
If the formula returns 0, it means that the two ranges are identical.

This will not work if you do not do the Ctl+Shft+Enter. Just noticed that this will tell you how many unmatched items you have, but will NOT identify the items. If you put everything into one worksheet and use the Highlight Duplicate Cells, you can then sort by cell color to bring the non-colored (non-duplicated) cells to the top.

 

[GTO]

Bravo to all the Excel experts on here! Nice to see I'm not the only one that uses it daily. I agree that vlookup is the easiest if the values are the same on both columns. Otherwise, I've used conditional formatting, or even nested if formulas when there are specific values I'm looking for. I haven't used an array formula before, so I'll give that a shot next time (thanks besserheimerphat).

 

[GMackey32]

I need some help with excel creating a couple of macros/auto sorting if any of you computer whiz's are up to it. It's somewhat complicated and I'm having a hard time finding answers clear enough for a novice excel user.

 

[cowgirl836]

I need some help with excel creating a couple of macros/auto sorting if any of you computer whiz's are up to it. It's somewhat complicated and I'm having a hard time finding answers clear enough for a novice excel user.

is it something a pivot table could be useful for? This is a bit beyond my realm. I usually hand it off to SAS people before I need to get terribly in-depth.

 

[GMackey32]

is it something a pivot table could be useful for? This is a bit beyond my realm. I usually hand it off to SAS people before I need to get terribly in-depth.

Not sure a pivot table is what I want. Basically I need a template that separates discard boxes into two tables of 24. Once I enter the data into specific cells, it'll auto sort this data into one of the tables based on weight. The tables also would need to be auto sorted within themselves based on ascending weight.

 

[cowgirl836]

Not sure a pivot table is what I want. Basically I need a template that separates discard boxes into two tables of 24. Once I enter the data into specific cells, it'll auto sort this data into one of the tables based on weight. The tables also would need to be auto sorted within themselves based on ascending weight.

ok yeah, I don't think it is then. I use pivot tables to sort out data already imported. Not sure an IF formula will help you either.

 

[GMackey32]

ok yeah, I don't think it is then. I use pivot tables to sort out data already imported. Not sure an IF formula will help you either.

I think an IF formula will only be part of it since I can use that for the cutoff point. But I'm just trying to figure out how to create some "entry cells" where I would enter said data and once I hit a created "Submit" button, it would automatically put that information into one of two tables. I have a friend that programs for IBM and he says it's possible, but I hate to put this on him when he has his own work to do.