Identifying duplicates across multiple tabs with VBA

**0nyx1756** · 11-17-2011, 12:19 PM

Hi everyone,

I currently have four tabs which look at sales-people.

The first tab is when a salesman sells a car

Staff number Car type Amount
A123456 Renault clio £1,000.00
A224321 Vauxhall Corsa £2,000.00
A555441 Renault Megane £1,500.00

The second is where they successfully refer the purchaser to buy insurance

Staff number Car type Amount
A123456 Renault clio £200.00
A555441 Renault Megane £400.00
A123456 Ford Focus £300.00

The third is where a salesman has taken a customer for a test drive

Staff number Car type date
A123456 Ford Focus 15/10/2011
A000931 Nissan Micra 18/10/2011

The fourth is where someone receives good customer feedback

Staff number Feedback from Feedback date
A123456 Mr Smith 18/11/2011

What I would like my spreadsheet to do is look at all the above tabs and give me a list of staff numbers that appear on two or more tabs so we can see how staff are getting on. This should be put on the ‘overall’ tab.

I’ve got the following code:

Please Login or Register  to view this content.

Unfortunately this only looks at two tabs and I can’t seem to get it to look at all four of them.

Ideally I’d like to avoid changing the format of the sheets (need to be separate tabs) and avoid pivot tables if possible.

Can anyone help?

**MarvinP** · 11-17-2011, 01:14 PM

Hi Onyx1756 and welcome to the forum,

How about this process -
Copy all 4 sheets staff numbers to a blank 5th sheet. On this sheet use a countif formula in a helper column that counts the number of that staff number above it. Then remove all rows based on the helper column and only leave those staff numbers that have more than 1 hit. To reduce this list to unique, use an Advanced Filter and check the "Unique" box.

If you need to do this only once, doing it by hand might take 3 minutes. If you need to do it multiple times then VBA may be appropriate. We need a sample of your data to show you how if the above doesn't make sense.

**Leith Ross** · 11-17-2011, 01:57 PM

Hello onyx1756,

Welcome to the Forum!

I made a few changes to your code. It will now append the staff number to the sheet "Overall".

Please Login or Register  to view this content.

**0nyx1756** · 11-18-2011, 05:01 AM

Hi guys thanks for your response and welcome. MarvinP - I have to do it repeatedly so think VBA would be more appropriate. Leith Ross, that code brings up a subscript error

Enclosed is the spreadsheet.

**0nyx1756** · 11-18-2011, 11:02 AM

I've come up with the current code:

Please Login or Register  to view this content.

Unfortunately it seems to look to see what duplicates are between car sales and the rest of the sheet but what, for example, if someone has sold insurance and received feedback but not sold a car? It does not come up then using the current code.

So you can understand what im doing and why: every time someone sells a car they will be put on this spreadsheet so if they sell ten cars a month, they will appear on there ten times. I want to check car sales, insurance, test drives and feedback to identify staff members who appear one more than one tab in order to single them out for praise.