Can I create a report from this without a macro?

(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
0
prof_ofwhat (194)
6/9/2009 12:02:00 AM
excel 39879 articles. 2 followers. Follow

3 Replies
599 Views

Similar Articles

[PageSpeed] 14

Hi,

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

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ed from AZ" wrote:

> (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
> 
0
6/9/2009 5:14:01 AM
On Jun 8, 10:14=A0pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> 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
0
prof_ofwhat (194)
6/9/2009 1:56:18 PM
On Jun 9, 6:56=A0am, Ed from AZ <prof_ofw...@yahoo.com> wrote:
> On Jun 8, 10:14=A0pm, Shane Devenshire
>
> <ShaneDevensh...@discussions.microsoft.com> wrote:
> > 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! =A0(Can't send the file - proprietary info.) =A0It'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
0
prof_ofwhat (194)
6/9/2009 4:02:47 PM
Reply:

Similar Artilces:

How do I create a Range Name?
I want to create different ranges of cells to capture the content and duplicate on another sheet or workbook using a macro. Just select the range of cells, hit Ctrl-F3, and add a name. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curious" <Curious@discussions.microsoft.com> wrote in message news:08E2FBF4-5142-4844-B680-41E43AFA8EFD@microsoft.com... >I want to create different ranges of cells to capture the content and > duplicate on another sheet or workbook using a macro. or, select the cells and name in t...

How to know when a window is created?
I want to create a CFormview derived application. I want to call GetClientRect() when the formview is created, but I can't find the message of handling this. If I call GetClientRect() in OnInitialUpdate(), I can't get the correct size I want. Thank you. zhaoyandong wrote: > I want to create a CFormview derived application. > > I want to call GetClientRect() when the formview is created, but I can't >find the message of handling this. > > If I call GetClientRect() in OnInitialUpdate(), I can't get the correct >size I want. Whenever the window ...

Ms Money hangs when generately monthly reports
I am using Microsoft Money Plus Deluxe Trial Version 17.0.120.727, and everytime I generate monthly reports under the Reports Tab I will encounter the following error and MS money will hang & close. Any idea what's wrong with ms money? Error message:- Runtime Error! Program: ... gram Files\Microsoft Money Plus\MNYCoreFiles\msmoney.exe This application has requested the Runtime to terminte it in an unusual way Please contact the application's support team for more information ...

Access 2007 Report-How ot change start of week 2 Monday 4 weekly g
Hello, Making a report in Access 2007 and I am grouping the data by week. Access starts the week on a Sunday. Can i change it to Monday for this Report? Thanks! On Fri, 29 Feb 2008 09:08:01 -0800, Luc Barrette wrote: > Hello, > > Making a report in Access 2007 and I am grouping the data by week. Access > starts the week on a Sunday. Can i change it to Monday for this Report? > > Thanks! Asked and answered yesterday. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Thanks Fred but I could find the control that you were refering to. C...

Can't find CD/DVDdrive
My DVD drives have disappeared. they don't show as hardware in control panel. Working fine yesterday - no software changes made. Both have power to them. I've unplugged everything and put a new lead from them to the motherboard. Doesn't appear to be anything physical. Followed various posts on this site. Mr Fixit recognises the problem but can't fix it. Went into registry. Higher filters there but no lower filters. Any suggestions? On 06/20/2010 05:13 AM, Philx5 wrote: > My DVD drives have disappeared. they don't show as hardware in control panel. ...

CPropertySheet create failing when adding to dialog box
Hi all, I am having problems with a class that I made that inherits from CPropertySheet. I need to add this class to a dialog box with the following code: m_appPropSheet.AddPage(m_saleIllustrationPropPageClientOne); m_appPropSheet.AddPage(m_saleIllustrationPropPageClientTwo); m_appPropSheet.Create(this, WS_CHILD | WS_VISIBLE, 0); m_appPropSheet.EnableStackedTabs(FALSE); m_appPropSheet.ModifyStyleEx (0, WS_EX_CONTROLPARENT); m_appPropSheet->ModifyStyle(0, WS_TABSTOP); m_appPropSheet.SetWindowPos(NULL, 0, 25, 0, 0, SWP_NOZORDER | SWP_NOSIZE | SWP_NOACTIVATE); ...

Viewing documents without publisher
I produce newsletters in Publisher. But I need a viewer to send to people who dont have publisher on their PCs. Seems that viewers are available for other office packages except Publisher any suggestions No such animal. Most folks create a pdf file and mail it out as an attachment. Not only is there a free reader (Adobe Reader) but the file size is much smaller than what Publisher creates. A couple of pdf producing programs are: PDF-XChange - No 1 for small file size - modest price Free trial at: http://www.docu-track.com/ Primopdf - free http://www.primopdf.com -- Don Vancouver, US...

Can't display all text in cell
I'm trying to display text in a cell. I've got Wrap Text on, and I hav several paragraphs of text. All of it appears in the formula bar, bu not in the cell displayed on the screen. And yes, I increased the ro height, but it still doesn't show. Any ideas? Thank -- Message posted from http://www.ExcelForum.com I am having the same problem. Apparantly there is a 1024 character limitation per cel in excel. The only advice I have received so far, which does work, is to press alt+enter to create a line break. If you find another work around, I would be interested. Hi besid...

Insert Macro
I've just seen a macro that I'd like to add into my current macro. Do I simply copy and paste to the bottom of my current macro? Thanks! If you're lucky, that would work. You may have to adjust variable names or even declare variable. Another way is to keep that macro as a different subroutine and just call it: sub yourexistingmacro() 'all your existing code call yournewmacro end sub sub yournewmacro() 'all that newstuff end sub Change "yournewmacro" to the correct name in both spots. And save your workbook before you test it. If it blows up real good...

Macro to create a new chart
I've been using this code for a while to easily create a new chart. However just recently has stopped working. It now created a "default" excel chart as a new tab in the workbook. Any suggestions? '******************************************************************** 'Creates 2 data series horizontal bar chart '******************************************************************** Dim myShtName As String myShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlBarClustered 'Creates Stacked bar ActiveChart.SetSourceData Source:=Rng, ...

Creating a summary page without blank lines
I have a project where I need to determine if the billing for supplemental charges for employees matches what is actually deducted from the employee's pay check. I just started this position and what I am working with is a separate spreadsheet for each month's bill. What I have done so far is to create a spreadsheet with a tab for each month that just has the supplemental information being billed for in the left most columns and what was deducted from each paycheck in the columns to the right, a tab that lists employee names and employee numbers, and a summary page that lists...

How can you enable a combobox to be "tabbed" or entered out of?
I am trying to allow users to tab in to a combobox and then tab/enter out after a selection is made. Right now you have to use the mouse to enter the combobox (tabbing goes to the cell underneath it) and after you make a selection, its like you're stuck in the box - you can only get out by using the mouse or pressing escape (which then brings you to the last cell you were in). Is there a way to fix this? ...

how to create row with data
imagine i have two rows: name and color: mary blue ken brown will blue what i want is having, in another row, just the names that have blue eyes, like mary will thank you On Mon, 11 May 2009 17:26:04 -0700, rafael <rafael@discussions.microsoft.com> wrote: >imagine i have two rows: name and color: >mary blue >ken brown >will blue > >what i want is having, in another row, just the names that have blue eyes, >like >mary >will Like: mary will or like: mary will ???? ty i have excel 2000 what i want is all names in a single row, wi...

Distribute macro to users
I have written a macro for a spreadsheet, chicago.xls. Let's call the macro testmacro. The users would like to be able to run the same macro for other spreadsheets (eg. denver.xls, dallas.xls, NewYork.xls, boston.xls, miami.xls etc..). So they'll open the spreadsheet, press a button and get the desired result. I have read some of the threads on putting a button on a floating toolbar. Debra Dalgleish's info is helpful but I'm just getting very confused. How can I give testmacro to the users so they can just press a button to run the macro? I am using Excel V...

Macro Security #3
In the process of building an Excel spreadsheet, I added a couple of macros. (Mistake #1, I know.) I don't really need the macros, so I deleted them and saved the file. But now when I go to re-open the file, I get that annoying little "Macros may contain viruses, it is always safe to disable macros, but if the macros are legitimate, blah, blah, blah..." dialog box. (There are no macros listed in the Macro dialog box when I do the ol' Alt+F8.) I know I can reset my security level to high and not have to deal with this, but I don't want to do that; I want Excel to recogn...

GP8 Security and Modified report
Hi there this is my problem We have this bunch of modified report for different departement here With of course different security for each of them So far, the initial security has been apllied with the standard security from an external consultant. He found it easier to work with that He told me a while ago the only problem is you cannot do it by groups,,, user only So I openned "Advance security" a choose "view by alternate modified and custom". Now what I see is not the same as standard security User for a particular report that doesn't have access ...

How can I customize the right-click context menu in Word 2007
I use cut & paste of imported text a lot in both Word 2007 and Excel 2007. In Excel the right-click context menu includes Paste Special; in Word it does not. How can I add Paste Special to the Word context menu? See http://gregmaxey.mvps.org/Customize_Word2007_SC_Menu_Programatically.htm -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "Steve Samson" <SteveSamson@discussions.microsoft.com> wrote in message news:A20FD91E-CFE2-402A-8A54-9F58880A3126@micros...

I can't see the image picture from the manager program
Using the Manager - Item Properties - Special Tab You can select the picture files that reside in the associated folder that is set by the Adminastrator - paths - pictures - folder location. The files sit on the backoffice PC and I can see the selected picture in the little window, via the Manager program no problem. At the Inventory PC, I set the Admin program to look at the server and file paths are set. I can see the picture files and select them but no image shows up in the little window. Anybody got any ideas what is causing this? Moe Register is looking local for photos, needs to ...

Can I use Rule to move digitally signed messages to separate folder?
Is there a way to move digitally signed messages to a separate folder using a Rule? Other options for segregating digitally signed messages? Thanks in advance. deko <nospam@hotmail.com> wrote: > Is there a way to move digitally signed messages to a separate folder > using a Rule? Other options for segregating digitally signed > messages? I don't see that as a selection criterion in the Rules wizard. -- Brian Tillman ...

Modify Protection Macro to allow formatting?
Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub How do I modify to allow users to format cells? Only been 4 minutes since your first post. Have a little patience. See reply at that post. Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 11:14:02 -0800, Visakha <Visakha@discussions.microsoft...

MSCRM Reports Export format
Does anyone know if there is a way to export reports as Rich Text Format (*.rtf)? or exporting to a CSV? We have a customer that still uses Wordperfect, and exporting to an Excel or Word Doc may not be an option. ...

Crystal Report password tool
what is error 404? I have a problem to change the password. Please help... Thanks 404 is page not found -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Francis" <Francis@discussions.microsoft.com> wrote in message news:CAA3BBAD-29D7-4815-999C-3976D19548DE@microsoft.com... > what is error 404? I have a problem to change the password. Please help... > Thanks ...

Can't send large attachments
I can't seem to find the place in Outlook to change the size of outgoing attachments. My ISP has a 10mb limit but Outlook seems to balk at anything larger than 2mb. Anyone? -- Nadyne Nelson nadyne@prospectiveplanning.com Nadyne on MSN Messenger Cable/DSL or dialup? If Cable/DSL, are you behind a router (Linksys) that lets you share a single IP address with many computers? "Nadyne Nelson" <nadyne@verizon.net> wrote in message news:Sizgc.19499$G_.13360@nwrddc02.gnilink.net... > > I can't seem to find the place in Outlook to change the size of ...

A print macro?
Hello. I am using Excel 2000. I have made a spreadsheet that will allow me to produce W2 forms. It works great, but I need some help with the printing. On one worksheet I have the information so that i can produce 100 W2's. I have 2 per sheet just like the real form. When I print preview I have a total of 50 printable pages. What I would like to do is to have a dialog box that I could type in page 1, page 2, etc. What my plan is to print 1 page at a time. I have to print 6 copies of each W2. I would like to type in a dialog box etc. "page 1" and have the macro know that I wa...

How can I access a file in Outlook Public Folder by VBscript
It does not have to be VBscript.. I need sync the file with my local harddrive... Thanks. Nix wrote: > It does not have to be VBscript.. > I need sync the file with my local harddrive... > > Thanks. Why does it need to be a script at all? You can add your public folder to your PF\Favorites (drag it there) and include it in your send/receive settings for offline use with your OST file. ...