Display a list if count is >1

gtd526

Active Member
Joined
Jul 30, 2013
Messages
297
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I have a list of upcoming birthdays (see below). I can list just 1, but 1+ ??
More than 1 birthday on same day.
How can I create a list of student names if count(D) is >1? ie Row 14, count of 2
Student names are listed: Kids!$B:$B
Next birthday: Kids!$J:$J

Thank you.

_Talentos SAH Artists_ - EXCELL.xlsm
ABCDE
1All Kids - Birthdays Coming
2SexStudentBirthdayCountIn () Days
3   01
4   02
5FNaomy Suarez 9/24/202013
6   04
7   05
8   06
9   07
10   08
11   09
12   010
13   011
14FGianna Diftereos10/3/2020212
15   013
16   014
17   015
Birthday List
Cell Formulas
RangeFormula
A3:A17A3=IFERROR(INDEX(Kids!L:L,MATCH($B3,Kids!B:B,0)),"")
B3:B17B3=IFERROR(INDEX(Kids!$B:$B,MATCH($E3,Kids!$K:$K,0)),"")
C3:C17C3=IFERROR(INDEX(Kids!$J:$J,MATCH($E3,Kids!$K:$K,0)),"")
D3D3=COUNTIF(Kids!$K$6:$K$22,"=1")
D4D4=COUNTIF(Kids!$K$6:$K$22,"=2")
D5D5=COUNTIF(Kids!$K$6:$K$22,"=3")
D6D6=COUNTIF(Kids!$K$6:$K$22,"=4")
D7D7=COUNTIF(Kids!$K$6:$K$22,"=5")
D8D8=COUNTIF(Kids!$K$6:$K$22,"=6")
D9D9=COUNTIF(Kids!$K$6:$K$22,"=7")
D10D10=COUNTIF(Kids!$K$6:$K$22,"=8")
D11D11=COUNTIF(Kids!$K$6:$K$22,"=9")
D12D12=COUNTIF(Kids!$K$6:$K$22,"=10")
D13D13=COUNTIF(Kids!$K$6:$K$22,"=11")
D14D14=COUNTIF(Kids!$K$6:$K$22,"=12")
D15D15=COUNTIF(Kids!$K$6:$K$22,"=13")
D16D16=COUNTIF(Kids!$K$6:$K$22,"=14")
D17D17=COUNTIF(Kids!$K$6:$K$22,"=15")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:A17Expression=$A3="M"textNO
A3:A17Expression=$A3="F"textNO
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
It's confusing to me because your XL2BB doesn't show the J nor K columns.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
297
Office Version
  1. 2019
Platform
  1. Windows
It's confusing to me because your XL2BB doesn't show the J nor K columns.
Sorry about that.
Its a different worksheet in the same workbook. I didn't create it, just adding a few things.
_Talentos SAH Artists_ - EXCELL.xlsm
ABCDEFGHIJKL
1MIN age:8
2 KIDSMAX age:15
3Legal Nameadded column
4NOMBRECASTING NETWORKPADRE O REPRESENTANTEFOTOCONTRATOBIRTHDAYEDADAGENext BdayBday In:SEX
5GIRLSDays
61Any Dalay Rodriguez SIDianele Gomez LopezsiSi12/27/2007111212/27/202097F
72Gianna DiftereosSIJordana FernandezsiSi10/3/20117810/3/202012F
83Brianny Pazmino NOKennyasino10/7/20116810/7/202016F
94Naomy Suarez SINildesisi9/24/200514149/24/20203F
105Tamara PiconesNOLourdessi?1/17/200712131/17/2021118F
116Valerie CardellaSIKemia Marrerosisi10/25/2008101110/25/202034F
127Barbara PortalesSIBarbara Rgz/Yosuanisi?10/3/2007121210/3/202012F
138Alejandra GarciaNOYanisleidy LarasiSI10/22/20108910/22/202031F
149Adiley TamayoNO FOTOOlga Santossisi6/3/200514156/3/2021255F
1510Katie PerezNOYasmin Josendesisi firmar1/2/200712131/2/2021103F
1611Sarah Marie Luis SIDenise Mesasisi11/10/2006131311/10/202050F
1712Talisa JustinianoSITerely Justiniano?si11/26/2009101011/26/202066F
1813Geraldine NuñezNOTalia/Alberto Ferreirasipendiente3/16/2011893/16/2021176F
1914Sofia MirabalSIGretchen Roig/Rolando Mirabalsisi1/25/20109101/25/2021126F
2015Isabella Lauren DiazSIPavel Diaz/ elizabethsisi11/7/2006121311/7/202047F
2116Elizabeth ChineaNOOscar Chinea/ Araceli Torres?si7/11/200712137/11/2021293F
2217Allison GarciaNOBrian Garcia/Maria?si9/1/20109109/1/2021345F
Kids
Cell Formulas
RangeFormula
J1J1=MIN(I6:I22)
J2J2=MAX(I6:I22)
I6:I22I6=INT(YEARFRAC($G6,TODAY()))
J6:J22J6=DATE(YEAR(G6)+DATEDIF(G6,TODAY(),"y")+1,MONTH(G6),DAY(G6))
K6:K22K6=DATEDIF(TODAY(),J6,"d")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I6:I22Cell Valuetop 1 bottom valuestextNO
I6:I22Cell Valuetop 1 valuestextNO
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
I thought if you put the # Days in O1 (e.g., find all who have bdays less than or equal to O1's value days away), you could do a macro to filter your people:

Code:
Sub MyFilter()
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A7:L43").AutoFilter Field:=11, Criteria1:="<=" & Range("O1").Value
    End With
End Sub

There's probably another VBA approach if you wanted the people listed, but I haven't created that.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
297
Office Version
  1. 2019
Platform
  1. Windows
I thought if you put the # Days in O1 (e.g., find all who have bdays less than or equal to O1's value days away), you could do a macro to filter your people:

Code:
Sub MyFilter()
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A7:L43").AutoFilter Field:=11, Criteria1:="<=" & Range("O1").Value
    End With
End Sub

There's probably another VBA approach if you wanted the people listed, but I haven't created that.
I found the solution. Using Index Match combo.
thx for the reply.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,864
Members
414,409
Latest member
FloordAlex

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
Top