Hello I am searching an alternative to the pivot table.
Since the pivot table works only if data are in the same header (column) how can I select a certain type of date for a given reference?
Let me explain here. I have a similar situation as below:
[TABLE="width: 1392"]
<tbody>[TR]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]65654[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]39876[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]6666[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]6654[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
I need to fill below table:
[TABLE="width: 261"]
<tbody>[TR]
[TD="width: 87"]Date[/TD]
[TD="class: xl63, width: 87, align: right"]21/02/2019[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Since I have more than 4 ids I don't want to put them manually under the same column.
Is there an index match or some other formula to search values for a specific date?
Thanks
Since the pivot table works only if data are in the same header (column) how can I select a certain type of date for a given reference?
Let me explain here. I have a similar situation as below:
[TABLE="width: 1392"]
<tbody>[TR]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[TD="class: xl64, width: 87"]ID[/TD]
[TD="class: xl64, width: 87"]Date[/TD]
[TD="class: xl64, width: 87"]Value 1[/TD]
[TD="class: xl64, width: 87"]Value2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]27/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]26/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]65654[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]25/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]39876[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]24/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]23/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]22/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]21/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]20/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]6666[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]6654[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]19/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]AAA[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]B[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]CC[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl63, align: right"]18/02/2019[/TD]
[TD]CCC[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
I need to fill below table:
[TABLE="width: 261"]
<tbody>[TR]
[TD="width: 87"]Date[/TD]
[TD="class: xl63, width: 87, align: right"]21/02/2019[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Since I have more than 4 ids I don't want to put them manually under the same column.
Is there an index match or some other formula to search values for a specific date?
Thanks