Can I create a report from this without a macro?

[Ed from AZ]

(XL2007) My data is presented in 5-row groups. Cols A, B, and C have
my identifiers; col AU has a text value I need to include. Headers in
row 9; cells in cols A, B and C are merged in groups of five (A10:A14
merged, A15:19 merged, etc, and same for B and C); in the Name Box,
though, data in the A10:A14 merged group shows up as A10. Col A is
the major group, col B is the sub-group, and col C is the ID number of
the sub-group.

What I would like is something like a pivot table. I tried that,
though, and XL told me I didn't have a valid range, or valid headers,
or something like that. I'd like to see:

Major Group
Sub-Group ID Text
Sub-Group ID Text
Sub-Group ID Text
Major Group
Sub-Group ID Text
Sub-Group ID Text
Sub-Group ID Text
etc.

And of course all alphabetized, just to make it interesting! I
figured a pivot table could do it, but I can't seem to get XL to take
my ranges. So I'm asking if there's a different way without a macro.

Ed

 

[Shane Devenshire]

Hi,

Why don't you show us what your data layout looks like.

 

[Ed from AZ]

Why don't you show us what your data layout looks like.

I'll give it a shot - as close as I can get in a text-based newsgroup,
anyway! (Can't send the file - proprietary info.) It'll probably
look better if you copy and paste into a text editor with a mono-
spaced font.

Col A Col B Col C Cols D-AT Col AU
Row09 Maj Hdr Sub Hdr ID Hdr Header
Row10 Major Grp Sub Grp ID Number Value 1 Text Value
Row11 | Merged | Merged | Merged | Value 2
Row12 | Cells | Cells | Cells | Value 3
Row13 | | | | Value 4
Row14 |__________|__________|__________| Value 5

The Row10-Row14 format is copied down for a couple hundred rows.
The values for Col A and Col B are from data validation drop-downs.
The text value in Col AU is the result of a formula picking values out
of Cols D-AT.

Hope tht's a bit clearer.
Ed

 

[Ed from AZ]

I'll give it a shot - as close as I can get in a text-based newsgroup,
anyway!  (Can't send the file - proprietary info.)  It'll probably
look better if you copy and paste into a text editor with a mono-
spaced font.

Had to change the layout a bit. The merged cells were giving the
Autofilter fits. I'd hoped I could filter on the merged cells and it
would constrain down to the merged cell - which would show me the
whole 5-row group. Not! All I got was the top row of each group. So
I unmerged the cells and used formulas to fill in the other four rows
with the value of the first row; now the Autofilter pulls up the
entire 5-row group.

Anyway, here's the new layout. Again, it looks better in a mono-
spaced font.

Col A Col B Col C Cols D-AT Col AU
Row09 Maj Hdr Sub Hdr ID Hdr Header
Row10 Major Grp Sub Grp ID Number Value 1 Text Value
Row11 Major Grp Sub Grp ID Number Value 2
Row12 Major Grp Sub Grp ID Number Value 3
Row13 Major Grp Sub Grp ID Number Value 4
Row14 Major Grp Sub Grp ID Number Value 5

Cheers!
Ed