Alternative to the pivot table

[00alex00]

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:

ID	Date	Value 1	Value2	ID	Date	Value 1	Value2	ID	Date	Value 1	Value2	ID	Date	Value 1	Value2
1	27/02/2019	AAA	129	2	27/02/2019	B	140	3	27/02/2019	CC	28	4	27/02/2019	CCC	1
1	26/02/2019	AAA	239	2	26/02/2019	B	230	3	26/02/2019	CC	48	4	26/02/2019	CCC	65654
1	25/02/2019	AAA	34	2	25/02/2019	B	44	3	25/02/2019	CC	29	4	25/02/2019	CCC	39876
1	24/02/2019	AAA	56	2	24/02/2019	B	49	3	24/02/2019	CC	46	4	24/02/2019	CCC	234
1	23/02/2019	AAA	32	2	23/02/2019	B	20	3	23/02/2019	CC	51	4	23/02/2019	CCC	5
1	22/02/2019	AAA	34	2	22/02/2019	B	22	3	22/02/2019	CC	51	4	22/02/2019	CCC	6
1	21/02/2019	AAA	23	2	21/02/2019	B	11	3	21/02/2019	CC	51	4	21/02/2019	CCC	54
1	20/02/2019	AAA	3	2	20/02/2019	B	-9	3	20/02/2019	CC	51	4	20/02/2019	CCC	0
1	19/02/2019	AAA	6666	2	19/02/2019	B	6654	3	19/02/2019	CC	51	4	19/02/2019	CCC	3
1	18/02/2019	AAA	5	2	18/02/2019	B	-7	3	18/02/2019	CC	51	4	18/02/2019	CCC	2

<tbody>
</tbody>

I need to fill below table:

Date	21/02/2019
ID	Value 1	Value 2
1
2
3
4

<tbody>
</tbody>

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

 

[ValuationMan]

Are all of the dates columns identical to each other?

 

[00alex00]

Are all of the dates columns identical to each other?

Unfortunately not

 

[jim may]

Do you mean as follows...

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	D	Date	Value 1	Value2	ID	Date	Value 1	Value2	ID	Date	Value 1	Value2	ID	Date	Value 1	Value2
2	1	27/02/2019	AAA	129	2	27/02/2019	B	140	3	27/02/2019	CC	28	4	27/02/2019	CCC	1
3	1	26/02/2019	AAA	239	2	26/02/2019	B	230	3	26/02/2019	CC	48	4	26/02/2019	CCC	65654
4	1	25/02/2019	AAA	34	2	25/02/2019	B	44	3	25/02/2019	CC	29	4	25/02/2019	CCC	39876
5	1	24/02/2019	AAA	56	2	24/02/2019	B	49	3	24/02/2019	CC	46	4	24/02/2019	CCC	234
6	1	23/02/2019	AAA	32	2	23/02/2019	B	20	3	23/02/2019	CC	51	4	23/02/2019	CCC	5
7	1	22/02/2019	AAA	34	2	22/02/2019	B	22	3	22/02/2019	CC	51	4	22/02/2019	CCC	6
8	1	21/02/2019	AAA	23	2	21/02/2019	B	11	3	21/02/2019	CC	51	4	21/02/2019	CCC	54
9	1	20/02/2019	AAA	3	2	20/02/2019	B	-9	3	20/02/2019	CC	51	4	20/02/2019	CCC	0
10	1	19/02/2019	AAA	6666	2	19/02/2019	B	6654	3	19/02/2019	CC	51	4	19/02/2019	CCC	3
11	1	18/02/2019	AAA	5	2	18/02/2019	B	-7	3	18/02/2019	CC	51
12
13
14		Date	21/02/2019
15		ID	Value 1	Value 2
16		1	AAA	23
17		2	B	11
18		3	CC	51
19		4	CCC	54
20

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C16 =INDEX($B$2:$D$11,MATCH($C$14,$B2:$B11,0),2) D16 =INDEX($B$2:$D$11,MATCH($C$14,$B2:$B11,0),3) C17 =INDEX($F$2:$H$11,MATCH($C$14,F2:F11,0),2) D17 =INDEX($F$2:$H$11,MATCH($C$14,$F$2:$F$11,0),3) C18 =INDEX($J$2:$L$11,MATCH($C$14,J2:J11,0),2) D18 =INDEX($J$2:$L$11,MATCH($C$14,$J$2:$J$11,0),3) C19 =INDEX($N$2:$P$11,MATCH($C$14,N2:N11,0),2) D19 =INDEX($N$2:$P$11,MATCH($C$14,$N$2:$N$11,0),3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[00alex00]

Thanks.

This works the only problem that I have to insert a lot of formula since I have more than 100 ids.

Do you think I can create a macro to collect all the info under the same column then create a pivot table?

Thanks

 

[jim may]

Sorry, but right off... No!

 

[ValuationMan]

You could try this. It worked on my computer. Put this in C16. It should work copying and pasting the formula.

=INDEX(OFFSET(C$2:C$11,0,($B16-1)*4),MATCH($C$14,OFFSET($B$2:$B$11,0,($B16-1)*4),0))

 

[ValuationMan]

One modification in case a match isn't found.

=IFERROR(INDEX(OFFSET(C$2:C$11,0,($B16-1)*4),MATCH($C$14,OFFSET($B$2:$B$11,0,($B16-1)*4),0)), "NA")

 

[00alex00]

Sorry, but right off... No!

Thanks you have been really helpful

 

[00alex00]

One modification in case a match isn't found.

=IFERROR(INDEX(OFFSET(C$2:C$11,0,($B16-1)*4),MATCH($C$14,OFFSET($B$2:$B$11,0,($B16-1)*4),0)), "NA")

Wow this is great. Thanks