Create a list from Pivot table field rows

**sayeamans** · 12-03-2009, 11:51 PM

I have data in two columns, name and skill. By using a pivot table I can set both name and skill as row fields and name as data field to get a list of employees by skill, listing employee name once.

My problem is how to dynamically create a list from the pivot table that I can automaticaly use in creating reports. I don't want to pivot table, I just want to reference the unique list of employee names by skill.

here is sample data on sheet 1, the pivot table on sheet 2, and the desired table I want to automatically build on sheet 3.

**JeanRage** · 12-04-2009, 04:15 AM

Hi,

It looks as if your constraint is no VBA ...

Attached is a sample with a couple of functions, as a starting point ...

HTH

**DonkeyOte** · 12-04-2009, 04:25 AM

I think realistically VBA is the best route, however, if as JR states you don't want to utilise then one route would be:

Sheet2
G11: =IF(D11="","",IF(C11<>"",D11,G10&CHAR(10)&D11))
copied down for all rows

Then utilise the above column to get your results on sheet3

Sheet3
D12: =INDEX(Sheet2!$G:$G,MATCH($C12&" Total",Sheet2!$C:$C,0)-1)
copied down
(ensuring D12 onwards is set to Wrap Text)

Note: the above assumes sub totals though is meant purely as proof of concept - it can be modified as required.