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 07:12
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

From: Ron Rosenfeld on 8 Feb 2010 08:37
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
From: Ron Rosenfeld on 8 Feb 2010 08:43
On Mon, 08 Feb 2010 08:37:11 -0500, Ron Rosenfeld <ronrosenfeld(a)nospam.org>
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

Here's an example of using the formula, instead of custom formatting, to return
nothing if there is no corresponding removal date in the column:

=IF(SMALL(IF(SN=$A2,Dates,1E+307),COLUMNS($A:A))=1E+307,"",
SMALL(IF(SN=$A2,Dates,1E+307),COLUMNS($A:A)))

Again, the above needs to be "array-entered" with ctrl-shift-enter.
--ron
From: Luke M on 8 Feb 2010 08:54
You can use this array** formula:

=SMALL(IF(A!$C$2:$C$100=$A2,A!$A$2:$A$100),COLUMN(A1))

Copy across and down as needed. Note that if a nth instance of a SN does not
occur, the formula will return the #NUM! error for that instance.

**Array formulas must be confirmed using Ctrl+Shift+Enter
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andri" 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
>
From: Andri on 8 Feb 2010 10:34
Dear Luke,

i cannot implement that formula, here is what i am targetting

Database in sheet "A"
Serial Number Date
X1 1-Jan-07
X2 1-Feb-07
X3 1-Mar-07
X1 1-Apr-07
X1 1-May-07
X4 1-Jun-07
X2 1-Jul-07
X3 1-Aug-07
X1 1-Sep-07
X3 1-Oct-07
X2 1-Nov-07


In sheet B.
I would like to match for SN, then search the first removal, then second
removal if any...so on.
1st Rmvl 2nd Rmvl 3rd Rmvl 4th Rmvl
X1 1-Jan-07 1-Apr-07 1-May-07 1-Sep-07
X2 1-Feb-07 1-Jul-07 1-Nov-07
X3 1-Mar-07 1-Aug-07 1-Oct-07
X4 1-Jun-07


"Luke M" wrote:

> You can use this array** formula:
>
> =SMALL(IF(A!$C$2:$C$100=$A2,A!$A$2:$A$100),COLUMN(A1))
>
> Copy across and down as needed. Note that if a nth instance of a SN does not
> occur, the formula will return the #NUM! error for that instance.
>
> **Array formulas must be confirmed using Ctrl+Shift+Enter
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Andri" 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
> >
 |  Next  |  Last
Pages: 1 2
Prev: Pivot Tables: Don't show data for detail, but still show subto
Next: Excel autofit row height not working