Transform a table to use it in a pivottable

[Joel Horowitz]

Hye,

I sometimes have raw data in a format that does not make it easy to feed into a pivottable. For instance, I have a table like this

Date Prod1 Prod2 Prod3 Prod4
1/1 343 545 242 543
2/1 432 542 143 656
... ... ...

What I would like is a format like

Date Productname Sales
1/1 Prod1 343
1/1 Prod2 545
1/1 Prod3 242
1/1 Prod4 543
2/1 Prod1 432
... ... ...

In that particular instance, I can do it, via the mod() function and vlookup. However, sometimes the layout of the table is a little different, and I need to rethink how to program it. Is there a macro that can do it in a flexible way?

Thanks

 

[PaddyD]

To do it 'flexibly', the macro would have to be equally 'flexible', and that means programming in a lot of different scenarios, loads of error checking etc. I do't know of anything like it in the various addins out there, and I suspect it would be a very large task to build macro / wizard to do the job.

As ever, I'll wait to be proved wrong on this, but I don't think it's the sort of thing you'll get all in one go from a board. Maybe if you were trying to write one & had specific problems on the way...

Paddy

 

[Joel Horowitz]

It looks like there's an easy way around it!

http://j-walk.com/ss/excel/usertips/tip068.htm

It needs a little tweaking if the first field has not unique values, but it's a very quick way to solve my problem

 

[royUK]

hi

I think these demo (www.excel-vba-access.com/excel-tips-reporting.htm)on the sumproduct function might help. If not it is still useful and worth checking out.
This message was edited by royUK on 2002-08-30 02:19
This message was edited by royUK on 2002-08-30 02:19