Prev: Pivot Tables: Don't show data for detail, but still show subto
Next: Excel autofit row height not working
From: Andri on 8 Feb 2010 10:48
Dear Ron,

same to you, excellent formula, case solved by now.

thank you for your kind guidance...highly appreciated.

respectfully,
andri

"Ron Rosenfeld" wrote:

> On Mon, 8 Feb 2010 04:12:01 -0800, Andri <Andri(a)discussions.microsoft.com>
> wrote:
>
> >Dear Experts,
> >
> >Please help for the following condition:
> >
> >1. I have one sheet "A" which contain of database, the field structure as
> >follows:
> >
> >Field A = Date
> >Field B = SN
> >Filed C ...
> >
> >I have a lot of records, which can contains the same SN (Serial Number) but
> >with different Removal Date.
> >
> >2. on the other worksheet...i create the following field
> >
> >COL A = SN
> >COL B = First Removal Date
> >COL C = Second Removal Date.
> >COL D = Third Removal Date.
> >
> >Please guide to which function i should use? As i use VLOOKUP, it will be go
> >the first occurance of the date removal...(the result works properly in FIELD
> >B), but not on Column C, D, etc.
> >
> >thank you for your kind help and TIA.
> >
> >respectfully,
> >andri
>
> Here's one method that might work:
>
> I assume you enter your serial numbers manually starting in A2
> SN is the named range of serial numbers on Sheet"A" (e.g. perhaps A1:A10000).
> Unless you are using Excel 2007+, do NOT define SN as a full column.
>
> This formula must be **array-entered**:
>
> B2: =LARGE(IF(SN=$A2,Dates,0),COLUMNS($A:A))
> ----------------------------------------
> 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.
>
> Then Fill Right and down as required.
>
> IMPORTANT NOTES:
>
> The formula will return a zero if there are more possible Removal Dates than
> entries for a particular serial number. You could TEST the return to see if it
> returns a zero, or you could use a custom format: m/d/yyyy;; so the zero's
> will not be displayed.
>
> -------------------------------------------------------
>
> As written, this will return the values in descending order (latest Removal
> Date first). If you want the answers sorted the other way, then:
>
> =SMALL(IF(SN=$A2,Dates,1E+307),COLUMNS($A:A))
>
> and use this custom format: [<2958466]m/d/yyyy;;
>
> --ron
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Pivot Tables: Don't show data for detail, but still show subto
Next: Excel autofit row height not working