Hi there,
I am trying to make a spreadsheet in which I need to be able to return multiple values corresponding to the same dates without repeating the first returned value again (If that makes sense!!). For example:
A (birthdates) B(people)
1 2/04/2012 Ash
2 3/04/2012 San
3 2/04/2012 Ong
4 5/04/2012 Joe
5 6/04/2012 Raj
6 2/04/2012 Sur
7 8/04/2012 Ryan

Say for example, out of the list I want to return all of the people with a birthday on the 2/04/2012. I can make a formula like:
'=VLOOKUP(A10,B2:C8, 2, FALSE)', where the cell range is B2:C8, A10 is the date (i.e. 2/04/2012) and the dates are located in column 2. It will return 'Ash' as having a birthday on the required date (which is great). However, I want to make a formula for the cell below this to give me the next person having a birthday on the 2/04/2012 (i.e. Ong), but not return 'Ash' again. And continue this process below Ong (i.e. Return Sur but not Ong or Ash).

I hope that makes sense and someone is able to help me out. I have been thinking about it for a while now, but I'm not super talented when it comes to excel forumlae!
Thanks

On Wed, 22 Feb 2012 05:41:05 +0000, azlan270690 wrote:


Hi there,
I am trying to make a spreadsheet in which I need to be able to return
multiple values corresponding to the same dates without repeating the
first returned value again (If that makes sense!!). For example:
A (birthdates) B(people)
1 2/04/2012 Ash
2 3/04/2012 San
3 2/04/2012 Ong
4 5/04/2012 Joe
5 6/04/2012 Raj
6 2/04/2012 Sur
7 8/04/2012 Ryan

Say for example, out of the list I want to return all of the people with
a birthday on the 2/04/2012. I can make a formula like:
'=VLOOKUP(A10,B2:C8, 2, FALSE)', where the cell range is B2:C8, A10 is
the date (i.e. 2/04/2012) and the dates are located in column 2. It will
return 'Ash' as having a birthday on the required date (which is great).
However, I want to make a formula for the cell below this to give me the
next person having a birthday on the 2/04/2012 (i.e. Ong), but not
return 'Ash' again. And continue this process below Ong (i.e. Return Sur
but not Ong or Ash).

I hope that makes sense and someone is able to help me out. I have been
thinking about it for a while now, but I'm not super talented when it
comes to excel forumlae!
Thanks

A simple method would be use a filter rather than a formula. That could be semi-automated using a macro. Would that be acceptable?

If you need a formula, you could use the following:

This formula must be **array-entered**:

=IFERROR(INDEX(People,SMALL((Birthdates=$A$10)*
ROW(Birthdates),COUNTA(Birthdates)-COUNTIF(
Birthdates,$A$10)+ROWS($1:1))),"")

In the above, People is a NAME'd range that *includes* the list of People (including the label in the first row); likewise for Birthdates. (You could substitue absolute references for the names; e.g. $B$1:$B$8)
After entering the formula in some cell, fill down as far as required. The formula will adjust to return subsequent matches of the birthday.
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.


For versions of Excel prior to 2007:

=IF((COUNTA(Birthdates)-COUNTIF(Birthdates,$A$10)+
ROWS($1:1))COUNTA(Birthdates),"",INDEX(
People,SMALL((Birthdates=$A$10)*ROW(Birthdates),
COUNTA(Birthdates)-COUNTIF(Birthdates,$A$10)+ROWS($1:1))))
I

Thanks for your help Ron. Yeah unfortunately I am unable to use a filter function for the particular spreadsheet I am using. But the formula was exactly what I was after. Thanks again.

On Thu, 23 Feb 2012 09:27:17 +0000, azlan270690 wrote:


Thanks for your help Ron. Yeah unfortunately I am unable to use a filter
function for the particular spreadsheet I am using. But the formula was
exactly what I was after. Thanks again.

Glad to help. Thanks for the feedback.