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:

Item #	MFG	MFG Part #
123456	ABC	OAIHP8
123456	XYZ	QEQEU
123456	AINS	IQFLCV

<tbody>
</tbody>

I would like my pivot table to look like this:

Item#	ABC	XYZ	AINS
123456	OAIHP8	QEQEU	IQFLCV

<tbody>
</tbody>

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