Page 2 of 6 FirstFirst 1234 ... LastLast
Results 11 to 20 of 54

Thread: Excel Sorcerers!

  1. #11
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,603
    Oh mighty Excel Sorcerers and Corvette lovers everywhere, wons't thou please favor us with a wicked cool "=if" formula that I used to know how to write but have forgotten?

    I have a column of values. They're all numbers currently, but I used to use this formula for words too. I will sort the column so that any duplicate values will be listed sequentially, like this:

    1
    2
    3
    3
    3
    3
    4
    5
    6

    I'm looking for this:

    If A2 = A1, write "duplicate" in B1. If A2 does not = A1, write "not duplicate".

    Then I would paste the formula down column B to get this desired result:

    1 not duplicate
    2 not duplicate
    3 duplicate
    3 duplicate
    3 duplicate
    3 not duplicate
    4 not duplicate
    5 not duplicate
    6 not duplicate

    My everlasting gratitude awaits your formulaic wizardry.

  2. #12
    Senior Member
    Join Date
    Jan 2014
    Posts
    10,171
    How does the formula know how far down Column A to look? Like, would it ever go:

    1
    2
    3
    4
    1
    1
    1

    and then have to "put duplicate" in B1 because 1 appears again in 5-7?

  3. #13
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,603
    Nope. By starting it as =if A2 = A1 (that may not be the correct code, but you get the idea), you're asking "is the cell right below this one a duplicate or not?"

    As you paste/drag the formula down, it automatically changes as it goes down, like this:

    A2 = A1?
    A3 = A2?
    A4 = A3?
    A5 = A4?
    ...and so on.

    That's why I sort the list first, so your list becomes this:

    1
    1
    1
    1
    2
    3
    4

    There is a handy tool in the Data menu in Excel 2010 (and I think 2007 too, but not 2003) called Remove Duplicates that works without sorting, so in this case...

    Like, would it ever go:

    1
    2
    3
    4
    1
    1
    1

    and then have to "put duplicate" in B1 because 1 appears again in 5-7?
    ...the Remove Duplicates command would recognize and remove three of the ones in that list without sorting. I will also give you a report when finished: "67 duplicates removed; 147 unique values remain", or something like that.

    However, I'm not trying to remove duplicates. I just want to identify them.

  4. #14
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,603
    Aha! I figured it out - too easy using the Formula Wizard. I was trying to type the formula in from memory.

    It's shown here for Column M, which is where I started. I have a header row, so M2 was the first cell to check.

    =IF(M3=M2,"Duplicate","Not Duplicate")

    You can M3=M2 to whatever you want - A2=A1, or wherever you're starting.

    Also, you can change the words in quotation marks to whatever you like.

    =IF(M3=M2,"Corvettes are just re-badged Malibus","Corvettes are unbelievably badass")

    Oh well...if nothing else, I now know where to find this formula the next time I forget.

  5. #15
    Senior Member
    Join Date
    Jan 2014
    Posts
    10,171
    That's why I wondered... it's only a problem if the potentially duplicate numbers are spread out... if you can sort by that column, it becomes much easier!

  6. #16
    Corvette Enthusiast Kchrpm's Avatar
    Join Date
    Jan 2014
    Location
    Cincinnati, OH
    Posts
    6,899
    You can do it without sorting.

    =if(countif(A$2:A2,A2)=1,"Not Duplicate","Duplicate")

    It counts how many times the value in A2 shows up in any cell between the top of the list (A$2) and the current entry (A2).
    Get that weak shit off my track

  7. #17
    Senior Member
    Join Date
    Jan 2014
    Posts
    10,171
    Nice!

    Is there a similar way to make it count the entire column A, or even a wide range of column A?

  8. #18
    Corvette Enthusiast Kchrpm's Avatar
    Join Date
    Jan 2014
    Location
    Cincinnati, OH
    Posts
    6,899
    Yeah, just use A:A instead of A$2:A2

    That won't find duplicates, though, it will only find items that are duplicated. If something shows up 5 times, they will all return 5. The original formula I wrote will result 1, 2, 3, 4 then 5.
    Get that weak shit off my track

  9. #19
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,603
    Quote Originally Posted by Kchrpm View Post
    You can do it without sorting.

    =if(countif(A$2:A2,A2)=1,"Not Duplicate","Duplicate")

    It counts how many times the value in A2 shows up in any cell between the top of the list (A$2) and the current entry (A2).
    Great stuff. Thanks K!

    I saved that on my new cheat sheet. I used to have one at my last job but never bothered to put one together here, until now.

    The neat thing about Excel is there are lots of ways of getting the same results. I thought I was pretty cool by sorting a column and then labeling duplicates, but someone always knows a better/faster/more elegant trick.
    Last edited by George; February 26th, 2014 at 07:18 AM.

  10. #20
    Corvette Enthusiast Kchrpm's Avatar
    Join Date
    Jan 2014
    Location
    Cincinnati, OH
    Posts
    6,899
    Someone used to have to do it with thousands of new entries every day
    Get that weak shit off my track

Posting Permissions

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