+ Reply to Thread
Results 1 to 11 of 11

Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

  1. #1
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    15

    Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    Hey Guys -

    I've got two spreadsheets of data which I'm trying to gather statistics from for comparison. The data is in a format similar to the below:
    Column A Column B Column C Column D
    Event #1 Value #1 Detail #1 ID #1
    Event #2 Value #2 Detail #2 ID #2
    Event #1 Value #1 Detail #1 ID #1
    Event #3 Value #2 Detail #2 ID #2
    Event #2 Value #2 Detail #2 ID #2


    Out of the 1,500+ rows, column A had only 190 unique cells, however; sometimes the cells B, C, or E may be different although A is a duplicate. For example, cell A for the 2nd row above is duplicate with other cells in column A, but it's cell in the D column differs. (Hope that makes sense)

    I've obviously got a count of how many are unique, but what I'm looking for is a way to:
    - Create a list of all unique rows
    - Add a cell to each which shows how many times it was listed in the data
    - Create a 2nd list similar to the above, but only for the unique Events in column A - not each entire unique row

    I considered filtering them then manually evaluating, but with so many unique values; that would have taken forever.

    Any suggestions? Thank You!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    This is an array formula. They are resource heavy. Expect that many rows of data to slow workbook calculations. You might reduce that load somewhat by pre-concatenating those fields in a separate column. Here they are done in formula.

    Array entered in F2 filled down and across until you get blanks.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    ColumnA
    ColumnB
    ColumnC
    ColumnD
    ColumnA
    ColumnB
    ColumnC
    ColumnD
    2
    Event#1
    Value#1
    Detail#1
    ID#1
    Event#1
    Value#1
    Detail#1
    ID#1
    3
    Event#2
    Value#2
    Detail#2
    ID#2
    Event#2
    Value#2
    Detail#2
    ID#2
    4
    Event#1
    Value#1
    Detail#1
    ID#1
    Event#3
    Value#2
    Detail#2
    ID#2
    5
    Event#3
    Value#2
    Detail#2
    ID#2
    6
    Event#2
    Value#2
    Detail#2
    ID#2
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    Another way: A non array non concatenated approach is to use some helper columns.

    Here I used the fill series feature to set row numbers a little beyond anticipated need. Then in F2 and filled down identifies the 1st of each unique combination and assigns a row number to it.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In H2 filled down and across until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then to get the counts of each in L2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    ColumnA
    ColumnB
    ColumnC
    ColumnD
    ColumnA
    ColumnB
    ColumnC
    ColumnD
    2
    1
    Event#1
    Value#1
    Detail#1
    ID#1
    1
    Event#1
    Value#1
    Detail#1
    ID#1
    2
    3
    2
    Event#2
    Value#2
    Detail#2
    ID#2
    2
    Event#2
    Value#2
    Detail#2
    ID#2
    2
    4
    3
    Event#1
    Value#1
    Detail#1
    ID#1
    FALSE
    Event#3
    Value#2
    Detail#2
    ID#2
    1
    5
    4
    Event#3
    Value#2
    Detail#2
    ID#2
    4
    6
    5
    Event#2
    Value#2
    Detail#2
    ID#2
    FALSE
    7
    6
    8
    7
    9
    8
    10
    9
    11
    10
    Last edited by FlameRetired; 06-28-2019 at 07:50 PM.

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    Dave has beaten me too it, but I am posting this as the approach is a little different.

    Rather than computing the unique rows by formula I have done it procedurally using the "Remove duplicates" data tool. The advantage of this approach is that the formulas involved are considerably simpler, the disadvantage is the added manual steps and the need to repeat them if this is not a "one-off" exercise.

    I started a new workbook and replicated your posted values in columns A:D.
    In col-E I created a unique ID simply by concatenating all 4 columns. In E1 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Col-F is a count of how many times each UID occurs. In F2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    To get a list of UIDs I have assumed this is a "one-off" exercise and done it procedurally rather than by formula:
    • Create a new worksheet "UniqueRows"
    • Copy/Paste the sheet1!col-E into UniqueRows!A1
    • Select A1
    • On the ribbon: Data > Remove Duplicates
    • Select "My data has headers" then OK

    You now have a list of unique UIDs.

    See the attached workbook.
    Attached Files Attached Files
    Last edited by GeoffW283; 06-28-2019 at 08:22 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  5. #5
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    15

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    Good Afternoon -

    I really appreciate your reply, FlameRetired. My data from the larger spreadsheet has 5396 rows, so copied the formula you provided above, then replaced all instances of $6 with $5396. Finally, I pasted it into F2 of the spreadsheet. It highlighted the first 4 columns as shown in the screenshot, however; when clicking out of F2 the cell was left empty. I drug the cell to replicate the formula over and down a few cells, but still nothing was populated. Clicking back in F2, I see the formula, so know it saved. Below is also the formula I used in F2:
    Please Login or Register  to view this content.
    I also tried the formula exactly as you posted it to test just in the first few rows, but got the same result. Am I doing something incorrectly?
    excel1.png

    Thank You
    Last edited by bzowk; 06-29-2019 at 03:10 PM.

  6. #6
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    15

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    By the way, I tried your 2nd suggestion as well. After placing the formula in F2, then double clicking lower right of cell to replicate it down; I found that all cells in F stated "FALSE". I then started by putting the 2nd formula in H2 as instructed and replicated it down a few, but all of those came up empty. Not sure if I'm doing something incorrectly since this one also isn't working or what. Please advise -
    excel2.jpg

    Thank You

    UPDATE

    I realized after posting that I had failed to go back and make an empty A column prior to testing. I did so plus deleted the extra column (E) containing all the same data, then used your formula in column F which was the next blank one. Unfortunately, I got the same results as shown below with all showing "FALSE" and column A empty. Any suggestions? Thanks
    excel4.jpg
    Last edited by bzowk; 06-29-2019 at 03:10 PM.

  7. #7
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    15

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    Thanks for replying, GeoffW283 -

    I see what you are doing so gave it a shot. Quick note, I realize my data has an extra column than my example, but all values in it are common so I deleted it when trying yours...

    The first part worked without issue as in column E it created a value that combined all cells. Unfortunately, when I started with the 2nd formula in column F; all results were "#VALUE!". I tried copying column E, pasting it into a new column as values, then replacing column E so that F would be referencing values instead of a formula's results (didn't know if that would make a difference), but got the same results as shown below:
    excel3.jpg

    Any suggestions? Thank You
    Last edited by bzowk; 06-29-2019 at 03:09 PM.

  8. #8
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    The only way I can think of to get the countif() in col-E to return a #VALUE! error is if the total length of the string in col-E is greater than 256 characters. I can't tell from your posted picture, but is that what is happening? If so, then do you need a concatenation of all four columns to form a unique string or is there a subset of columns that would still be unique but not exceed 256 characters? If this isn't the reason for the #value! error then please post a subset of your workbook - just enough to reproduce the #value! error.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    I agree with Geoff.

    The time has arrived for a workbook upload.

    If you do not know how to do this:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.

  10. #10
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    15

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    I finally got it working - Thanks!!! Turns out the count of not only the combination of the cells, but one of the cells alone was over 900 on average so just compared the event names from column A itself.

    I really appreciate your help - just have one more question if possible, please...

    So now I have two spreadsheets which each have two columns (that count) listing event names in one and the # of times the event appeared in the other. Roughly 90% of the events on each sheet have the exact same name, but about 10% are not on the other. My final task is to create a list of all unique events between the two sheets which list the difference between each of their counts. Events which are on only one sheet would be 0 obviously.

    Would this be doable? Again, I really appreciate your assistance!! Thanks!

    UPDATE

    I think I'm good now as since posting, I did a couple of things manually so now have a single spreadsheet as follows with A containing a list of all unique event names, B the total of that event from sheet 1, C a total of that event from sheet 2, and D where I plan to put the comparison between the two. I think I'll just subtract one from the other then know which had the positive difference if the result is negative or not.
    Column A Column B Column C Column D
    Event Name A 100 150
    Event Name B 109 90
    ...

    Thanks again!
    Last edited by bzowk; 06-30-2019 at 02:43 PM.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

    Glad you found solution. Thank you for updating us.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 08-27-2018, 11:55 AM
  2. [SOLVED] Counting amount of times capital word appears in list of rows
    By mrsak87 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-29-2018, 04:15 AM
  3. [SOLVED] Need to know how many times a unique date appears more than three times for each location
    By justaguyintx in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-15-2015, 03:04 PM
  4. Replies: 5
    Last Post: 06-15-2015, 02:47 PM
  5. [SOLVED] Count unique names only, not the number of times it appears
    By amyp22x3 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-04-2013, 01:32 PM
  6. [SOLVED] Count unique names only, not the number of times it appears
    By amyp22x3 in forum Excel General
    Replies: 7
    Last Post: 04-04-2013, 11:37 AM
  7. [SOLVED] Counting number of times unique id appears
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-26-2012, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1