Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have report in Excel format (Excel 2007) from Accountant department - and it has Groupping by Rows.

+ Client 1   300$  (group Bills by Client)
|-- BIll 1   100$
|-- Bill 2   200$

So in Excel It looks like this in plain rows format (If I ungroup those rows):

1 Client1 300$
2 Bill1  100$
3 Bill2  200$

1,2,3 - row numbers.

So I cant Pivot these data to get Client-by-Bill-SUm report, because rows with Client Name are not Connected (that is necessary for Pivoting info by Client, Bills) with Bills rows after UnGroupping.

share|improve this question

2 Answers 2

What you need to do is "flatten" your data and add column headers so your example would look like this...

CLIENT Bill#   Amount
Client1 Bill1  100$
Client1 Bill2  200$

How you do this depends on how much data you have, and how variable the number of bill per client is.

Your first step is to getting the client name added to each Bill line would be indent (add a cell before) each Bill data line,

 Client1 
         Bill1  100$
         Bill2  200$

then use a formula to copy down the Client name

 Client1 300$
 Client1 Bill1  100$
 Client1 Bill2  200$

This will take a little playing with - and as I say, it all depends on your data

share|improve this answer

Not sure I understand the specifics, but I believe that blaze might be useful to you. You may be able to use it to help automate the manual method of your task.

Blaze

is able to automate the unique recurrent tasks.

will learn by itself that you are preforming a repetitive task and spontaneously offer to help you.

share|improve this answer
    
How it could help? I have Excel SpreadSheet with Groupped Rows and I need "to extract" data in UnGroupped format, but saving the "structure" - So Bill, Client, Sum should be available. So that I could later analyze those ungroupped data via PivotTable. I cant understand How I could Automate conversion from Groupped data into "plain". –  zmische Dec 9 '09 at 7:59
    
Fair enough, just an idea. Good luck. –  outsideblasts Dec 9 '09 at 8:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.