2nd, 3rd, ... nth most common text within a date range

[leefletcher]

I have searched all over for a solution to this and I am beyond flummoxed. Don't let me down...

My data is in two columns (Diagnosis and Date) and I need to know the 1st - 3rd most common Diagnoses within a particular date range.

Diagnosis Date
Autism 1/31/18
Down's Syn 12/5/17
Asthma 12/12/17
ID 1/4/18
Autism 1/2/18
Autism 12/5/17
Asthma 12/2/17
Down's Syn 1/17/18
Autism 1/8/18
Autism 1/3/18
Down's Syn 12/15/17
Asthma 12/2/17
ID 1/6/18
Autism 1/21/18
Autism 12/15/17
Asthma 12/12/17
Down's Syn 1/1/18
Autism 1/8/18

Most common diagnosis in January?
2nd most common diagnosis in January?
3rd ....?

 

[Eric W]

Welcome to the Board!

Maybe:

	A	B	C	D	E
1	Diagnosis	Date		Date	1/1/2018
2	Autism	1/31/2018		Most common
3	Down's Syn	12/5/2017		Autism	6
4	Asthma	12/12/2017		Down's Syn	2
5	ID	1/4/2018		ID	2
6	Autism	1/2/2018
7	Autism	12/5/2017
8	Asthma	12/2/2017
9	Down's Syn	1/17/2018
10	Autism	1/8/2018
11	Autism	1/3/2018
12	Down's Syn	12/15/2017
13	Asthma	12/2/2017
14	ID	1/6/2018
15	Autism	1/21/2018
16	Autism	12/15/2017
17	Asthma	12/12/2017
18	Down's Syn	1/1/2018
19	Autism	1/8/2018

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E3 =IF(D3="","",COUNTIFS($A$2:$A$19,D3,$B$2:$B$19,">="&$E$1,$B$2:$B$19,"<="&EOMONTH($E$1,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D3 {=IFERROR(INDEX($A$2:$A$19,MOD(LARGE(IF(COUNTIF($D$2:$D2,$A$2:$A$19)=0,IF($B$2:$B$19>=$E$1,IF($B$2:$B$19<=EOMONTH($E$1,0),COUNTIFS($A$2:$A$19,$A$2:$A$19,$B$2:$B$19,">="&$E$1,$B$2:$B$19,"<="&EOMONTH($E$1,0))+(ROW($A$2:$A$19)-ROW($A$2)+1)/1000))),1),1)*1000),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the 1st of the month in E1. Put the formulas in (D3 is an array formula), and copy down the columns. Note that this uses 1 date in E1, and the formula includes EOMONTH (the End of Month function) to figure out the end date. You can put an end date in F1 if you want and replace the EOMONTH function with $F$1 if you want.

Let us know if this works for you.

 

[leefletcher]

Thank you for getting back to me! I'm really grateful for your help.

As you can probably guess, my table isn't as simple (it's an export someone else is doing). To help out, I've named some ranges.

Diag = $A$2:$A$19 (T2:T2000 in my spreadsheet)
RefDate = $B$2:$B$19 (Y2:Y2000 in my spreadsheet)

Also,
W2010 is the first of the month
I upped the row count to 10000 because I have over 1000 rows

=INDEX(Diag,MOD(LARGE(IF(COUNTIF($Y$2:$Y$2,Diag)=0,IF(RefDate>=W2010,IF(RefDate<=EOMONTH(W2010,0),COUNTIFS(Diag,Diag,RefDate,">="&W2010,RefDate,"<="&EOMONTH(W2010,0))+(ROW(Diag)-ROW($A$2)+1)/10000))),1),1)*10000)

I took off the IFERROR because it was coming back blank.

What did I mess up?

Thank you!!

 

[Eric W]

Where are you putting the results? The first COUNTIF is used to make sure we don't show duplicates, and the first range in it should point to the cell right above the cell the formula is in. Also note how the $ signs are used. Also, you need the $ on the W2010 references. So it should look something like this:

	T	U	V	W	X	Y	Z	AA	AB
1	Diagnosis					Date
2	Autism					1/31/2018		Most common
3	Down's Syn					12/5/2017		Autism	6
4	Asthma					12/12/2017		Down's Syn	2
5	ID					1/4/2018		ID	2
6	Autism					1/2/2018
7	Autism					12/5/2017
8	Asthma					12/2/2017
9	Down's Syn					1/17/2018
10	Autism					1/8/2018
11	Autism					1/3/2018
12	Down's Syn					12/15/2017
13	Asthma					12/2/2017
14	ID					1/6/2018
15	Autism					1/21/2018
16	Autism					12/15/2017
17	Asthma					12/12/2017
18	Down's Syn					1/1/2018
19	Autism					1/8/2018
2010			Date	1/1/2018

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula AB3 =IF(AA3="","",COUNTIFS(Diag,AA3,RefDate,">="&$W$2010,RefDate,"<="&EOMONTH($W$2010,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula AA3 {=IFERROR(INDEX(Diag,MOD(LARGE(IF(COUNTIF($AA$2:$AA2,Diag)=0,IF(RefDate>=$W$2010,IF(RefDate<=EOMONTH($W$2010,0),COUNTIFS(Diag,Diag,RefDate,">="&$W$2010,RefDate,"<="&EOMONTH($W$2010,0))+(ROW(RefDate)-ROW(INDEX(RefDate,1))+1)/10000))),1),1)*10000),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To Diag =Sheet3!$T$2:$T$25 RefDate =Sheet3!$Y$2:$Y$25 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

Looks like I really overcomplicated that formula. Try this one:

	T	U	V	W	X	Y	Z	AA	AB
1	Diagnosis					Date
2	Autism					1/31/2018		Most common
3	Down's Syn					12/5/2017		Autism	6
4	Asthma					12/12/2017		ID	2
5	ID					1/4/2018		Down's Syn	2
6	Autism					1/2/2018
7	Autism					12/5/2017
8	Asthma					12/2/2017
9	Down's Syn					1/17/2018
10	Autism					1/8/2018
11	Autism					1/3/2018
12	Down's Syn					12/15/2017
13	Asthma					12/2/2017
14	ID					1/6/2018
15	Autism					1/21/2018
16	Autism					12/15/2017
17	Asthma					12/12/2017
18	Down's Syn					1/1/2018
19	Autism					1/8/2018
2010			Date	1/1/2018

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula AB3 =IF(AA3="","",COUNTIFS(Diag,AA3,RefDate,">="&$W$2010,RefDate,"<="&EOMONTH($W$2010,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula AA3 {=IFERROR(INDEX(Diag,MODE(IF(COUNTIF($AA$2:$AA2,Diag)=0,IF(RefDate>=$W$2010,IF(RefDate<=EOMONTH($W$2010,0),MATCH(Diag,Diag,0)))))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To Diag =Sheet3!$T$2:$T$25 RefDate =Sheet3!$Y$2:$Y$25 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[leefletcher]

Oh, man, that works, but...

1. The named range has blanks but it works when I fill them with "No Diagnosis Given".
2. I wanted to list the results in columns by month and not include the count, if possible.

I can work around both but would appreciate any more help you could give to resolve them. I've already learned a ton from this!

Most Common Diagnoses	Dec '17	Jan '18	Feb '18
1st
2nd
3rd

<tbody>
</tbody>

Many thanks!

 

[Eric W]

That's all easy enough with some tweaks to the formula:

	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Diagnosis					Date
2	Autism					1/31/2018		Most Common Diagnoses	12/1/2017	1/1/2018	2/1/2018
3	Down's Syn					12/5/2017		1	Asthma	Autism
4	Asthma					12/12/2017		2	Down's Syn	ID
5	ID					1/4/2018		3	Autism	Down's Syn
6	Autism					1/2/2018
7	Autism					12/5/2017
8	Asthma					12/2/2017
9	Down's Syn					1/17/2018
10	Autism					1/8/2018
11	Autism					1/3/2018
12	Down's Syn					12/15/2017
13	Asthma					12/2/2017
14	ID					1/6/2018
15	Autism					1/21/2018
16	Autism					12/15/2017
17	Asthma					12/12/2017
18	Down's Syn					1/1/2018
19	Autism					1/8/2018

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula AC2 =EOMONTH(AB2,0)+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula AB3 {=IFERROR(INDEX(Diag,MODE(IF(COUNTIF(AB$2:AB2,Diag)=0,IF(LEN(SUBSTITUTE(Diag," ",""))>0,IF(RefDate>=AB$2,IF(RefDate<=EOMONTH(AB$2,0),MATCH(Diag,Diag,0))))))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To Diag =Sheet3!$T$2:$T$25 RefDate =Sheet3!$Y$2:$Y$25 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Removing the count is easy enough, just don't use that formula. Then put your starting date in AB2 (or whatever cell you want), and put the following months in the next columns. You can use the formula in AC2 if you want and drag right. You can even put in the 1st 2 dates, then use AutoFill to fill in the rest.

Now put in the AB3 formula, change the ranges to match your sheet, and confirm with Control+Shift+Enter. Now drag the formula down and right as needed. The AB2 here refers to the row right above the formula, and now expects the date to be there. Pay special attention to the $ signs, because they are designed to adapt as you copy the formula. The IF(LEN(SUBSTITUTE part I added to ignore any cells that are empty, or filled only with spaces.

Let me know how this works!

 

[leefletcher]

This is excellent and it works perfectly. Thank you!!

Note: I had to do some extra work other than just copying the formulas and changing the month cell references. I put in an explanation below, just in case you wanted to look at it more. However, it all works fine for me and my problem here is SOLVED!

2 things that may or may not matter:
-the formulas are on a different worksheet than the data.
-the months were in a row below the formulas

I copied the formula, referenced the month, double checked the $'s, and hit CSE. I got "Asthma". When I used drag to the right and then down, it just gave me "Asthma" in every field.

So...
I created some space on the data worksheet with the months directly above the formulas and it worked. On the formulas worksheet, I moved the months (that I'm using in other formulas) to the row above where I wanted the formulas and then copied the formulas over from the data worksheet. Dragging worked fine, and the data stayed the same when I grabbed the months and put them below all of the rest of my formulas. Problem SOLVED!

However,
When I saw that the 2nd and 3rd diagnoses were similar (I am using data that users enter free form), I thought that I might want 4th & 5th, as well. I grabbed the top row and used drag and everything changed 1st - 5th in every month to "Asthma" again. I then grabbed the 3rd and pulled it down for each month and it seems to work fine.

If there is something I can learn from this, I'd love to hear it... BUT I've taken up enough of your time.

Many, many thanks!

 

[Eric W]

I'm certainly glad that you got it working, and nice job adapting it to your workbook.

However, there's one thing that bears mentioning:

=IFERROR(INDEX(Diag,MODE(IF(COUNTIF([COLOR=#ff0000]AB$2:AB2[/COLOR],Diag)=0,IF(LEN(SUBSTITUTE(Diag," ",""))>0,IF(RefDate>=[COLOR=#0000ff]AB$2[/COLOR],IF(RefDate<=EOMONTH([COLOR=#0000ff]AB$2[/COLOR],0),MATCH(Diag,Diag,0))))))),"")

In this version of the formula that I posted, the AB2 cell is listed 3 times. It actually has 2 purposes. The 2 ranges in blue is the cell with the date. The one in red is to make sure that we don't list any diagnosis more than once. The COUNTIF looks at all the rows above the current cell to see if we've used the diagnosis before. So if your formula is in AX9, it should say AX$8:AX8. In my example, both locations were the same cell. You wanted the dates below, so the formula probably should look like:

=IFERROR(INDEX(Diag,MODE(IF(COUNTIF([COLOR=#ff0000]AB$2:AB2[/COLOR],Diag)=0,IF(LEN(SUBSTITUTE(Diag," ",""))>0,IF(RefDate>=[COLOR=#0000ff]AB$2010[/COLOR],IF(RefDate<=EOMONTH([COLOR=#0000ff]AB$2010[/COLOR],0),MATCH(Diag,Diag,0))))))),"")

The way you described to move the dates is clever and should be fine. But if you look at the formulas in the top row and the next row, the COUNTIF range is different.

Just a thought. If it's working OK, you don't need to change anything. In any case, I'm glad you got it working!