Help with Array formula?
 

Can anyone help me with an appropriate array formula please - at least I
assume the following will need an array formula.

I have a spreadsheet which, in two adjacent columns (say A & B), records
names of people and dates of meetings with them. Many of the names are
repeated. NB Not all the dates are in chronological order.

In Col D I have a simple list of all the names (not repeated). What I would
like to show in the corresponding cells in Col E is the date of the most
recent meeting with each person on the list.

So far it's defeated me - can anyone help please?

Thanks,

V

Help with Array formula?
 

Try this:

A1:A100 = names with repeats
B1:B100 = random dates
D1:Dn = unique names

Enter this array formula in E1 and copy down as needed:

=MAX(IF(A$1:A$100=D1,B$1:B$100))

Format as DATE

Note that if a name in D dosen't appear in A the formula will return 0 and
formatted as a DATE it will display as 1/0/1900.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Victor Delta" wrote in message
...
Can anyone help me with an appropriate array formula please - at least I
assume the following will need an array formula.

I have a spreadsheet which, in two adjacent columns (say A & B), records
names of people and dates of meetings with them. Many of the names are
repeated. NB Not all the dates are in chronological order.

In Col D I have a simple list of all the names (not repeated). What I
would like to show in the corresponding cells in Col E is the date of the
most recent meeting with each person on the list.

So far it's defeated me - can anyone help please?

Thanks,

V

Help with Array formula?
 

Hi Victor

you could try this, array-entered, E1:
dates: A1:B100
=MAX(($A$1:$A$100=D1)*$B$1:$B$100)

Copy down as far as needed

Regards,
Pedro J.

Can anyone help me with an appropriate array formula please - at least I
assume the following will need an array formula.

I have a spreadsheet which, in two adjacent columns (say A & B), records
names of people and dates of meetings with them. Many of the names are
repeated. NB Not all the dates are in chronological order.

In Col D I have a simple list of all the names (not repeated). What I
would like to show in the corresponding cells in Col E is the date of
the most recent meeting with each person on the list.

So far it's defeated me - can anyone help please?

Thanks,

V

Help with Array formula?
 

"Infinitogool" wrote in message
...
Hi Victor

you could try this, array-entered, E1:
dates: A1:B100
=MAX(($A$1:$A$100=D1)*$B$1:$B$100)

Copy down as far as needed

Very many thanks to both of you.

Biff - I have used your formula and it works perfectly. I also used
conditional formatting to hide the unwanted zeros.

V

Help with Array formula?
 

"Victor Delta" wrote in message
...
"Infinitogool" wrote in message
...
Hi Victor

you could try this, array-entered, E1:
dates: A1:B100
=MAX(($A$1:$A$100=D1)*$B$1:$B$100)

Copy down as far as needed

Very many thanks to both of you.

Biff - I have used your formula and it works perfectly. I also used
conditional formatting to hide the unwanted zeros.

V

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP