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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
K
  • Question Question
conditional formatting question
Replies
2
Views
603
kerm007
K
M
  • Question Question
Hit a wall with aggregate function
Replies
0
Views
117
MZING81
M
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
319
etaf
etaf
S
  • Solved
Table manipulations using dynamic arrays
2
Replies
10
Views
154
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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