Pivot Table: Ways to return only the value - no calculation?

[Newb123]

Hello and thanks in advance for your help

I have a spread sheet that looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item #[/TD]
[TD]MFG[/TD]
[TD]MFG Part #[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]ABC[/TD]
[TD]OAIHP8[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]XYZ[/TD]
[TD]QEQEU[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]AINS[/TD]
[TD]IQFLCV[/TD]
[/TR]
</tbody>[/TABLE]
I would like my pivot table to look like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]ABC[/TD]
[TD]XYZ[/TD]
[TD]AINS[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]OAIHP8[/TD]
[TD]QEQEU[/TD]
[TD]IQFLCV[/TD]
[/TR]
</tbody>[/TABLE]

The layout is not the problem. The values in the 'value grid' should not be calculated but strictly return the actual value or part number. Not COUNT, SUM etc... However, I cant find a way to do this.

Has anyone got any suggestions?

Regards

Bert

 

[Oeldere]

to my knowledge that is not possible with an pivot table.

you could use Vlookup to get the values that way.

 

[tbobolz]

I do not know if you pivot table is a requirement or not; however, monthly need to resort my data in this cube format and I record a maco with Relative References enabled on the Developers tab to restructure the data as it appears in your example. Then include a loop to stop at the end of your data.

 

[Fazza]

Hello, Bert

A pivot table doesn't return text in the data field, just things like COUNT or SUM (of numbers).

A cross-tab report can return text, as it can use MAX or MIN functions that work with text. So maybe an external data query returning a cross-tab report is OK for you?

The SQL could be

TRANSFORM MAX(MFG Part #]
SELECT [Item#]
FROM your_table
GROUP BY [Item #]
PIVOT MFG

I'll post again shortly with links to similar old threads that have further details. regards, Fazza

 

[Fazza]

I didn't find an old forum example that spells out the steps - but there are a few I've posted in the past. I'm short of time today, sorry. This was the best I found. If you want I can look again tomorrow. http://www.mrexcel.com/forum/excel-questions/384664-can-i-return-text-pivot-table.html