Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: duplicate data over 10+ spreadsheets

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, but that doesn't seem to work?

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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 )

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •