Alternative to the pivot table

00alex00

New Member
Joined
Jun 18, 2015
Messages
40
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
 
If you can, everything will be easier with well structured data. Such as re-arrange the data to a single table.

For info, a pivot table can be made from poorly structured data - it is just an example of combining data from more than one table. Suggest you google.
 
Last edited:
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top