Find all the names with same birthday

[tpsdas]

Find all the names with same birthday
Names (in column A - A2: A100) & birthdays (in column B - B2: B100).
I want to know all the names of people who are having birthday in a particular date in excel ( C1).
VB is Not to be used please.
Please note : in same date multiple peoples birthday are there.
This is required to print birthday cards

[JohnTopley]

Try

Created named ranges ("Name" and "Birth_date" for columns A and B) defined below


"Name"

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,"?*"))

"Birth_Date"

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))


=IFERROR(INDEX($A:$A,SMALL(IF(TEXT(Birth_Date,"ddmm")=TEXT($C$1,"ddmm"),ROW(Name),""),ROWS($1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

then copy down

Ensure ALL dates are Excel date format (dd/mm/yyyy)

[AlKey]

Try this:
1. Make sure that the date in C1 is a real date and make sure the dates in Col B are not text.
2. Enter in D1

this will count the number of people

Please Login or Register  to view this content.

The Enter array formula in E1 and copy down

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Please Login or Register  to view this content.

[tpsdas]

JohnTopley sir, Thnaks for the help.
But the formula or the way uou have done , i did not understand. Also the attachment is you have
given is not showing the names. Can u kindly correct it & resend, so that I can use this

[JohnTopley]

It shows names in column D ???

[tpsdas]

no sir, when i down load the excel file it is showing #NAME?

[JohnTopley]

This is due to the Excel version you have (2003)

Try these:

Named ranges


=Sheet1!$A$2:INDEX(Sheet1!$A2:$A1000,COUNTIF(Sheet1!$A2:$A1000,"?*"))


=Sheet1!$B$2:INDEX(Sheet1!$B2:$B1000,COUNTA(Sheet1!$B2:$B1000))


In D2

=IFERROR(INDEX($A$2:$A$1000,SMALL(IF(TEXT(Birth_Date,"ddmm")=TEXT($C$1,"ddmm"),ROW(Name)-ROW($A$2)+1,""),ROWS($1:1))),"")

[tpsdas]

thanks...working.
tomorrow I will use this in actual worksheet
Another request sir .
Kindly remove the column names & use the actual column like (A2: A100) and resent the excel format.
This is just for my under standing. It is a small request sir

[JohnTopley]

Simply replace the names with $A$2:$A$1000 or $B$2:$B$1000 or whatever maximum you want.