Pivot Table Question

[Dave S]

Pivot table looks like the following, created from a source general ledger.
I need to have the account number, Description and the total in just on row.

Any ideas ? Please help.

Thank you.

Dave


Account Description Total
1001 500
Cash 500
2001 1000
Payables 1000
3001 700
Equity 700

 

[dan_l]

can you post a little better sample data?

 

[NARAYANK991]

Hi Dave ,

What is your raw data like ?

Suppose it is as shown below ?

Account    Description    Total
1001            Cash            500
2001            Payables    1000
3001            Equity          700
2001            Payables    1000
3001            Equity          700
1001            Cash            500
2001            Payables    1000
3001            Equity          700
2001            Payables    1000
2001            Payables    1000
3001            Equity          700
2001            Payables    1000
3001            Equity          700
1001            Cash            500

The the pivot table can look like this , provided you select Show Report in Tabular Form :

Account    Description    Totals
1001            Cash          1500
2001            Payables     6000
3001            Equity        3500
Grand Total                  11000

Narayan

 

[dan_l]

Depending on your needs (and I'm assuming that you have more than 3 accounts

Also, with Narayan's method, get rid of sub totals.

You can also just create a helper column in your source data like "concatenate([account]&" - " & [ Description])"

 

[Dave S]

Hi NarayanK,

Thank you for your suggestion. I will try. I created a sample worksheet in excel, copied and pasted here for you all to see. But the column arrangements were messed up here. I have several accounts.

I do like to see the pivot output just like the way your 1st table shows up here. In my pivot table output, account number is in Column A and account total is in Column C. Then Account Description is in next row Column B and Description total is in Column C. Total for the Account and Description are the same information. This is because of the source file is set up that way.

Therefore, I just want Account Number, Account Description and Total in one row just as the way you are showing. say like, A2 B2 and C2.

It will be nice if I can show you the information in columns. But I guess this page doesn't show tables the way it supposed to look.

Thank you,

Note: Thanks Dan

 

[Dave S]

Depending on your needs (and I'm assuming that you have more than 3 accounts

Also, with Narayan's method, get rid of sub totals.

You can also just create a helper column in your source data like "concatenate([account]&" - " & [ Description])"

Thanks Dan,

But sub totals has Account description line. But I should try this.

Thank you

Dave

 

[Dave S]

Hi Dave ,

What is your raw data like ?

Suppose it is as shown below ?

Account    Description    Total
1001            Cash            500
2001            Payables    1000
3001            Equity          700
2001            Payables    1000
3001            Equity          700
1001            Cash            500
2001            Payables    1000
3001            Equity          700
2001            Payables    1000
2001            Payables    1000
3001            Equity          700
2001            Payables    1000
3001            Equity          700
1001            Cash            500

The the pivot table can look like this , provided you select Show Report in Tabular Form :

Account    Description    Totals
1001            Cash          1500
2001            Payables    6000
3001            Equity        3500
Grand Total                  11000

Narayan

 

[Dave S]

Ok,

I guess I figured it out.

I would be excited to learn more formulas that can achieve this.

But in my worksheet. I just filtered account descriptions and copied to blank cells against account numbers and then get rid of Subtotals like Dan suggested.

Thank you guys.

Dave