MOST FREQUENT RECURRING TEXT / PHRASE

Unlucky

Unlucky

Board Regular
Joined
Dec 3, 2014
Messages
58
Office Version
  1. 2016
Platform
  1. Windows
Most frequent recurring text. I was able to enter the most frequent formula, but I'm having difficulty with the 2nd, 3rd, 4th, and 5th most frequent.

I use column D for a program name ($D$:$D$121)
I use column E for the recurring text I want to search for ($E$2:$E$121)
I use column F for the program name pool ($F$2:$F$14)

Column G ($G$2:$G$14) list the most frequent recurring text {=INDEX($E$2:$E$121,MODE(IF($D$2:$D$121=$F$2,MATCH($E$2:$E$121,$E$2:$E$121,0))))} . . . confirmed with Ctrl+Shift+Enter

Column H ($H$2:$H$14) is the 2nd most frequent recurring text
Column I ($I$2:$I$14) is the 3rd most frequent recurring text
Column J ($J$2:$J$14) is the 4th most frequent recurring text
Column K ($K$2:$K$14) is the 5th most frequent recurring text

Problem: I'm having trouble with entering the formula for columns H, I, J, & K
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Try:

Book1
DEFGHIJK
2aA13aA15A17A18A14A11
3bA17bA13A15A17A19A12
4cA12cA15A16A19A17A12
5dA15dA10A15A13A12A17
6eA10eA10A16A13A15A12
7fA16fA18A13A16A14A11
8gA10gA17A10A13A16A12
9hA14hA19A10A12A16A11
10aA15
11bA19
12cA15
13dA10
14eA19
15fA14
16gA17
17hA12
18aA15
19bA13
20cA19
21dA10
22eA16
23fA18
24gA15
25hA12
26aA18
27bA12
28cA18
29dA13
30eA10
31fA11
32gA13
33hA19
34aA18
35bA15
36cA14
37dA17
38eA11
39fA14
40gA10
41hA13
42aA17
43bA11
44cA15
45dA15
46eA10
47fA18
48gA16
49hA16
50aA16
51bA18
52cA11
53dA12
54eA14
55fA18
56gA16
57hA19
58aA14
59bA10
60cA15
61dA10
62eA13
63fA13
64gA12
65hA16
66aA17
67bA14
68cA17
69dA10
70eA13
71fA16
72gA11
73hA17
74aA17
75bA13
76cA13
77dA15
78eA15
79fA10
80gA17
81hA11
82aA19
83bA15
84cA19
85dA13
86eA15
87fA18
88gA13
89hA10
90aA11
91bA15
92cA17
93dA13
94eA12
95fA13
96gA12
97hA10
98aA15
99bA17
100cA16
101dA18
102eA18
103fA13
104gA17
105hA11
106aA11
107bA13
108cA16
109dA12
110eA12
111fA13
112gA18
113hA19
114aA14
115bA16
116cA16
117dA10
118eA16
119fA11
120gA11
121hA10
Sheet15
Cell Formulas
RangeFormula
G2:K9G2=INDEX($E$2:$E$121,MODE(IF(($D$2:$D$121=$F2)*(COUNTIF($F2:F2,$E$2:$E$121)=0),MATCH($E$2:$E$121,$E$2:$E$121,0)*{1,1})))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
Eric W, You're Awesome! Totally Awesome! That solved my one problem for the day. Thanks again!
 
Upvote 0
So, . . . . . there are columns that don't have enough entries to sort the data out into five different categories (Top 5). I tried using a IFISERROR but it is not working. I also tried conditional formatting, to change the cell text to the same color as the cell, but it will not change it. Basically, I want to eliminate or hide the #N/A return. Any thoughts on how to make it disappear?

I did not merge the cells with the returns, because Excel would not let me do it; instead the text is not wrapped, and in a normal view, it looks like the data is in one cell (cosmetics). Here is the table.


