Display Dates Absent

[Legacy 400409]

Sheet 1:
P=Presnt
A=Absent

ID	1-Jan-17​	2-Jan-17	3-Jan-17	4-Jan-17	5-Jan-17	6-Jan-17	7-Jan-17	8-Jan-17	9-Jan-17	10-Jan-17
001	P	P	A	P	A	A	A	P	P	A
002	A	P	P	P	P	A	A	P	P	P
003	A	A	A	A	A	P	P	P	P	P
004	A	P	P	P	P	P	P	P	P	P

<tbody>
</tbody>

Sheet 2:

001	1-Jan-17
	6-Jan-17
	7-Jan-17
	10-Jan-17

<tbody>
</tbody>

i wanted help with a formula, once i enter the ID it would give me dates of the person absent (from sheet 1), i have tried hlookup but cant get multiple data, only names and other details i.e each cell having specific hlookup function, can this be done?

 

[sastoka]

Hi there,

From the look of it, you might want to try using a pivot table.

That should cover your need.

Regards.

 

[Legacy 400409]

hi sastoka, i tried pivot but it give me the count of days rather than giving me separated days absent (like a list of each day absent).

 

[Legacy 400409]

hi sastoka, i tried pivot but it give me the count of days rather than giving me separated days absent (like a list of each day absent).

 

[Nishant94]

Unknown
	A	B	C	D	E	F	G	H	I	J	K
2	ID	01-Jan-17	02-Jan-17	03-Jan-17	04-Jan-17	05-Jan-17	06-Jan-17	07-Jan-17	08-Jan-17	09-Jan-17	10-Jan-17
3	001	P	P	A	P	A	A	A	P	P	A
4	002	A	P	P	P	P	A	A	P	P	P
5	003	A	A	A	A	A	P	P	P	P	P
6	004	A	P	P	P	P	P	P	P	P	P
7
8		Option 1	Option 2
9	001	03-Jan-17	03-Jan-17
10		05-Jan-17	05-Jan-17
11		06-Jan-17	06-Jan-17
12		07-Jan-17	07-Jan-17
13		10-Jan-17	10-Jan-17
14
15
16
17
Sheet15

Cell Formulas
Range	Formula
C9	=IFERROR(INDEX($B$2:$K$2,AGGREGATE(15,6,(COLUMN($B$3:$K$3)-COLUMN($B$3)+1)/(INDEX($B$3:$K$6,MATCH($A$9,$A$3:$A$6,0),)="A"),ROWS(C$9:C9))),"")
B9	{=IFERROR(INDEX($B$2:$K$2,SMALL(IF(INDEX($B$3:$K$6,MATCH($A$9,$A$3:$A$6,0),)="A",COLUMN($B$3:$K$3)-COLUMN($B$3)+1),ROWS(B$9:B9))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 400409]

Thanks Nishant the formula worked pefectly