MOST COMMON TEXT

Unlucky

Unlucky

Board Regular
Joined
Dec 3, 2014
Messages
58
Office Version
  1. 2016
Platform
  1. Windows
I was able to flesh out the most common occurrences, but ran into difficulty when I wanted to add another criteria. Basically, I am having trouble with the formula for adding criteria to the most common, 2nd most common, etc. Here are my source tables and sorting tables. The additional criterial is finding the most common, 2nd most common, etc. defect.

TEST LOG FOR FORMULAS.xlsx
ABCDEFGHIJKLMN
1RAW DATA FROM WORKLOAD TRACKER
2CYFYWEEKDATE1st SHIFT2nd SHIFTPROGRAMVARIANTSERIALCATEGORYJOB COMPLETEDABBREVIATED CATEGORYACCEPT / REJECT / WAIVEHOURS
32022202222-Jan-221ABCABC_688688812ABC_ADMINProcess ReviewADMINREJECTED2.00
42022202224-Jan-221CBRAMGAMG0022CBR_GARAGETop Water RechecksGARAGEREJECTED1.00
52022202224-Jan-221QRSTQRST_M22QRST0208AQRST_ADMINFORM 2408ADMINACCEPTED0.50
62022202224-Jan-221XYZXYZ_HEPH1016XYZ_GARAGEDrive RechecksGARAGEACCEPTED1.00
72022202224-Jan-221QRSTQRST_M22QRST0179AQRST_ADMINFORM 2408ADMINACCEPTED0.50
82022202224-Jan-221PIPPIPPIP181PIP_GARAGEDriveGARAGEREJECTED3.00
92022202224-Jan-221ABCABC_688688805ABC_GARAGEGarage ATIGARAGEREJECTED1.00
102022202224-Jan-221PIPPIPPIP184PIP_WELDSRP/NSRPWELDACCEPTED0.50
112022202224-Jan-221ABCABC_688688815ABC_ADMINNCRADMINREJECTED0.50
122022202224-Jan-221CBRABC_688688816CBR_ADMINNCRADMINREJECTED0.50
132022202224-Jan-221CBRABC_688688817CBR_ADMINNCRADMINREJECTED0.50
142022202224-Jan-221PIPPIPPIP194PIP_WELDTurnoverWELDACCEPTED0.50
152022202224-Jan-221CBRAMCAMC0030CBR_CSINATOCSIACCEPTED0.50
162022202224-Jan-221CBRAMMAMM0026CBR_CSISteeringCSIACCEPTED0.50
172022202224-Jan-221CBRAMCAMC0041CBR_WELDFinalWELDACCEPTED1.00
182022202224-Jan-221QRSTQRST_M22QRST0222ACBR_TOP-WATERTop waterTOP WATERACCEPTED0.50
192022202224-Jan-221PIPPIPPIP181PIP_GARAGEControl testsGARAGEREJECTED1.00
202022202224-Jan-221ABCABC_688688805ABC_GARAGEGarage ATI RechecksGARAGEACCEPTED0.50
212022202224-Jan-221PIPPIPPIP181PIP_GARAGEControl testsGARAGEACCEPTED0.50
222022202225-Jan-221PIPPIPPIP188PIP_CSISteeringCSIACCEPTED0.50
232022202225-Jan-221PIPPIP_ADMINRFVADMINACCEPTED0.50
242022202225-Jan-221PIPPIP_ADMINRFVADMINACCEPTED0.50
252022202225-Jan-221CBRAMGAMG0022CBR_GARAGETop Water RechecksGARAGEACCEPTED1.00
262022202225-Jan-221CBRAMGAMG0022CBR_GARAGEDriveGARAGEREJECTED2.50
272022202225-Jan-221QRSTQRST_M22QRST0222AQRST_PAINTPaintPAINTREJECTED3.00
282022202225-Jan-221QRSTQRST_M22QRST0221AQRST_PAINTPaintPAINTACCEPTED0.50
292022202225-Jan-221QRSTQRST_M22QRST0240AQRST_GARAGERoll OffGARAGEACCEPTED0.50
302022202225-Jan-221QRSTQRST_M22QRST0241AQRST_GARAGERoll OffGARAGEACCEPTED0.50
312022202225-Jan-221ABCABC_688688823ABC_WELDFinalWELDACCEPTED1.00
322022202225-Jan-221CBRAMMAMM0027CBR_CSISteeringCSIACCEPTED0.50
332022202226-Jan-221ABCABC_688688806ABC_GARAGEGarage ATIGARAGEREJECTED2.00
342022202226-Jan-221CBRAMMAMM0027CBR_CSISteeringCSIACCEPTED0.50
352022202226-Jan-221CBRAMGAMG0022CBR_GARAGEDrive RechecksGARAGEACCEPTED0.50
362022202226-Jan-221QRSTQRST_M22QRST0222AQRST_GARAGEDrive RechecksGARAGEACCEPTED0.50
372022202226-Jan-221QRSTQRST_M22QRST0225ACBR_TOP-WATERTop waterTOP WATERACCEPTED0.50
382022202227-Jan-221PIPPIPPIP194PIP_WELDTurnoverWELDACCEPTED0.50
392022202227-Jan-221CBRAMCAMC0030CBR_CSINATOCSIACCEPTED0.50
402022202227-Jan-221CBRAMMAMM0026CBR_CSISteeringCSIACCEPTED0.50
412022202228-Jan-221CBRAMCAMC0041CBR_WELDFinalWELDACCEPTED0.50
422022202228-Jan-221QRSTQRST_M22QRST0222AQRST_GARAGEDrive RechecksGARAGEACCEPTED0.50
432022202228-Jan-221QRSTQRST_M22QRST0225ACBR_TOP-WATERTop waterTOP WATERACCEPTED0.50
442022202239-Jan-221CBRAMGAMG0022CBR_GARAGEDrive RechecksGARAGEACCEPTED0.50
452022202239-Jan-221ABCABC_688688823ABC_WELDFinalWELDREJECTED0.50
462022202239-Jan-221ABCABC_688688823ABC_WELDFinalWELDREJECTED0.50
472022202239-Jan-221ABCABC_688688823ABC_WELDFinalWELDREJECTED0.50
TRACKER
Cell Formulas
RangeFormula
A3:A47A3=YEAR($D3)
C3:C47C3=WEEKNUM($D3)


TEST LOG FOR FORMULAS.xlsx
PQRSTUVWXYZAAAB
1TOP 5 AUDIT AREA CATEGORIES BY PROGRAMTOP 5 DEFECT AREA CATEGORIES BY PROGRAM
2TOPSECONDTHIRDFOURTHFIFTHREJECTEDTOPSECONDTHIRDFOURTHFIFTH
3ABCWELDGARAGEADMIN  ABCWELDGARAGEADMIN  
4CBRCSIGARAGEADMINWELD CBRCSIGARAGEADMINWELD 
5QRSTGARAGETOP WATERADMINPAINT QRSTGARAGETOP WATERADMINPAINT 
6XYZGARAGE    XYZGARAGE    
7PIPGARAGEWELDADMIN  PIPGARAGEWELDADMIN  
8FACILITYGARAGEADMINGARAGEADMINGARAGEFACILITYGARAGEADMINGARAGEADMINGARAGE
9
10TOP 10 AREA CATEGORIES BY FACILITYTOP 10 DEFECT CATEGORIES BY FACILITY
11CATQTYRANKORDEREDCATORDEREDCATQTYRANKORDEREDCATORDERED
12ADMIN831CONTROL16ADMIN421GARAGE6
13CONTROL072WELD9CONTROL052ADMIN4
14CSI743ADMIN8CSI053WELD3
15GARAGE1614CSI7GARAGE614PAINT1
16PACK075TOP WATER3PACK055CONTROL0
17PAINT266PAINT2PAINT146CONTROL0
18SWIM077CONTROL0SWIM057CONTROL0
19TOP WATER358CONTROL0TOP WATER058CONTROL0
20TURRET079CONTROL0TURRET059CONTROL0
21WELD9210CONTROL0WELD3310CONTROL0
TRACKER
Cell Formulas
RangeFormula
Q3:Q7Q3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$P3)*(COUNTIF($P3:$P3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
R3:R7R3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$P3:$Q3)*(COUNTIF($P3:$Q3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
S3:S7S3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$P3:$R3)*(COUNTIF($P3:$R3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
T3:T7T3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$P3:$S3)*(COUNTIF($P3:$S3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
U3:U7U3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$P3:$T3)*(COUNTIF($P3:$T3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
Q8,X8Q8=INDEX($L$3:$L$47,2)
R8,Y8R8=INDEX($L$3:$L$47,3)
S8,Z8S8=INDEX($L$3:$L$47,MODE(MATCH($L$3:$L$47,$L$3:$L$47,4)))
T8,AA8T8=INDEX($L$3:$L$47,5)
U8,AB8U8=INDEX($L$3:$L$47,6)
X3:X7X3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$W3)*(COUNTIF($W3:$W3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
Y3:Y7Y3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$W3:$X3)*(COUNTIF($W3:$X3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
Z3:Z7Z3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$W3:$Y3)*(COUNTIF($W3:$Y3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
AA3:AA7AA3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$W3:$Z3)*(COUNTIF($W3:$Z3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
AB3:AB7AB3=IFERROR(INDEX($L$3:$L$47,MODE(IF(($G$3:$G$47=$W3:$AA3)*(COUNTIF($W3:$AA3,$L$3:$L$47)=0),MATCH($L$3:$L$47,$L$3:$L$47,0)*{1,1}))),"")
Q12:Q21Q12=SUM(IF($L$3:$L$47=$P12,1,0))
R12:R21R12=RANK($Q12,$Q$12:$Q$21)
T12T12=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$21))),1))
U12U12=MAX($Q$12:$Q$21)
T13T13=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$13))),1))
U13U13=LARGE($Q$12:$Q$21,2)
T14T14=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$14))),1))
U14U14=LARGE($Q$12:$Q$21,3)
T15T15=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$15))),1))
U15U15=LARGE($Q$12:$Q$21,4)
T16T16=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$16))),1))
U16U16=LARGE($Q$12:$Q$21,5)
T17T17=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$17))),1))
U17U17=LARGE($Q$12:$Q$21,6)
T18T18=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$18))),1))
U18U18=LARGE($Q$12:$Q$21,7)
T19T19=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$19))),1))
U19U19=LARGE($Q$12:$Q$21,8)
T20T20=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$20))),1))
U20U20=LARGE($Q$12:$Q$21,9)
T21T21=INDEX($P$12:$P$21,AGGREGATE(15,6,(ROW($P$12:$P$21)-ROW($P$12)+1)/($Q$12:$Q$21=LARGE($Q$12:$Q$21,ROWS(U12:U$21))),1))
U21U21=MIN($Q$12:$Q$21)
X12:X21X12=COUNTIFS($L$3:$L$47,$W12,$M$3:$M$47,$W$2)
Y12:Y21Y12=RANK($X12,$X$12:$X$21)
AA12:AA21AA12=INDEX($W$12:$W$21,AGGREGATE(15,6,(ROW($W$12:$W$21)-ROW($W$12)+1)/($X$12:$X$21=LARGE($X$12:$X$21,ROWS(AB$12:AB12))),1))
AB12AB12=MAX($X$12:$X$21)
AB13AB13=LARGE($X$12:$X$21,2)
AB14AB14=LARGE($X$12:$X$21,3)
AB15AB15=LARGE($X$12:$X$21,4)
AB16AB16=LARGE($X$12:$X$21,5)
AB17AB17=LARGE($X$12:$X$21,6)
AB18AB18=LARGE($X$12:$X$21,7)
AB19AB19=LARGE($X$12:$X$21,8)
AB20AB20=LARGE($X$12:$X$21,9)
AB21AB21=MIN($X$12:$X$21)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
jkpieterse said:
Wouldn't this be easier by using some pivot tables?
Click to expand...
The data is added hourly, . . . the current source table has over 7200 entries so far this calendar year . . . so the pivot table would have to be refreshed many times over the day to have real time data. Not very practical.
 
Upvote 0
for the table with the heading "TOP 5 AUDIT AREA CATEGORIES BY PROGRAM" and "TOP 10 AREA CATEGORIES BY FACILITY" the formulas are good. The table to the left of them (TOP 5 DEFECT AREA CATEGORIES BY PROGRAM) is where I have trouble getting it to count the number of associated 'REJECTED' entries in column M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
213
kerm007
K
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
197
Tetra201
Tetra201
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
191
gtd526
gtd526
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
172
gtd526
gtd526
gtd526
  • Question
Create List of Cell Values with Criteria
Replies
1
Views
252
gtd526
gtd526

Forum statistics

Threads
1,215,863
Messages
6,127,391
Members
449,382
Latest member
DonnaRisso

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