FORMULA_DRAFT_2021-11-23.xlsx
BCDEFGHIJKLMNOPQ
29TOP FIVE ROOT CAUSES FOR CORRECTIVE ACTION REQUESTS ON EACH PROGRAM
30PROGRAMTOP ROOT CAUSE2nd MOST COMMON ROOT CAUSE3rd MOST COMMON ROOT CAUSE4th MOST COMMON ROOT CAUSE5th MOST COMMON ROOT CAUSE
31AAVQA - FIR Test Failure (Control Test)QA - QMS Audit Major FindQA - CorrosionQA - QMS Audit Minor FindENG - Material Review Board
32ACVENG - Configuration ManagementQA - Process InadequateQA - QMS Audit Major FindQA - CorrosionENG - Material Review Board
33AMPVQA - FIR Test Failure (Control Test)#N/A#N/A#N/A#N/A
34BFVQA - FIR Test Failure (Control Test)#N/A#N/A#N/A#N/A
35M88QA - Form, Fit, FunctionQA - FIR Test Failure (Control Test)PA - Plant ClearancePA - GFM AccountabilityQA - Corrosion
36PIM-CATQA - CorrosionQA - Procedure Not FollowCO - Payment DiscrepanciesQA - Cyclic Maintenance#N/A
37PIM-SPHQA - CorrosionQA - Form, Fit, Function#N/A#N/A#N/A
38PFCSQA - Form, Fit, FunctionQA - Loose or Missing#N/A#N/A#N/A
39M109A5QA - Calibration#N/A#N/A#N/A#N/A
40M109A6QA - FIR Test Failure (Control Test)#N/A#N/A#N/A#N/A
41Con Bus SysQA - FIR Test Failure (Control Test)#N/A#N/A#N/A#N/A
42FacilityQA - FIR Test Failure (Control Test)QA - Weld#N/A#N/A#N/A
43QMSQA - QMS Audit Minor Find#N/A#N/A#N/A#N/A
DASHBOARD
Cell Formulas
RangeFormula
B31:B43B31='DROP DOWNS'!F6
C31:C43C31=INDEX('RAW CAR DATA'!$BV$8:$BV$1507,MODE(IF(('RAW CAR DATA'!$AR$8:$AR$1507=$B31)*(COUNTIF($B31:$B31,'RAW CAR DATA'!$BV$8:$BV$1507)=0),MATCH('RAW CAR DATA'!$BV$8:$BV$1507,'RAW CAR DATA'!$BV$8:$BV$1507,0)*{1,1})))
F31:F43F31=INDEX('RAW CAR DATA'!$BV$8:$BV$1507,MODE(IF(('RAW CAR DATA'!$AR$8:$AR$1507=$B31)*(COUNTIF($B31:$C31,'RAW CAR DATA'!$BV$8:$BV$1507)=0),MATCH('RAW CAR DATA'!$BV$8:$BV$1507,'RAW CAR DATA'!$BV$8:$BV$1507,0)*{1,1})))
I31:I43I31=INDEX('RAW CAR DATA'!$BV$8:$BV$1507,MODE(IF(('RAW CAR DATA'!$AR$8:$AR$1507=$B31)*(COUNTIF($B31:$F31,'RAW CAR DATA'!$BV$8:$BV$1507)=0),MATCH('RAW CAR DATA'!$BV$8:$BV$1507,'RAW CAR DATA'!$BV$8:$BV$1507,0)*{1,1})))
L31:L43L31=INDEX('RAW CAR DATA'!$BV$8:$BV$1507,MODE(IF(('RAW CAR DATA'!$AR$8:$AR$1507=$B31)*(COUNTIF($B31:$I31,'RAW CAR DATA'!$BV$8:$BV$1507)=0),MATCH('RAW CAR DATA'!$BV$8:$BV$1507,'RAW CAR DATA'!$BV$8:$BV$1507,0)*{1,1})))
O31:O43O31=INDEX('RAW CAR DATA'!$BV$8:$BV$1507,MODE(IF(('RAW CAR DATA'!$AR$8:$AR$1507=$B31)*(COUNTIF($B31:$L31,'RAW CAR DATA'!$BV$8:$BV$1507)=0),MATCH('RAW CAR DATA'!$BV$8:$BV$1507,'RAW CAR DATA'!$BV$8:$BV$1507,0)*{1,1})))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C31:Q43Cell Value=#N/AtextNO
 
Upvote 0
Try wrapping your formulas with IFERROR. Here's the C31 formula:

Excel Formula: 
=IFERROR(INDEX('RAW CAR DATA'!$BV$8:$BV$1507,MODE(IF(('RAW CAR DATA'!$AR$8:$AR$1507=$B31)*(COUNTIF($B31:$B31,'RAW CAR DATA'!$BV$8:$BV$1507)=0),MATCH('RAW CAR DATA'!$BV$8:$BV$1507,'RAW CAR DATA'!$BV$8:$BV$1507,0)*{1,1}))),"")
 
Upvote 0
Wow! That's amazing! Thanks again! Initially, it did not work, until I used the Ctrl+Shift+Enter.
 
Upvote 0
In a variation to the above formula, instead of finding the most common recurring text and reporting the results horizontally (ROW . . . Most common, 2nd most common, 3rd . . . ), how would I report the data descending vertically (COLUMN . . . Most common, 2nd most common, 3rd . . . )? What part of the formula needs to be changed?

There is only one criteria (i.e.: OPEN . . . CELL $B42).

{=IFERROR(INDEX('RAW CAR DATA'!$E$8:$E$1507,MODE(IF(('RAW CAR DATA'!$AN$8:$AN$1507=$B42)*(COUNTIF($B42:$C42,'RAW CAR DATA'!$E$8:$E$1507)=0),MATCH('RAW CAR DATA'!$E$8:$E$1507,'RAW CAR DATA'!$E$8:$E$1507,0)*{1,1}))),"")}
 
