+ Reply to Thread
Results 1 to 23 of 23

top occurance in data table

  1. #1
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    top occurance in data table

    I need help to write a formula that will return the top occurance of data combination found in 2 columns

    col 1 col 2
    1 1
    2 3
    1 2
    1 1
    3 2
    2 2
    1 1
    Last edited by eberns65; 12-12-2018 at 01:39 PM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula assitance

    Could you post a sample sheet with your data and expected results and if necessary how those results are to be arrived at?
    BUT, most importantly, please change your post title to something more appropriate, think in terms of using a search function, what would you put into the search to find a similar problem?
    Formula assistance is almost what everyone who visits this forum wants so it isn't very descriptive of your problem.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: Formula assitance

    Thanks for the response. I tried to attach an excel spreadsheet sample but the site won't give me the option.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Formula assitance

    Try this...

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    HTH
    Regards, Jeff

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula assitance

    under the quick reply window, instead of using post quick reply, hit go advanced, then mid way down is manage attachments, click that then use the browse window to find your sample and upload it.
    Well, thank you for the title change! Though I must say I'm not sure I am clear about what your problem is.
    I'm hoping an attachment clears it up.
    Last edited by Sam Capricci; 12-12-2018 at 01:44 PM.

  6. #6
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    I attached a sample file
    Attached Files Attached Files

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: top occurance in data table

    thank you for the attachment. Are you looking for the highest two cells in adjacent rows sum? In other words, row 19 has 6 in cell A19 and 13 in cell B19 so the two add to 19 which is the highest total in those two columns.

  8. #8
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    No. The data should not be read as numbers to add together. In my example I believe the highest occurance is 1-1

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: top occurance in data table

    I'm trying to understand but I'm still not clear on what you are trying to do and what your answer is and how to arrive at it.
    in row 2 you have 1 in A2 and 1 in B2. They are also in A10/B10 and again in A16/B16. Is the answer 1 - 1 because there are three of those out of the 32?
    or is it because there are 13 ones between col A and col B?

  10. #10
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    Thanks for your patience here. The answer above is the first. Because there are 3 occurances of 1-1. Think of column 1 as a region and column 2 as a location. So for region 1 there might be 13 locations. Same with region 2-13. For each region there could be up to 13 locations. So both columns have to be read together and then which appears the greatest number of times.

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: top occurance in data table

    How about using some helper columns...

    In column C >> =A2&B2 >> copied down

    In column D >> =COUNTIF($C$2:$C$32,C2) >> copied down

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: top occurance in data table

    Offhand I don't know a single formula to do this so I did what I think you want in three steps.
    step 1 - I used a concatenation formula in column C to get the combinations
    step 2 - I copied the results from col C and pasted special >> values into col D
    step 3 - used a countif formula of the values in col D for their frequency in col C and then sorted columns D and E by col E descending.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    Yes. I was thinking this as well but how to I get cell to report 1-1 is recurring most often? At the top of my spreadsheet I want to see the answer 1-1 or whatever is the most recurring.

  14. #14
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    Thanks but this is a document that will be updated and added to on a regular basis. I can't cut and paste each time I add new data. The #1 occurance should be recalculated with each additional entry on my spreadsheet

  15. #15
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: top occurance in data table

    Hi,

    =LOOKUP(1,0/FREQUENCY(0,1/COUNTIFS(A2:A32,A2:A32,B2:B32,B2:B32)),A2:A32&"|"&B2:B32)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  16. #16
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    XOR LX Thank you so much for the help. This worked!!!!!!!!!!!!!!!!!!!!!

  17. #17
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: top occurance in data table

    No worries! Glad to help.

    Cheers

  18. #18
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    Hate to say this but the above formula didn't work. I thought the result returned was correct and then I tested it and the results didn't change. I attached the actual spreadsheet I will use it in (removed some private data). The formula answer is in merged cell FG2. Starting on row 428 I tested the formula by entering 10 and 1 multiple times. So the answer above should change to 10-1. If you keep adding more row of 10 1 the answers changes by 1. The data in column F and G can be read as text if that helps
    Attached Files Attached Files

  19. #19
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: top occurance in data table

    Because your original file didn't contain any blank cells within the range being queried; this one does.

    Simple correction:

    =LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIFS(F6:F999,F6:F999,G6:G999,G6:G999))),F6:F999&"-"&G6:G999)

    Cheers

  20. #20
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    That was it. Now... what if I wanted to also see what the actual number of times that made that query #1 (i.e. how many times it appears?)
    Thanks again for your quick response and all your help.

  21. #21
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: top occurance in data table

    Sure.

    =SUMPRODUCT(0+(F6:F999&"-"&G6:G999=F2))

    Cheers

  22. #22
    Registered User
    Join Date
    12-12-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    36

    Re: top occurance in data table

    Curious... what is the logic follwed in Excel when there is tie in the number of occurances? The top occurance happens 8 times. I entered 8 occurances of another number combination but it didn't change the answer until I had 9 entries.
    How does Excel chose which asnwer to return on a tie?

  23. #23
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: top occurance in data table

    It depends on the formula you use. If you state your preference, I can amend the formula if necessary.

    Regards

+ 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. [SOLVED] assitance to actual vba code not to show formula in the cells
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-29-2018, 02:38 PM
  2. Assitance on AverageIf Formula
    By sfoxbox in forum Excel General
    Replies: 1
    Last Post: 06-17-2014, 11:13 AM
  3. [SOLVED] Pivot Table Assistance Needed
    By mistye525 in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 07-29-2013, 02:05 PM
  4. Drop down Box Assitance
    By philjthommo in forum Excel General
    Replies: 4
    Last Post: 01-15-2013, 01:33 AM
  5. Assitance appreciate with project
    By yashica in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-19-2012, 03:23 PM
  6. Data Associat Assitance
    By adeelkhan007 in forum Excel General
    Replies: 3
    Last Post: 05-11-2011, 03:48 AM
  7. Assitance with excel formula and logic
    By lee2k60 in forum Excel General
    Replies: 1
    Last Post: 03-31-2011, 05:31 AM

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