Show text in pivot table instead values of 0-1 Without VBA

[afaizan]

Hi Guys,

I am really struggling to just consolidate my data via Pivot table without VBA.

in *DATA* sheet these are the columns

Date

ID

Name

Status

<tbody>
</tbody>

so i made a pivot in *Sheet2*

i want to show in pivot sheet the actual status of an employee Absent/present/other

Here is a link to download sample sheet

https://drive.google.com/open?id=1sVD1pq8SqIgQDnRXUCifuokRtXi7tfpc

currently its showing 1

looking forward a solution of this

thanks

 

[James006]

Hello,

Your requirement for a "Text-Pivot Table" is indeed possible ...

BUT ... you have added a constraint : Without VBA ...

And with this constraint ... it is not possible ...

 

[afaizan]

Hello,

Your requirement for a "Text-Pivot Table" is indeed possible ...

BUT ... you have added a constraint : Without VBA ...

And with this constraint ... it is not possible ...

----------------------------------------------------------------

Thanks James,

but what would be the solution for this? how can i do that?

thanks

 

[Peter_SSs]

If you want to avoid vba then perhaps you could create another table with formulas like below. This could be below your Pivot Table as I have done, or perhaps on another worksheet.
B23 is copied across
A24 is copied down.
B24 is copied across and down.

Excel Workbook
	A	B	C	D	E
2
3	Count of Status	Date
4	Name	2018-03-01	2018-03-04	2018-03-05	2018-03-06
5	Anitha				1
6	Arooj, Yaqub	1	1	1	1
7	Faizan, Akram	1	1	1	1
8	Fouad	1	1	1	1
9	George	1	1	1	1
10	Huzaifa, Vahora	1	1	1	1
11	Jose	1	1	1	1
12	Karishna, Prasad	1	1	1	1
13	Kelly	1	1	1	1
14	Michel	1	1	1	1
15	Nelson	1	1	1	1
16	Saber	1	1	1	1
17	Sajeer	1	1	1	1
18	Stefanie	1	1	1	1
19	Suraj	1	1	1	1
20	Grand Total	14	14	14	15
21
22
23		2018-03-01	2018-03-04	2018-03-05	2018-03-06
24	Anitha				Absent
25	Arooj, Yaqub	Present	IN	Present	Present
26	Faizan, Akram	Present	Present	Present	Present
27	Fouad	Present	Present	Present	Present
28	George	Present	Present	Present	Present
29	Huzaifa, Vahora	Present	Present	Present	Present
30	Jose	IN	Present	Present	Present
Sheet2

 

[afaizan]

If you want to avoid vba then perhaps you could create another table with formulas like below. This could be below your Pivot Table as I have done, or perhaps on another worksheet.
B23 is copied across
A24 is copied down.
B24 is copied across and down.

Sheet2

	A	B	C	D	E
2
3	Count of Status	Date
4	Name	2018-03-01	2018-03-04	2018-03-05	2018-03-06
5	Anitha				1
6	Arooj, Yaqub	1	1	1	1
7	Faizan, Akram	1	1	1	1
8	Fouad	1	1	1	1
9	George	1	1	1	1
10	Huzaifa, Vahora	1	1	1	1
11	Jose	1	1	1	1
12	Karishna, Prasad	1	1	1	1
13	Kelly	1	1	1	1
14	Michel	1	1	1	1
15	Nelson	1	1	1	1
16	Saber	1	1	1	1
17	Sajeer	1	1	1	1
18	Stefanie	1	1	1	1
19	Suraj	1	1	1	1
20	Grand Total	14	14	14	15
21
22
23		2018-03-01	2018-03-04	2018-03-05	2018-03-06
24	Anitha				Absent
25	Arooj, Yaqub	Present	IN	Present	Present
26	Faizan, Akram	Present	Present	Present	Present
27	Fouad	Present	Present	Present	Present
28	George	Present	Present	Present	Present
29	Huzaifa, Vahora	Present	Present	Present	Present
30	Jose	IN	Present	Present	Present

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:127px;"><col style="width:96px;"><col style="width:96px;"><col style="width:96px;"><col style="width:96px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B23 =B4 A24 =A5 B24 =IFERROR(INDEX(Data!$D$8:$D$293,MATCH(B$4&$A24,INDEX(Data!$A$8:$A$293&Data!$C$8:$C$293,0),0)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

--------------------------------------------------

Thanks very much Mr. Peter.

its done..

Thank you

 

[Learn Excel in Tamil]

Hi afaizan,

if you want count of the data's it's possible via privot table, however for text is not possible as per my knowledge, since the value field has been used to calculate the values only, if you want summary report using function or Macro, that will be possible,

Thanks and please provide an information

Regard,
Learn excel in tamil

 

[afaizan]

Thanks,

Learn Excel in Tamil

But Mr. Peter_SSs solved my question.

 

[Peter_SSs]

Thanks very much Mr. Peter.

its done..

Thank you

You are very welcome.

BTW, for the future, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.