Upvote 0
Here is the sample / example table. I also need help with filling in the rest of the blocks based on the associated document number.


TEST LOG FOR FORMULAS.xlsx
ABCDEFGHIJKLMNOP
1OPENDOCUMENTS
2NUMBERLEVELISSUEDCAP DUEPROGRAMISSUED BYSTATUSCOMMENTS
32938120200008B
42938120200008B
52938120200008B
62938120200008B
72938120200008B
82938120200008B
92938120200008B
102938120200008B
112938120200008B
122938120200008B
132938120200008B
142938120200008B
152938120200008B
162938120200008B
172938120200008B
182938120200008B
192938120200008B
202938120200008B
212938120200008B
222938120200008B
23
24SOURCE DATA TABLE
25NUMBERLEVELISSUEDCAP DUEPROGRAMISSUED BYSTATUSCOMMENTS
262938120200005AI10-Sep-2020-Sep-20ABCTomCLOSEDLoose hardware
272938120200006AI12-Sep-2022-Sep-20ABCCharlieCLOSEDInstalled backwards
282938120200007AI12-Sep-2022-Sep-20CDRWilliamCLOSEDLoose hardware
292938120200008AI21-Sep-201-Oct-20QMFWilliamCLOSEDMissing hardware
302938120200008BII22-Sep-202-Oct-20ABCBobOPENInstalled upside down
312938120200008CIII22-Sep-202-Oct-20CRFTomOPENMissing paint
322938120200009AI23-Sep-203-Oct-20CDRGregOPENLoose hardware
332938120200010AI4-Oct-2014-Oct-20QMFTomCLOSEDLoose hardware
342938120200011AI5-Oct-2015-Oct-20WWWWilliamCLOSEDInstalled upside down
352938120200011BII12-Oct-2022-Oct-20WTFCharlieCLOSEDInstalled backwards
362938120200012AI14-Oct-2024-Oct-20CDRBobOPENMissing paint
372938120200013AI3-Nov-2013-Nov-20ABCBobOPENOperator error
382938120200014BII6-Nov-2016-Nov-20ABCBobOPENFailed test
392938120200015AI6-Nov-2016-Nov-20WTFTomOPENLoose hardware
402938120200016AI7-Nov-2017-Nov-20WWWWilliamCLOSEDMissing paint
412938120200016CIII28-Nov-208-Dec-20ILSCharlieOPENInstalled backwards
422938120200017AI29-Nov-209-Dec-20PRTCharlieCLOSEDLoose hardware
432938120200018AI8-Dec-2018-Dec-20ABCBobOPENLoose hardware
442938120200019AI10-Dec-2020-Dec-20CDRGregCLOSEDInstalled backwards
452938120200020AI15-Dec-2025-Dec-20CRFTomCLOSEDLoose hardware
462938120200021AI22-Dec-201-Jan-21CRFWilliamCLOSEDMissing hardware
472938120200022BII30-Dec-209-Jan-21CRFBobCLOSEDInstalled upside down
482938120200023AI30-Dec-209-Jan-21WWWBobCLOSEDMissing paint
492938120200023BII31-Dec-2010-Jan-21ABCWilliamOPENLoose hardware
502938120200024AI7-Jan-2117-Jan-21ILFGregOPENLoose hardware
512938120200025AI12-Jan-2122-Jan-21WTFTomOPENInstalled upside down
522938120200026AI13-Jan-2123-Jan-21ABCTomOPENInstalled backwards
532938120200027AI21-Jan-2131-Jan-21ABCTomOPENMissing paint
542938120200027CIII3-Feb-2113-Feb-21ABCTomOPENMissing hardware
552938120200028AI4-Feb-2114-Feb-21WTFGregOPENInstalled upside down
562938120200029AI13-Feb-2123-Feb-21WWWWilliamOPENMissing paint
572938120200030BII8-Mar-2118-Mar-21ILSBobOPENLoose hardware
582938120200030CIII9-Mar-2119-Mar-21PCFTomOPENLoose hardware
FORMULA TEST
Cell Formulas
RangeFormula
A3:A22A3=IFERROR(INDEX($A$26:$A$58,MODE(IF(($K$26:$K$58=$G$1)*(COUNTIF($G$1,$A$26:$A$58)=0),MATCH($A$26:$A$58,$A$26:$A$58,0)*{1,1}))),"")
E26:E58E26=D26+10
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I am not sure what output you are looking for. Could you manually fill in the results section with the expected results?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Multiple conditions
Replies
8
Views
196
awoohaw
awoohaw
A
  • Question
Macro help
Replies
9
Views
314
Peter_SSs
Peter_SSs
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
120
JMC57
J
K
  • Solved
Formula for third most frequent text with multiple criteria
Replies
3
Views
916
Fluff
Fluff
E
  • Question
Customized text wrapping to new row (not within cell)
Replies
8
Views
203
rlv01
R

Forum statistics

Threads
1,215,064
Messages
6,122,942
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top