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
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.
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
Thanks for the response. I tried to attach an excel spreadsheet sample but the site won't give me the option.
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
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.
I attached a sample file
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.
No. The data should not be read as numbers to add together. In my example I believe the highest occurance is 1-1
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?
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.
How about using some helper columns...
In column C >> =A2&B2 >> copied down
In column D >> =COUNTIF($C$2:$C$32,C2) >> copied down
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.
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.
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
Hi,
=LOOKUP(1,0/FREQUENCY(0,1/COUNTIFS(A2:A32,A2:A32,B2:B32,B2:B32)),A2:A32&"|"&B2:B32)
Regards
XOR LX Thank you so much for the help. This worked!!!!!!!!!!!!!!!!!!!!!
No worries! Glad to help.
Cheers
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
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
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.
Sure.
=SUMPRODUCT(0+(F6:F999&"-"&G6:G999=F2))
Cheers
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?
It depends on the formula you use. If you state your preference, I can amend the formula if necessary.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks