Vlookup Multiple Output

[Vin90]

I'm finding a formula which can filter out my data to only show 1 ID with 1 time associated date (if it has multiple date)

So my current Data table looks someting like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]O1[/TD]
[TD]O2[/TD]
[TD]O3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/1/2000[/TD]
[TD]123[/TD]
[TD]123[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/1/2000[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2/1/2000[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2/1/2000[/TD]
[TD]1234[/TD]
[TD]1234[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2/1/2000[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3/1/2000[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3/1/2000[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]


I'm trying to find a way to make new worksheet that can recognize and consolidate the date of particular ID, for example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]O1[/TD]
[TD]O2[/TD]
[TD]O3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/1/2000[/TD]
[TD]1234[/TD]
[TD]1234[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2/1/2000[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3/1/2000[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]

I found this a formula like this, which works OK, but I get multiple same date which I don't need..
=IF(ISERROR(INDEX(Data,SMALL(IF(RawData!$A:$A=$A$2,ROW(RawData!$A:$A)),ROW(1:1)),2)),"",INDEX(Data,SMALL(IF(RawData!$A:$A=$A$2,ROW(RawData!$A:$A)),ROW(1:1)),2))

Any suggestions?

 

[Vin90]

In E2 just enter and copy down:

=IF($A2<>$A3,ROW($A2)-ROW($A$2)+1,"")


In A1 just enter:

=COUNT(Sheet1!E2:E14)

In A3 control+shift+enter, not just enter, copy across, and down:

=IF(ROWS(A$3:A3)>$A$1,"",INDEX(Sheet1!A$2:A$14,SMALL(IF(ISNUMBER(Sheet1!$E$2:$E$14),Sheet1!$E$2:$E$14),ROWS(A$3:A3))))

This works beautifully, thanks a lot Aladin..

If I want to make this as an automated process, do I need build a macro? Because the data source is a dynamic range which will expand as users input daily data.

Thank you.

 

[Aladin Akyurek]

This works beautifully, thanks a lot Aladin..

If I want to make this as an automated process, do I need build a macro? Because the data source is a dynamic range which will expand as users input daily data.

Thank you.

You can use dynamic named ranges...

Define the following names one by one in the Name Manager:

1) Lrow as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$B:$B)

Adjust the sheet name if necessary (in all definitions).

2) Product as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

3) Date as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

4) TotalN as referring to:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

5) RejectN as referring to:

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,Lrow)

6) Finally, Last as referring to:

=Sheet1!$E$2:INDEX(Sheet1!$E:$E,Lrow)

Once done, you need to edit the formulas such that they refer to the dynamic named ranges.

The formula in E2 of Sheet1 remains unchanged:

=IF($A2<>$A3,ROW($A2)-ROW($A$2)+1,"")

The formula in A1 of Sheet2 becomes:

=COUNT(Last)

The formula in A3 of Sheet2 becomes:

=IF(ROWS(A$3:A3)>$A$1,"",INDEX(Product,SMALL(IF(ISNUMBER(Last),Last),ROWS(A$3:A3))))

This must be confirmed with control+shift+enter, not just enter, and copied down.

We need now separate formulas for B3, C3, and D3, all confirmed with control+shift+enter and copied down.

B3:

=IF(ROWS(A$3:A3)>$A$1,"",INDEX(Date,SMALL(IF(ISNUMBER(Last),Last),ROWS(A$3:A3))))

C3:

=IF(ROWS(A$3:A3)>$A$1,"",INDEX(TotalN,SMALL(IF(ISNUMBER(Last),Last),ROWS(A$3:A3))))

D3:

=IF(ROWS(A$3:A3)>$A$1,"",INDEX(RejectN,SMALL(IF(ISNUMBER(Last),Last),ROWS(A$3:A3))))

 

[Vin90]

You can use dynamic named ranges...

Thanks for the swift reply, it works perfect with the dynamic range..

Also I tried to put these formulas into vba code, to make the autofill automatic if the Sheet1 Source Data is updated with new values..
However, I only get the first row correct, the subsequent rows are a copy of row 1 values..

Is there a way to put them in macro as well?

Cheers

 

[Aladin Akyurek]

Thanks for the swift reply, it works perfect with the dynamic range..

Also I tried to put these formulas into vba code, to make the autofill automatic if the Sheet1 Source Data is updated with new values..
However, I only get the first row correct, the subsequent rows are a copy of row 1 values..

Is there a way to put them in macro as well?

Cheers

Whenever new records are added, they are automatically included.

 

[Vin90]

Whenever new records are added, they are automatically included.

I tried to input some dummy data, but it Sheet2 didn't get updated by itself.
Do I have to drag the formula until the end of the row in Sheet2?

 

[Aladin Akyurek]

I tried to input some dummy data, but it Sheet2 didn't get updated by itself.
Do I have to drag the formula until the end of the row in Sheet2?

Either copy down the formulas as far as you might need or write a VBA routine which reads the count (N) in A1 of Sheet2 and copies the formulas in A3:D3 to N-1 rows down.