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.


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,

         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


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.


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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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