Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Date againsts unique customer list
I have an initial customer list A2:A1000 and the Dates each customer called
in with a question B2:B1000 from that list I have created (using advanced filtering) a unique List (G2:G100) A2:A1000 are of course not all unique customers, as the same customer could of called in any number of times through the year. However using my Unique list (G2: G100) I would like to get the first date in which this customer called and nothing more and display it in the cells H2:H100 I am using excel2007 but it needs to be backward compatible with 2003. Any suggestions -- Nelson |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Date againsts unique customer list
If the dates are in ascending order you could use VLOOKUP
VLOOKUP(G2,$A$2:$B$1000,2,FALSE) since it will return the first match it finds Otherwise =MIN(IF($A$2:$A$10=G2,$B$2:$B$10)) this is an array fromula and must tbe entered with CTRL+SHIFT+ENTER not just ENTER Once you have it in H2 you can copy it normally down the column best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Nelson" wrote in message ... I have an initial customer list A2:A1000 and the Dates each customer called in with a question B2:B1000 from that list I have created (using advanced filtering) a unique List (G2:G100) A2:A1000 are of course not all unique customers, as the same customer could of called in any number of times through the year. However using my Unique list (G2: G100) I would like to get the first date in which this customer called and nothing more and display it in the cells H2:H100 I am using excel2007 but it needs to be backward compatible with 2003. Any suggestions -- Nelson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Date againsts unique customer list
On Tue, 7 Jul 2009 08:20:01 -0700, Nelson
wrote: I have an initial customer list A2:A1000 and the Dates each customer called in with a question B2:B1000 from that list I have created (using advanced filtering) a unique List (G2:G100) A2:A1000 are of course not all unique customers, as the same customer could of called in any number of times through the year. However using my Unique list (G2: G100) I would like to get the first date in which this customer called and nothing more and display it in the cells H2:H100 I am using excel2007 but it needs to be backward compatible with 2003. Any suggestions If the dates in column B are sorted (ascending) then you may try this in cell H2: =VLOOKUP(G2,A$2:B$1000,2,FALSE) Copy down to cell H100 If the dates in column B are not sorted then you may try this in cell H2: =MIN(IF(A$2:A$1000=G2,B$2:B$1000)) Copy down to cell H100. Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pick out unique components in a list with unique and common | Excel Discussion (Misc queries) | |||
CONVER CSV CUSTOMER DATA TO A CUSTOM INDIVI CUSTOMER PRICE SHEET | Excel Discussion (Misc queries) | |||
how do I advance filter a large csv file for unique customer #'s | Excel Worksheet Functions | |||
Customer List Categorized | New Users to Excel | |||
To get customer list | Excel Worksheet Functions |