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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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 Question
conditional formatting question
Replies
2
Views
585
kerm007
K
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
S
  • Solved
Table manipulations using dynamic arrays
2
Replies
10
Views
120
Fluff
Fluff
P
  • Question Question
Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically
Replies
6
Views
308
ExcelToDAX
E
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
351
Tetra201
Tetra201

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

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