Removal Date

[Andri]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Luke M]

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

 

[Andri]

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

 

[Andri]

Dear Luke,

Excellent formula, case solved...

thank you and highly appreciated.

respectfully,
andri

P/S : Please ignore the previous message

 

[Andri]

Dear Ron,

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

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

respectfully,
andri

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
.