Use index and match function to get data from pivot table

[john-lau]

Hello

I receive the pivot table from other department regarding student's activitie
performance. Some of students joint same activities , some of them only joi
on
activity. There are 1000 students in file. I only require 200 students.
don'
have the source files. There are no grouping/ similar characteristics, so
can't use grouping of pivot table to select those 200 students

The format as followin

RM Name Campaign Name % of unattend % of bad performance % of positiv
commen
Dick Lau 99999* Anti-Smoke 0 50 9
Dick Lau 99999* Communication Skill 10 60 8
Dick Lau 99999* interpersonal skill 11 55 7
Dick Lau 99999* Play group 20 60 6
Gordon Lau 99999* Anti-Smoke 0 23 10
Gordon Lau 99999* Communication Skill 11 32 9
Gordon Lau 99999* interpersonal skill 4 32 6
Gordon Lau 99999* Play group 5 55 4
Gordon Lau 99999* leadership 10 55 2

I just need students following 5 data
"Sum of % of unatten
(all activities)

"Sum of % of bad performanc
(all activities)

"Sum of % o
positive comment (anti smoke)

"Sum of % of positive commen
(play group)

"Sum of % of positive commen
(interpersonal skills)

I wouuld like to use index and match function/ VBA. It seems that formul
function is possible. I receive this file every week, there may be ne
activities in the later week. Therefore, it is much better to use formul
function. If using VBA, after they add new activities, the macro may b
requir
to modified

I have the excel file for reference. However, I tried several times, I stil
can't upload the file in the forum. I use firefox browser.

 

[Jim Cone]

If you can sort the names, you should be able to use 'Subtotals' found on the Data menu.
Once the data is subtotaled, you can select/copy/paste the visible cells to another sheet.
--
Jim Cone
Portland, Oregon USA .
http://www.contextures.com/excel-sort-addin.html .
Independent review of Special Sort excel add-in (30 ways to sort)



"john-lau" <[email protected]>
wrote in message
news[email protected]...