duplicate data over 10+ spreadsheets

o · Apr 6th, 2002, 06:00 AM

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

Derek · Apr 6th, 2002, 06:12 AM

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

o · Apr 6th, 2002, 06:30 AM

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

[ This Message was edited by: o on 2002-04-06 05:32 ]

Derek · Apr 6th, 2002, 06:47 AM

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

o · Apr 6th, 2002, 07:19 AM

Sorry, but that doesn't seem to work?

It's as if I haven't done anything...

Derek · Apr 6th, 2002, 07:27 AM

Hi
Works for me. Did you select a colour for your conditional formatting?
I've almost got your duplicate warning working too
regards
Derek

Derek · Apr 6th, 2002, 07:36 AM

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 ]

o · Apr 6th, 2002, 08:11 AM

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

)

Chris Davison · Apr 6th, 2002, 08:19 AM

Quote:

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

Derek · Apr 6th, 2002, 08:29 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 6th, 2002, 06:00 AM	#1
o New Member Join Date: Apr 2002 Location: England Posts: 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

Apr 6th, 2002, 06:12 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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

Apr 6th, 2002, 06:30 AM	#3
o New Member Join Date: Apr 2002 Location: England Posts: 5	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 [ This Message was edited by: o on 2002-04-06 05:32 ]

Apr 6th, 2002, 06:47 AM	#4
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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

Apr 6th, 2002, 07:19 AM	#5
o New Member Join Date: Apr 2002 Location: England Posts: 5	Sorry, but that doesn't seem to work? It's as if I haven't done anything...

Apr 6th, 2002, 07:27 AM	#6
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi Works for me. Did you select a colour for your conditional formatting? I've almost got your duplicate warning working too regards Derek

Apr 6th, 2002, 07:36 AM	#7
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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 ]

Apr 6th, 2002, 08:11 AM	#8
o New Member Join Date: Apr 2002 Location: England Posts: 5	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 )

Apr 6th, 2002, 08:29 AM	#10
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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 ]