Reverse Crosstab Query in Excel 4

[DeLaMartre]

This is a bit off-the-wall, but does anyone have any suggestions as to how to create a "reverse crosstab query" in Excel, whereby the values in a crosstab-style table are put into a standard database field format?

Any help would be greatly appreciated!

Thanks very much,


-Bob in California

 

[SkipVought]

Bob in California,

This is Skip in Texas! ;-)

Start the PT Wizard using the crosstab report as the data source.

Select Multiple Consolidation Ranges.

Select I will create the Page Fields.

Select and add the Corsstab range.

In the Layout, drag the ROW 7 COLUMN buttons OFF the Layout.

Finish.

You will see a 4-cell PT. Double click the total VALUE.

VOLA!

BTW, you may need to manipulate parts of your source if you have what would be multiple ROWS or multiple COLUMNS.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[GlennUK]

ooh, I like that. Have a star from me

Cheers, Glenn.

 

[SkipVought]

Thanx, Glenn from the United Kingdom!

Appreciated Lady Thatcher's remarks last week!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DeLaMartre]

Brilliant Solution, Skip-in-Texas!

Thanks so much for your time and expertise!

-Bob in California

 

[DeLaMartre]

Skip,

I was just working with your solution to this challenge some more, and I just have to thank you again. Fantastic!

Best Regards,


-Bob in California

 

[SkipVought]

Thanks! Glad to help! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

Just as an addendum, if your table were to look like the following:-

A B C D E F G H I
1 aa bb cc dd Jan Feb Mar Apr May etc
2 xx xx xx xx yy yy yy yy yy
3 xx xx xx xx yy yy yy yy yy
4 xx xx xx xx yy yy yy yy yy
6 xx xx xx xx yy yy yy yy yy
6 xx xx xx xx yy yy yy yy yy

where you have multiple fields to the left of your table, you can still accommodate this by inserting a helper column to the left of the Jan column above, using concatention with say a % to delimit the data, eg:-

=A2&"%"&B2&"%"&C2&"%"&D2

Copy / paste as values Col E with your concatenation, and then delete Cols A

Now use the method Skip outlined above, and then just use Data / text To columns / Delimited / with % as the 'other' delimiter.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DeLaMartre]

Thanks, Ken; You always have a way of adding value. A Star to you, as well.

Regards,


-Bob in California

 

[SkipVought]

Ken,

Great "added value" suggestion!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

You're welcome, and thank you. I use both of these a hell of a lot, and have found this to be a huge timesaver sometimes

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------