Second & Third most frequent text with criteria

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hello TristanPKT. Welcome to the forum and Happy Independence Day!!!

Try array entering this formula in E3. Fill 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:

=IFERROR(INDEX($B$2:$B$25,MODE.MULT(IF(($D3=$A$2:$A$25)*

ISNA(MATCH($B$2:$B$25,$D3:D3,0)),MATCH($B$2:$B$25,$B$2:$B$25,0)+{0,0}))),"")

	D	E	F	G
1
2	ID	#1 State	#2 State	#3 State
3	A1	KY	OH	IN
4	A2	FL	GA	NC
5	A3	NY	MA	PA
6	A4	CA	WA	OR
7

This worked!! Thanks so much!

Originally Posted by TristanPKT

This worked!! Thanks so much!

We have asked you to mark this SOLVED but you seem to have abandoned us altogether. So I'm marking it SOLVED and closing it.

You are welcome. Thank you for the feedback and added rep.

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

Originally Posted by dy24

I get 1st most common repeatedly with this formula. Can anyone tell me why?

To answer this we need to see your data and exactly what formula you are using.

Since this question was answered over 5 years ago, and your question is about your file, you need to start a new thread. Attach a file with sample data showing your formula, and also indicate what result you were expecting to see and why that should be the result.



Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread