jjfrosty1

Q: How can I find duplicates in Numbers?

How can I find duplicates in Numbers?

Do I search each field or is there a short cut?

iMac (21.5-inch Mid 2011), OS X Mountain Lion (10.8.4)

Posted on Jul 19, 2013 9:47 AM

Close

Q: How can I find duplicates in Numbers?

  • All replies
  • Helpful answers

Previous Page 2 of 5 last Next
  • by SGIII,

    SGIII SGIII Feb 21, 2014 5:46 PM in response to writer_in_virginia
    Level 6 (10,796 points)
    Mac OS X
    Feb 21, 2014 5:46 PM in response to writer_in_virginia

    It's all too easy for gremlins to creep in when copying and pasting from a forum like this. All it takes is one little one.

     

    Glad it's working there now!

     

    SG

  • by mleke69,

    mleke69 mleke69 Mar 4, 2014 12:13 PM in response to SGIII
    Level 1 (0 points)
    Mar 4, 2014 12:13 PM in response to SGIII

    Thanks for this it worked great!  What would I need to change for it to do a spreadsheet?  I have an 4 columns in my spreadsheet, email, first name, last name and owner.  I need these rows to stay together but want to delete the duplicates.

     

    Thanks again!

  • by SGIII,

    SGIII SGIII Mar 4, 2014 12:18 PM in response to mleke69
    Level 6 (10,796 points)
    Mac OS X
    Mar 4, 2014 12:18 PM in response to mleke69

    Do you mean you have rows where the values all four columns are duplicates of another row?  Or are you looking for duplicates on one column only and want to remove the whole row when you encounter a duplicate?

     

    SG

  • by mleke69,

    mleke69 mleke69 Mar 4, 2014 12:44 PM in response to SGIII
    Level 1 (0 points)
    Mar 4, 2014 12:44 PM in response to SGIII

    All four columns are duplicates of another row.  This is a list of customers and some of the customers are entered several times, so I want to remove one whole row that duplicates another.

     

    Thanks!!

  • by SGIII,

    SGIII SGIII Mar 4, 2014 1:15 PM in response to mleke69
    Level 6 (10,796 points)
    Mac OS X
    Mar 4, 2014 1:15 PM in response to mleke69

    If you're only doing this occasionally, you could do this:

     

    Use the script to copy paste the distinct values from column A into column A of a new table.

     

    [Edit... will fill in rest. Had wrong instructions.]

     

    SG

  • by SGIII,

    SGIII SGIII Mar 4, 2014 1:28 PM in response to mleke69
    Level 6 (10,796 points)
    Mac OS X
    Mar 4, 2014 1:28 PM in response to mleke69

    If you are doing this occasionally, you could do this:

     

    1. Set up a new empty table with the same structure as the first
    2. Select the body cells in column A of your first table
    3. Run the script upthread
    4. Paste result into body cells of column B of your new table
    5. Use a VLOOKUP to fill in the rest of the cells in the new table
    6. Select all cells in the new table, command-c to copy, and Edit > Paste Formula Results. (This removes the formulas).

     

     

    Here, this looks like this:

     

    Screen Shot 2014-03-04 at 4.26.46 PM.png

     

    The formula in cell B2, copied right and down, is:

     

       =VLOOKUP($A2,Table 1::$A:$D,COLUMN(),FALSE)

     

    If you are doing this a lot in different sheets or documents then it would not be hard to have an AppleScript generate a new table. But this way, using the script and one VLOOKUP formula, is probably the easiest if you don't have to do this that often.

     

    SG

  • by mleke69,

    mleke69 mleke69 Mar 4, 2014 1:35 PM in response to SGIII
    Level 1 (0 points)
    Mar 4, 2014 1:35 PM in response to SGIII

    Thank you!  I am not sure if I am doing something wrong but when I run the script on column A in table 1 and paste it into column A in table 2 it works perfect, the duplicates are gone.  But when I use the index function the rows don't line up, it keeps the duplicate names so the email addresses no longer match to the correct first, last and owner.  Sorry if I am not explaining myself clearly, I hope this makes sense.

    Thanks!

  • by SGIII,

    SGIII SGIII Mar 4, 2014 1:38 PM in response to mleke69
    Level 6 (10,796 points)
    Mac OS X
    Mar 4, 2014 1:38 PM in response to mleke69

    But when I use the index function the rows don't line up, it keeps the duplicate names so the email addresses no longer match to the correct first, last and owner. 

     

    That's right. I misled you. Sorry about that INDEX formula; I wasn't quick enough to edit that post.  Use the VLOOKUP in my revised post.

     

    SG

  • by mleke69,

    mleke69 mleke69 Mar 4, 2014 2:00 PM in response to SGIII
    Level 1 (0 points)
    Mar 4, 2014 2:00 PM in response to SGIII

    Yes, that worked!!  Thank you SOOOO much!

  • by k8ty68,

    k8ty68 k8ty68 Mar 16, 2014 6:33 PM in response to SGIII
    Level 1 (0 points)
    Mar 16, 2014 6:33 PM in response to SGIII

    Thankyou so so much, my duplicate numbers are now highlighted! is there also a way to work out missing numbers from a range of numbers, say 1-100?Screen Shot 2014-03-17 at 2.29.42 pm.png

  • by Barry,

    Barry Barry Mar 17, 2014 12:55 AM in response to k8ty68
    Level 7 (32,697 points)
    iWork
    Mar 17, 2014 12:55 AM in response to k8ty68

    Hi k8ty,

     

    "is there also a way to work out missing numbers from a range of numbers, say 1-100?"

     

    You could put a count of each possible value into a table. In the sample below, I've used a range of 0-99 rather than 1-100. Values shown in the Data table are randomly generated. The count of each value is shown in the Count table. "Empty" cells actually contain a count of zero; conditional formatting is used to change the text colour to white, matching the cell fill colour, if the value is zero. The highest count, 4, was for the value 49, detemined by adding the value at the top of the column (40) to the value at the left of the row (9).

    Screen Shot 2014-03-17 at 12.46.02 AM.png

    Regards,

    Barry

  • by SGIII,

    SGIII SGIII Mar 17, 2014 8:57 AM in response to k8ty68
    Level 6 (10,796 points)
    Mac OS X
    Mar 17, 2014 8:57 AM in response to k8ty68

    is there also a way to work out missing numbers from a range of numbers, say 1-100

     

    Hi k8ty68,

     

    AppleScript is pretty good for solving this kind of problem.  Here is an example that checks the values in your selection range against a range from 1-100 and places the "missing" values on the clipboard for pasting wherever you want. 

     

    This assumes integers. If you have other values you want to check against you need to build matchList with something like:

     

    set matchList to {1.1, 2.3, 5.1, 4.4, 5...........}

     

    To use, paste the script into AppleScript Editor, select cells in a Numbers 3 table, run the script, click once in a cell where you want your missing values to appear in a column, and command-v to paste.  If you need this as a service you can paste it into a Run AppleScript action in Automator. If you have trouble with that, post, and I'll explain further how to do that.

     

    SG

     


    --select range in Numbers 3 table, run, paste values not in match list

    --https://discussions.apple.com/message/25190384?ac_cid=tw123456#25190384

    property rangeStart : 1

    property rangeEnd : 100

     

    --1. build list of values (here numbers are specified) to check against

    set matchList to {}

    repeat with i from rangeStart to rangeEnd

              copy i as number to end of matchList

    end repeat

     

    --2. make list of values in selection range

    tell application "Numbers" to tell front document to tell active sheet

              set selectedTable to first table whose class of selection range is range

              tell selectedTable to tell the selection range

                        set valuesList to {}

                        repeat with i from 1 to count cells

                                  set thisValue to value of cell i as text

                                  if thisValue is not "missing value" then copy thisValue as number to end of valuesList

                        end repeat

              end tell

    end tell

     

    --3. Go through each value in matchList and list it if it is not in valuesList

    set missingList to {}

    repeat with i from 1 to count matchList

              tell item i of matchList

                        if it is not in valuesList then

                                  copy it to end of missingList

                        end if

              end tell

    end repeat

     

     

    --4. Convert missingList to string for pasting

    set pasteStr to joinList(missingList, return) --change return to tab if paste horizontal

    set the clipboard to pasteStr

    display notification "Ready to paste " & (count missingList) & " missing values."

    return pasteStr

     

     

    --handler to convert list to string

    to joinList(aList, separator) --convert AS list to delimited string

              set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, separator}

              set lstStr to aList as string

              set text item delimiters of AppleScript to oTid

              return lstStr

    end joinList

     

    --end of script

     

     

     

     


  • by SGIII,

    SGIII SGIII Mar 17, 2014 9:44 AM in response to k8ty68
    Level 6 (10,796 points)
    Mac OS X
    Mar 17, 2014 9:44 AM in response to k8ty68

    And here's another way, not requiring AppleScript to identify missing values, though it does require Numbers 3.  I've used a range of 1-10.

     

    Screen Shot 2014-03-17 at 12.30.10 PM.png

     

    The following formula forces the values in Table 1 into a one-dimensional array (1-column) with this formula, using a the UNION.RANGES function new to Numbers 3.

     

     

         =INDEX(UNION.RANGES(FALSE,Table 1::$A$1,Table 1::$A$2:$C$10),1,ROW())

     

    Screen Shot 2014-03-17 at 12.34.50 PM.png

     

    Then in the Match table just list in column A the values in the range against which you want to check for missing values and in column B use a garden-variety lookup formula to look for values in the other table, and if they are not there, generate an error that here displays a "Missing" message.

     

        =IFERROR(VLOOKUP(A2,Values 1D::$A,1,FALSE),"Missing")

     

    Screen Shot 2014-03-17 at 12.38.33 PM.png

     

    SG

  • by Barry,

    Barry Barry Mar 17, 2014 1:14 PM in response to SGIII
    Level 7 (32,697 points)
    iWork
    Mar 17, 2014 1:14 PM in response to SGIII

    This version eliminates the Values ID table of SG's solution above. Counts (and the "Missing" tag for counts of zero) are calculated directly in the Match table:

    Screen Shot 2014-03-17 at 1.11.13 PM.png

    Table 2::B2, and filled down:

     

    =IF(COUNTIF(Table 1 :: $A$2:$C$10,A)<1,"Missing",COUNTIF(Table 1 :: $A$2:$C$10,A))

     

    Values 1 to 10 used in the example to fit here. Easily extendible to 1-100.

     

    Regards,

    Barry

  • by SGIII,

    SGIII SGIII Mar 17, 2014 1:43 PM in response to Barry
    Level 6 (10,796 points)
    Mac OS X
    Mar 17, 2014 1:43 PM in response to Barry

    Yes, thanks for the example. Definitely easier with just the two tables.  With Numbers 3 the AppleScript solution is easier still, especially if this kind of checking for missing values may be needed for other tables, sheets, or documents.  Just paste the script into AppleScript Editor, select the range of cells in a table, run, paste the results wherever wanted.

     

    SG

Previous Page 2 of 5 last Next