Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This is probably an Array Formula? | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula Help | Excel Worksheet Functions | |||
Array Formula Help | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |