duplicate data over 10+ spreadsheets

o

New Member
Joined
Apr 5, 2002
Messages
5
I have about 12, 13, 14ish sheets where there's a list of items (Paper, Paper Clips, Staples, Tape and so on) in column B in each. These will occasionally have duplicate entries in themselves (in the very same columns on the very same sheet);or on another sheet in the same Excel file.

I have seen on numerous websites, including this one and the forum, how to quite easily use Conditional Formatting to (for example) highlight in bold red duplicate entries on the same list in the same sheet; what I need to do is cross over between this and compare the data being entered into all of these lists.

How do I do this?

(I've guessed at trying to name all of the lists' entires in a range called Items, and then applied conditional formatting to all, hoping that if things are in the same Range, perhaps it would cross check like that? But no, this doesn't work unfortunatly... so here I am :))

Thanks to all who try to respond to my desperate Excel-illiterate pleas for help :wink:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi there
Can you clarify, is the problem that column B of all sheets combined must only contain unique entries (ie paperclips must only appear once in a column B in the entire workbook)?
regards
Derek
 
Upvote 0
That's it exactly.

Paperclips must only ever appear once in ONE column B, on ONE worksheet.

I just need duplicates highlighting is all..... perhaps if some macro genius out there cares to also (god knows how) alert the data inputter to where the original entry duplicate to the one being entered is, i.e. an alert for....The data you are entering is already on the sheet "Financing Department"....

well, I'd be prepared to give em my first born :wink:
This message was edited by o on 2002-04-06 05:32
 
Upvote 0
Hi again
Well I'll have to think about the first born (bit cramped for bedroom space at the moment).
On an easier tack. Create a new worksheet.
Select A1, type = sign then click on B1 of your first worksheet and immediately press Enter. Scroll this formula down as far as needs be. Now select B1 and repeat the process with your second sheet, until all sheets have been done. Say that fills columns A:F. In A1 Select all that data and use this conditional formatting:
=COUNTIF($A:$F,A1)>1
If you have used more columns change the F to whatever.

Give this a go, meanwhile I'll have a ponder about your wish list

regards
Derek
 
Upvote 0
Sorry, but that doesn't seem to work?

It's as if I haven't done anything... :(
 
Upvote 0
Hi
Works for me. Did you select a colour for your conditional formatting?
I've almost got your duplicate warning working too
regards
Derek
 
Upvote 0
Hi
If still no luck, delete all the conditional formatting except for cell A1. Now select A1 and the rest of the data, go to conditional formatting and click OK.

Create a duplicate to test it and let me know.
regards
Derek

PS Check that quote marks have not been automatically added around your formula in the conditional formatting. If so, select all the conditionally formatted cells and remove the quote marks.
This message was edited by Derek on 2002-04-06 06:42
 
Upvote 0
you're right!!! it DOES work....

I created a new excel file to test it all out.... and it works fine with this...

But when I go back to the file i'm working with, i get HASHvalue!... eh i can't figure out a way to type the HASH sign, that one with four lines, aka pound sign for some strange reason.... because I'm running Windows emulated on an iBook running the Mac OS.... so the keyboard isn't correctly laid out... i.e. if i want a @ i have to hit the key labelled with "... aaaaggggh.

Back on track...

Before, with my new expeirment file, when i highlighted the cells i wanted to use, the first cell's data that i was highlighing was put into A1 on the Duplicate Entry worksheet.... this isn't so with my own file... it either puts the VALUE! thing in, or it's as if i'm typing the =sheet3!B:45 etc as text... and it jut displays this.

SO, what could be causing this then?

(as for the first born btw, i was also thinking perhaps not such a good idea... shipping the little monster out to Australia would be very expensive from the UK :wink:)
 
Upvote 0
as for the first born btw, i was also thinking perhaps not such a good idea... shipping the little monster out to Australia would be very expensive from the UK

we only send convicts, don't we ?!?!

big :wink:
 
Upvote 0
Hi again
Got to go to bed soon, so just leave you with this for now to play with in your experimental workbook:

When you have made your new worksheet, as explained, rename it "Duplicates"
In Cell V1 type this formula =COUNTIF(A:T,U1)
This allows you 20 columns (A:T) to list (by formula) the data in column B of 20 sheets

Now go to each worksheet in turn, right click the sheet tab, left click View Code
Paste this code into the white area

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
Sheets("Duplicates").Range("Duplicates!U1").Value = Target.Value
If Sheets("Duplicates").Range("Duplicates!V1").Value > 1 Then
MsgBox "Duplicate Entry. Check worksheet called Duplicates"
End If
End If
End Sub

Now when you make an entry in column B of any sheet this will happen:
Your entry will automatically go to cell U1 of worksheet Duplicates.
The formula in V1 will then return how many times this entry appears in the columns A:T.
If the answer is more than 1 it will trigger a warning message.

When you do the real thing, you might want to put your worksheet names in row 1 with your data starting from the second row, then your users will be able to see which sheets have the duplication.

With your actual file, if it is showing the formula as text try reformatting all cells as general.

Hope this works for you
If not, I'll check for any posts tomorrow.

regards
Derek
PS Chris, be nice or I'll point the bone at you!!!
This message was edited by Derek on 2002-04-06 07:31
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top