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?

 

[Aladin Akyurek]

Sheet1 (data)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] ID[/td][td] Date[/td][td] O1[/td][td] O2[/td][td] O3[/td][/tr]
[tr][td]

2​

[/td][td] A[/td][td] 1/1/2000[/td][td] 123[/td][td] 123[/td][td] 123[/td][/tr]
[tr][td]

3​

[/td][td] A[/td][td] 1/1/2000[/td][td] 12[/td][td] 1[/td][td] 1[/td][/tr]
[tr][td]

4​

[/td][td] A[/td][td] 2/1/2000[/td][td] 14[/td][td] 15[/td][td] 16[/td][/tr]
[tr][td]

5​

[/td][td] A[/td][td] 2/1/2000[/td][td] 1234[/td][td] 1234[/td][td] 1234[/td][/tr]
[tr][td]

6​

[/td][td] B[/td][td] 2/1/2000[/td][td] 20[/td][td] 21[/td][td] 22[/td][/tr]
[tr][td]

7​

[/td][td] B[/td][td] 3/1/2000[/td][td] 23[/td][td] 24[/td][td] 25[/td][/tr]
[tr][td]

8​

[/td][td] B[/td][td] 3/1/2000[/td][td] 26[/td][td] 27[/td][td] 28[/td][/tr]
[/table]

Sheet2 (sublisting)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]idx[/td][td] ID[/td][td] Date[/td][td] O1[/td][td] O2[/td][td] O3[/td][/tr]
[tr][td]

4​

[/td][td]

1​

[/td][td]A[/td][td] 1/1/2000[/td][td]

123​

[/td][td]

123​

[/td][td]

123​

[/td][/tr]
[tr][td]

5​

[/td][td]

2​

[/td][td]A[/td][td] 1/1/2000[/td][td]

12​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td]

6​

[/td][td]

3​

[/td][td]A[/td][td] 2/1/2000[/td][td]

14​

[/td][td]

15​

[/td][td]

16​

[/td][/tr]
[tr][td]

7​

[/td][td]

4​

[/td][td]A[/td][td] 2/1/2000[/td][td]

1234​

[/td][td]

1234​

[/td][td]

1234​

[/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In A2 just enter:

=COUNTIFS(Sheet1!A2:A8,A1)

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

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$A$2:$A$8=$A$1,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$8,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

 

[Vin90]

Hi @Aladin Akyurek

Thanks for your reply, but this is not the result that I'm expecting. Grouping the ID-A is already correct, but I still need a formula that can filter the date into 1 entry, for example, in Sheet1(DATA) B2:B5 there are two of 1/1/2000 and 2/1/2000, I need only 1 entry of each in Sheet2.

Dont worry about the other column, because the date filter is the only needed..

Sheet2 (sublisting)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]idx[/TD]
[TD] ID[/TD]
[TD] Date[/TD]
[TD] O1[/TD]
[TD] O2[/TD]
[TD] O3[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]A[/TD]
[TD] 1/1/2000[/TD]
[TD]

123​

[/TD]
[TD]

123​

[/TD]
[TD]

123​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD] 1/1/2000[/TD]
[TD]

12​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

3​

[/TD]
[TD]A[/TD]
[TD] 2/1/2000[/TD]
[TD]

14​

[/TD]
[TD]

15​

[/TD]
[TD]

16​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

4​

[/TD]
[TD]A[/TD]
[TD] 2/1/2000[/TD]
[TD]

1234​

[/TD]
[TD]

1234​

[/TD]
[TD]

1234​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In A2 just enter:

=COUNTIFS(Sheet1!A2:A8,A1)

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

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$A$2:$A$8=$A$1,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$8,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

[/QUOTE]

 

[Aladin Akyurek]

hi @aladin akyurek

thanks for your reply, but this is not the result that i'm expecting. Grouping the id-a is already correct, but i still need a formula that can filter the date into 1 entry, for example, in sheet1(data) b2:b5 there are two of 1/1/2000 and 2/1/2000, i need only 1 entry of each in sheet2.

Dont worry about the other column, because the date filter is the only needed..

I'm afraid I'm misled by the formula you posted and the incomplete output. I think What follows is what you require...

Sheet1 (houses the data)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] ID[/td][td] Date[/td][td] O1[/td][td] O2[/td][td] O3[/td][/tr]
[tr][td]

2​

[/td][td] A[/td][td] 1/1/2000[/td][td] 123[/td][td] 123[/td][td] 123[/td][/tr]
[tr][td]

3​

