Formula to return the most commonly occurring (text) value based on criteria

Hello experts,

I am wondering if you can help me (again).

I am trying to create a formula to automate some values in a table. I have an idea in my head, but if you have any better ideas, please feel free to share - as I still learning.

I have 2 data sets -

1 - List of Sites
2 - List of Issues (Exceptions) recorded for each site.

Please see sample provided.

I would like to use the List of Sites to form a table whereby the most commonly occurring reason for each category automatically populates. After some research online it suggests using Index, Match and Mode functions - but I am not clever enough to create that on my own - so am hoping you guys may be able to help me out.

Many thanks in advance.

Hi,
please see attached, if I understood you correct then -

D4 and across:
="The most commonly occuring exception reason for issues pertaining to"&" "&D$3&"at site"&" "&""""&TRIM($C4)&""""&" (IF ANY)"

Thank you for the reply, but sadly it was not what I was looking for. My Output sheet was just to describe what I was looking for. I want the actual reasons from the exceptions tab to appear in the table and not just the text that I wrote. The text I wrote was just to explain what I wanted to go where. Thanks, though.

What result do you want if there are 2 or more criteria with equal counts?

And if needed, is a VBA solution OK?

Hi, John. Good question - both(or all) results if possible. I don't think it would be that common where two 'Reasons' share the same count but definitely a consideration.

VBA is not something I know much about, or anything about to be honest, so formula would definitely be the preferred option. But if VBA is the only way, that may have to do. Just a couple of questions around that if VBA is the solution -

1. Would I just need to run a macro to complete that table?
2. If so, I assume it wouldn't take too long to update?
3. Is there a way I can run the VBA, update the table, and then just disable so I can send this sheet out on a weekly basis without other users having to run it?

Many thanks for your input. Appreciated.

P.s. this is only a method I have thought up in my head. There may be a better way of doing it. I just want to so for each site, the most common reason for each determinant (Turbidity, Ammonia etc).

to answer your questions, is the sample a sample or is it a comparable size to the real file you use?

probably
1)yes
2) depends on its size, see above
3) Yes or you send it out as a sperate sheet/workbook where they can not change anything, this could be part of the VBA code

It's comparable. Usually 2.5-4k rows. Not much variation weekly. Never vastly more than that.

what about the attached pivot as a starting point

Chooses the top 1 option for each category

That pivot is exactly right, and was my first choice. I want to be able to show a simple bar chart with the site > determinants > most common reason. However, when I chart it, it's completely messy because of the multiple columns. So my second choice was to go for the table I asked for above.

Attachment 729570

I have tried reformatting and googling for advice but nothing I do makes the chart look how I want it too.

but you can base the table on the pivot. I am at a loss how you can bar chart text values!
https://www.contextures.com/excelloo...ndexmatch.html to populate your table

Fantastic!

Thank you both. This is a relief. I appreciate you both taking the time to help me out.

Quick question - the source data will change on a weekly basis, which will of course amend the pivot table, too. Is there a way I can force the formula to adjust it's range that it's looking up. (There will be a varying number of sites each week - between 40-60). Or will it do this automatically?

Thanks again.

Also, just out of curiosity if I can just ask - how did you manage to formulate the return of the highest number/most common reason? I can't work it out in your formula. I.e. where Turbidity has 5/6 reasons, what part of the forumla is making it return only the highest value?

Thanks again.

My formula simple takes the results of the Pivot Table and puts them in your output matrix.

As noted previously the Pivot table returns Category rather than Reason so it needs amending: not an area I am familiar with.

If the pivot table is in the same sheet, I assume you can make dynamic range(s)..

Yes that's fine I have already amended the pivot and everything is working fine. I just couldn't work out how it knows to take the largest count from the list - but either way it's doing it nicely.

In the filter for the exception field value filters top 10 (stupidly named), if you select it you can choose top 1Attachment 729598

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

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.