Reference the most frequent of different conditions with indexing

[karl6333]

Good day!

I need to know how to reference the most frequent of different conditions with indexing. It's kinda hard to explain in words but I attached the excel file sample for you to understand.

So from the database worksheet, it's like raw data that I want to make an output of.

Column 1 contains some text that I want to get which is the most frequent for every sub column (column 2 and column 3)

Column 2 is a grouping for every table in the Output worksheet.

Column 3 are sub rows in the Output worksheet.

I would like to make reference functions to automate this from database to output but I find it hard. Please help, thank you.

Untitled-1.jpg

Untitled-2.jpg

[AliGW]

I am sorry - I do not understand how you get the output data from the database. Where are Annie and Derick in the database?

[samba_ravi]

as per my understanding A means Annie and D Means Derick, am I right?

[karl6333]

I am sorry I didn't include that, but nflsales is right. A=Annie and D=Derick

[JeteMc]

If I understand correctly the following array entered formulas* will yield the expect results in column B of the Output sheet.
In B3:B6:

Please Login or Register  to view this content.

In B10:B12:

Please Login or Register  to view this content.

In B17:B20:

Please Login or Register  to view this content.

*1) Select cell B3,
2) Paste the formula into the formula bar,
3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Copy down to B6.
Repeat the same steps for the second and third formulas ( for cells B10 and B17)
Let us know if you have any questions.