[/td][td] A[/td][td] 1/1/2000[/td][td] 12[/td][td] 1[/td][td] 1[/td][/tr]
[tr][td]

4​

[/td][td] A[/td][td] 2/1/2000[/td][td] 14[/td][td] 15[/td][td] 16[/td][/tr]
[tr][td]

5​

[/td][td] A[/td][td] 2/1/2000[/td][td] 1234[/td][td] 1234[/td][td] 1234[/td][/tr]
[tr][td]

6​

[/td][td] B[/td][td] 2/1/2000[/td][td] 20[/td][td] 21[/td][td] 22[/td][/tr]
[tr][td]

7​

[/td][td] B[/td][td] 3/1/2000[/td][td] 23[/td][td] 24[/td][td] 25[/td][/tr]
[tr][td]

8​

[/td][td] B[/td][td] 3/1/2000[/td][td] 26[/td][td] 27[/td][td] 28[/td][/tr]
[/table]

Sheet2 (implements unique sublist processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td] ID[/td][td] Date[/td][td] O1[/td][td] O2[/td][td] O3[/td][/tr]
[tr][td]

3​

[/td][td]A[/td][td] 1/1/2000[/td][td]

135​

[/td][td]

124​

[/td][td]

124​

[/td][/tr]
[tr][td]

4​

[/td][td]A[/td][td] 2/1/2000[/td][td]

1248​

[/td][td]

1249​

[/td][td]

1250​

[/td][/tr]
[tr][td]

5​

[/td][td]B[/td][td] 2/1/2000[/td][td]

20​

[/td][td]

21​

[/td][td]

22​

[/td][/tr]
[tr][td]

6​

[/td][td]B[/td][td] 3/1/2000[/td][td]

49​

[/td][td]

51​

[/td][td]

53​

[/td][/tr]
[tr][td]

7​

[/td][td][/td][td] [/td][td][/td][td][/td][td][/td][/tr]
[/table]

In A1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Sheet1!A2:A8=""),IF(ISNUMBER(Sheet1!B2:B8),MATCH(Sheet1!B2:B8&Sheet1!A2:A8,Sheet1!B2:B8&Sheet1!A2:A8,0))),ROW(Sheet1!A2:A8)-ROW(Sheet1!A2)+1),1))

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

