Help with Array formula?

V

Victor Delta

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
 
T

T. Valko

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)
 
I

Infinitogool

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.
 
V

Victor Delta

Infinitogool said:
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
 
T

T. Valko

Victor Delta said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top