Compairing Tabs

[AC5FF]

I could probably do this through some extensive VLOOKUP formulas, but I was hoping to find an easier way about it... I'm also terrible (read as "newbe with no experience") at writing VB - especially in Excel!

What I have is a spreadsheet with numerous (15+) tabs. I use it for inventory reporting to accounting. Each sheet has a list of part numbers with associated quantities.

What I want to do is compare each sheet with each other and look for duplicates. I.E. I don't want to have part# 1000-01-0001 on sheet 2 AND on sheet 15.

Anyone know of an easy way to do this? I could "cut/paste" sheet to sheet and then add in a column to tell which sheet they came off of - but that seems time consuming as well. Just thought I would look for the "easy way" out.

Thanx!

 

[lemo]

maybe stack up all the data from 15+ tabs in one new tab and run a pivot table on this new big data block?

 

[AC5FF]

I kind of started a manual sort last night. Got about 20% through the data and gave up and went home. However I did just that - copied all the data stacked into a new tab and sorted by part# to quickly scan for duplicates.

I hadn't considered a pivot table .... May have to give that a try!

 

[lemo]

pivot table will automatically give you the number of times each part appears.

just don't forget to create an additional column to record the name of the tab where the data came from - if you need that info.

 

[AC5FF]

Okay... Looks like I got a bit of work to do. I got a great jump on it today though..

Here's what I've found and what I need some assistance with.

After compairing a running list of all my part numbers I have found numerous duplicates that should not be there. What I now need to do is sum up a bunch of columns I put together using VLOOKUP. However, if VLOOKUP cannot find a match I get a #N/A result - and summing up rows that include the #N/A does not work.

I thought I could get rid of the #N/A using an IF statement. I.E. IF VLOOKUP>=0 than use the VLOOKUP total else use ""... The rows that have values still showed up; but those that contained #N/A did not change. I also looked at find/replace and conditional formatting - but that didn't help either. Any ideas what I'm doing wrong??

FYI - here's my IF statement:
=IF(VLOOKUP(A2,'V INV'!$A$6:$C$173,3,FALSE)>=0,VLOOKUP(A2,'V INV'!$A$6:$C$173,3,FALSE),"")

I'm running out of ideas on this one.... Been playing off/on with it most of the day...

 

[Brianwarnock]

Can you use the if(ISNA .. Test to catch and change the #N/A result

Brian

 

[NBVC]

If you have 2007 or later, you can also use IFERROR

e.g.

=IFERROR(VLOOKUP(),"") will return a null if error is encountered.

You can also use SUMIF to ignore #N/A error

e.g.

=SUMIF(A1:A10,"<>#N/A")

sums values in A1:A10 ignoring the #N/A error.

 

[Brianwarnock]

I'll have a lot to learn if I ever join the modern world, ie post 2003

Brian

 

[AC5FF]

A week late - but it's done.
Thanks NVBC - SumIf function worked for me and I was able to do what was needed.