=IF(ROWS(A$3:A3)>$A$1,"",INDEX(Sheet1!A$2:A$8,SMALL(IF(FREQUENCY(IF(1-(Sheet1!$A$2:$A$8=""),IF(ISNUMBER(Sheet1!$B$2:$B$8),MATCH(Sheet1!$B$2:$B$8&Sheet1!$A$2:$A$8,Sheet1!$B$2:$B$8&Sheet1!$A$2:$A$8,0))),ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROWS(A$3:A3))))

In C3 just enter, copy across, and down:

=IF($A3="","",SUMIFS(INDEX(Sheet1!$C$2:$E$8,0,MATCH(C$2,Sheet1!$C$1:$E$1,0)),Sheet1!$A$2:$A$8,$A3,Sheet1!$B$2:$B$8,$B3))

 

[Vin90]

Sorry, I think my earlier explanation was not clear enough.

I've tried the given formula:

=SUM(IF(FREQUENCY(IF(1-(Sheet1!A2:A8=""),IF(ISNUMBER(Sheet1!B2:B8),MATCH(Sheet1!B2:B8&Sheet1!A2:A8,Sheet1!B2:B8&Sheet1!A2:A8,0))),ROW(Sheet1!A2:A8)-ROW(Sheet1!A2)+1),1))

This works well.

The one in A3 also works well, but when i drag to B3 (as suggested) it returned with the same value as A3.

=IF(ROWS(A$3:A3)>$A$1,"",INDEX(Sheet1!A$2:A$8,SMALL(IF(FREQUENCY(IF(1-(Sheet1!$A$2:$A$8=""),IF(ISNUMBER(Sheet1!$B$2:$B$8),MATCH(Sheet1!$B$2:$B$8&Sheet1!$A$2:$A$8,Sheet1!$B$2:$B$8&Sheet1!$A$2:$A$8,0))),ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROWS(A$3:A3))))

So the output now looks something like this

Sheet2 (implements unique sublist processing)

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]O1[/TD]
[TD]O2[/TD]
[TD]O3[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In practice, I changed the below highlighted range to a group name (dynamic), does it affect the formula in general?
=IF(ROWS(A$3:A3)>$A$1,"",INDEX(Sheet1!A$2:A$8,SMALL(IF(FREQUENCY(IF(1-(Sheet1!$A$2:$A$8=""),IF(ISNUMBER(Sheet1!$B$2:$B$8),MATCH(Sheet1!$B$2:$B$8&Sheet1!$A$2:$A$8,Sheet1!$B$2:$B$8&Sheet1!$A$2:$A$8,0))),ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROWS(A$3:A3))))

 

[Aladin Akyurek]

You need to apply cobtrol+shift+enter in a3, then copy across to b3...

The formula in c3 requires just enter, then copied across and down.

 

[Vin90]

@Aladin Akyurek

Please disregard the last reply.. I think I've overthinking the way I want to approach my problem..
Looking at different angle, I see certain pattern in my data that can help things easier, but I need some help..

Sheet 1 Data Source
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Index[/TD]
[TD]Date[/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]12/10/17[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]12/10/17[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]13/10/17[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]13/10/17[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]13/10/17[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]14/10/17[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]C[/TD]
[TD]3[/TD]
[TD]14/10/17[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C[/TD]
[TD]3[/TD]
[TD]14/10/17[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

I figure maybe it will be easier to assign number to the product so on Sheet1!B2:B I use

=if(A2="A",1,if(A2="B"=2,3))

Then I put this formula:

=if(Sheet1!B2=B1,"",Sheet1!A2)

and drag down

on a new sheet Sheet2 A, and I get something like this:

Sheet 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

My intention was to obtained a consolidated table without the gaps (like this), and put a formula on B2 to get the last date of a series of consecutive product.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]13/10/17[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]14/10/17[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]14/10/17[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

Hence I can use a function which return the value that match A&B from the source page.


Sorry for the confusion

 

[Aladin Akyurek]

Please refrain from providing formulas yourself regarding the problem you want help for. The best way is: an input sample along with the output you need, augmented with a description in words.

A question which involves the new input sample. Does your source data (Sheet1) really have that index column? If it does not, care to post the input sample as is along with the output that goes with that?

 

[Vin90]

Sorry, I'm a bit confused how to explain this. But I don't have the index column in my original Data sheet.

The current state of my data looks like the following:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Total Number[/TD]
[TD]Reject Number[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]12/10/17[/TD]
[TD]500[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]12/10/17[/TD]
[TD]800[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]12/10/17[/TD]
[TD]200[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD]13/10/17[/TD]
[TD]700[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]13/10/17[/TD]
[TD]1200[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]13/10/17[/TD]
[TD]1700[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C[/TD]
[TD]14/10/17[/TD]
[TD]1000[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD]14/10/17[/TD]
[TD]1500[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]A[/TD]
[TD]14/10/17[/TD]
[TD]300[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]A[/TD]
[TD]15/10/17[/TD]
[TD]900[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]C[/TD]
[TD]15/10/17[/TD]
[TD]1500[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]C[/TD]
[TD]15/10/17[/TD]
[TD]2500[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]B[/TD]
[TD]16/10/17[/TD]
[TD]600[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

I want to compile these data into new worksheet that looks like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Total Number[/TD]
[TD]Reject Number[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]12/10/17[/TD]
[TD]800[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]13/10/17[/TD]
[TD]1700[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]14/10/17[/TD]
[TD]1500[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]15/10/17[/TD]
[TD]900[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C[/TD]
[TD]15/10/17[/TD]
[TD]2500[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]16/10/17[/TD]
[TD]600[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

A formula that basically filter the data source and take only the last value of series of product.

Additional information, I have the Data Source (A1:D15), Product (A2:A) and Date (B2:B) in the group with a dynamic OFFSET function.


I hope this make sense.

 

[Aladin Akyurek]

Sorry, I'm a bit confused how to explain this. But I don't have the index column in my original Data sheet.
[...]

Sheet1

[TABLE="class: grid, width: 429"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Total Number[/TD]
[TD]Reject Number[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]12-Oct-2017[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]12-Oct-2017[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]12-Oct-2017[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]13-Oct-2017[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]13-Oct-2017[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]13-Oct-2017[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]14-Oct-2017[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]14-Oct-2017[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]14-Oct-2017[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]15-Oct-2017[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]15-Oct-2017[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]15-Oct-2017[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]16-Oct-2017[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]13[/TD]
[/TR]
</tbody>[/TABLE]

In E2 just enter and copy down:

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

This extends the data wit an additional column in order to support the processing in Sheet2.

Sheet2

[TABLE="class: grid, width: 344"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Total Number[/TD]
[TD]Reject Number[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]12-Oct-2017[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]13-Oct-2017[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]14-Oct-2017[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]15-Oct-2017[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]15-Oct-2017[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]16-Oct-2017[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
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))))