1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Subtract Difference of Spaces Between Unique Records in Excel

Discussion in 'Business Applications' started by Stisfa, May 24, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    I tried searching for this topic but it's obviously another set of search terms that I obviously have no idea in recreating, so I'm sorry if I'm double-posting for a set of principles that are probably already established.

    Running Microsoft Excel 2007 on Microsoft Windows XP SP3.

    So here's the idea:
    I've got thousands of records where many of them are going to be duplicates. The way I'm manipulating the data follows these steps:
    1) Sorting the Column by Lowest Value to Highest Value (or Alphabetically)
    2) Placing a Series Column Next to the Just Sorted Column
    3) Perform a Filter on the Duplicate Records with these options: "Filter the list, in place" with the option of "Unique records only"
    4) Then Subtract the Difference Between Each Unique Record's Series #

    I've attached a spreadsheet to demonstrate what I'm looking for.

    I would manually do this if I only had a few records to do this with but I'm still resulting in several thousand records even after performing the "Unique Filter" function. To put it simply, I know how to perform this in the manually laborious method that would require several hours of my time but I need something that can turn it into a several minute process.

    Once again, I apologize for repeating a problem that's probably already been answered.

    Thanks in advance!
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I took a looik at the sample you posted but cannot understand where what is comming from.
    Could you maybe post a sample with 25 rows of what you get and I ould take a look and see if I can make something out of it.
    You example doesn't make it clear for me and I therefore don't understand you question.
    If it isn't too much of a problem it might help understanding
     
  3. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    Lol, funny thing is, I finally figured it on my own (which rarely ever happens).

    Okay, here's another data set with 25 records/rows (ignore the first Worksheet, that was the first one I sent).

    I changed the method of counting the number of duplicates but I still needed it to be more "automatic".

    The first 3 Steps you see in this Worksheet are steps that MUST occur. The reason being that I need the names to be organized alphabetically then I need to know how many times this contestant has won.

    Basically, I needed it to:
    1) Alphabetized
    2) Hiding Duplicates
    3) Displaying the # of Times the Name is Duplicated

    My issue is that I have roughly 11,000 of these records and that even after hiding the duplicates, I'll still have over 8,000 unique records. I need Excel to automatically recognize that I'm counting the contents within a cell rather than have myself constantly type in the "Criteria".

    So I used the functionality of the COUNTIF Formula with the Criteria based on the values within the "Winning Contestant" Column.

    =COUNTIF(B2:B26,B2)
    This formula worked excellent, especially when I double-clicked the Fill Handle. The Formulas would then populate with the Range & Criteria following the cell they were adjacent to and since I had previously sorted the Data set, I found that it counted everything else below the adjacent cell which works great since the Unique Filter Function shows the top most value that is being duplicated in the spreadsheet. The only thing that I needed to change was the range, since some duplicates occur over several hundred times.

    So the formula that would perfectly work for me in this scenario looks something more like this:
    =COUNTIF(B2:B501,B2)
    The wider range accounts for some of the values that are duplicated several hundred times.

    I found the formula from this place:
    http://www.contextures.com/xlfunctions04.html

    I knew that this was something that must have been done before but it wasn't until I had a good night's sleep that changed my perspective, thus resulting in a change in the methodology of performing this function. Funny how a simple change of viewpoint can make such a profound difference. Once again, apologies to the TSGF Community that has to put up with individuals such as myself who think creatively enough to hurt themselves :D.

    Thanks for the help you've offered Keebellah.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I had the idea that I didn't do much for you but, okay, glad to have been of use.
    My question now, This could be done simply using vba (macro).
    But then, which date do you want to show or do you only want the constant's name and the time he / she wins (is in the list)?
    If the contents of the the first tab are the basics then it could be done quite simple and reurn a name sorted list with the numbers
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Just for fun, I wrote a macro for the sheet you sent me.
    Press (click) the Smiley and the macro runs (creates a sheet named TEMP) :)
     

    Attached Files:

  6. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    I haven't checked the macro out yet but thanks for helping out.
     
  7. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    Just a note for those who are complete noobs like me:
    If the range you have set in your formula is not going to change & you would like the range to be constant after you use the Fill Handle, then change the formula from:
    =COUNTIF(B2:B26,B2)
    to
    =COUNTIF($B$2:$B$26,B2)

    I never knew what the dollar sign did until today, lol. I realize this is verbatim for experienced Excel users but this is brand new to me and I think it's amazing that I can prohibit the Fill Handle from following the sequence of the Fill (as far as my ranges, at least).
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/925047

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice