Pivoting Text

**MoreSpeed** · 02-13-2009, 12:34 PM

Afternoon All,

I am trying to create a pivot table but the I need it to return the value of the cell rather than a sum or count.

I have attached a file demonstrating what I need... I am stuck!!

Thanks

**DonkeyOte** · 02-13-2009, 12:42 PM

A PT can't populate the DATA field with Text unfortunately.

Is the relationship of Part No to Xref 1:1 as your example implies or in reality are you looking to concatenate multiple records... ie there may be multiple "Competitor Nos" per Part No. / Xref combination ?

If not you can use an INDEX/MATCH approach but the 1:1 is of critical importance.

**MoreSpeed** · 02-13-2009, 12:44 PM

No, there maybe multiple competitor numbers against any part number

**DonkeyOte** · 02-13-2009, 12:57 PM

In that case I'm afraid you will want/need to utilise a UDF (VBA) -- is that feasible in your environment... ?

The only alternative I can think of would be to create a key which concatenates strings for you...eg:

Please Login or Register  to view this content.

Thus the last record for any combination contains a string of Competitor Nos.

Then the matrix:

Please Login or Register  to view this content.

However aesthetically this may not be ideal and pending length of final string in D it may fail (if string length exceeds 255 chars)

**MoreSpeed** · 02-13-2009, 01:02 PM

I can use basic VBA but wouldn't know where to start with this...

**MoreSpeed** · 02-13-2009, 01:22 PM

I have just checked the data and in reality there can be more than one competitor to any part number but this should not heppen so an INDEX / MATCH formula should work.

Can you help with this?

**DonkeyOte** · 02-13-2009, 01:28 PM

I would advise you adopt the formula approach outlined earlier... see attached (Others may differ mind...

... they normally do!)

IMO if the *real* ranges are vast VBA would be best but you should always adopt that which you are most comfortable with given you must maintain it.