SOLVED

# How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Copper Contributor

# How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Hi,

I have a spreadsheet. Each Tab (in month order) Starting July, August, September and so on. In Column A a store number will be entered. This store number cannot be duplicated across any of the other tabs/months.  What formula can I use to check for any duplicates entered on a tab? For example, say the current month is December and 2687 is entered, but its also in July. How can I show that its a duplication to another month?

7 Replies

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Create a new sheet.

Enter the names of the tabs in - for example - A1:A6.

With this range selected, click in the Name/Address box on the left hand side of the formula bar, type Months and press Enter. This creates a named range.

Let's say you want to apply this to A2:A50 on each month sheet.

Select A2:A50 on the July sheet.

A2 should be the active cell in the selection.

On the Data tab of the ribbon, click Data Validation.

Select Custom from the Allow drop-down.

Enter the following formula:

=SUM(COUNTIF(INDIRECT(Months&"!A2:A50"), A2))=1

Activate the Error Alert tab and enter an appropriate message to be displayed when the user tries to enter a duplicate value.

Click OK.

Repeat these steps for each of the other month sheets.

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Here's a 365 solution.

Presuming the data is in A1:A1000 in Sheets July to December.

Step 1: Create a named item to stack data and discard blanks:

``=TOCOL(July:December!\$A\$2:\$A\$1000,1)``

Step 2: Create conditional formatting rule to apply fill color to dupes:

``=SUM(N(\$A2=AllStores))>1``

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Hi, How do I go about Step 1? Thanks

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

@HansVogelaar  - hi, I have followed your steps exactly and its throwing up an error?

best response confirmed by Lennox2525 (Copper Contributor)
Solution

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Here's a short gif of step 1:

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Thank you - I didnt know how to do that :) Perfect!

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

You're welcome!
1 best response

Accepted Solutions
best response confirmed by Lennox2525 (Copper Contributor)
Solution

# Re: How can I check for duplicates in Column A over multiple tabs (in months) on Excel Spreadsheet?

Here's a short gif of step 1: