Display Dates Absent

[Legacy 400409]

Sheet 1:
P=Presnt
A=Absent

[TABLE="class: grid, width: 640"]
<tbody>[TR]
[TD]ID[/TD]
[TD]

1-Jan-17​

[/TD]
[TD]2-Jan-17
[/TD]
[TD]3-Jan-17[/TD]
[TD]4-Jan-17[/TD]
[TD]5-Jan-17[/TD]
[TD]6-Jan-17[/TD]
[TD]7-Jan-17[/TD]
[TD]8-Jan-17[/TD]
[TD]9-Jan-17[/TD]
[TD]10-Jan-17[/TD]
[/TR]
[TR]
[TD="align: center"]001[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]002[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[/TR]
[TR]
[TD="align: center"]003[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[/TR]
[TR]
[TD="align: center"]004[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]001[/TD]
[TD]1-Jan-17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6-Jan-17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7-Jan-17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10-Jan-17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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