[Solved] Pivot tables or alternate arrangement

[me231]

i have a ledger that has column headings
rows have entries
column A is an invoice number
the entries are scattered across the column headings
I want the column headings to be the row titles and the values sorted....?
basically the opposite if what i have now

[Matareuz]

You can do it using INDIRECT and ADDRESS functions and archive that easily.

[Villeroy]

I normalized your table and boiled it down to 24 relevant records.

[gpgrego]

Try this, all automated with a macro. Just a Click!

[me231]

wow - thank you all for the homework - I ended up pasting special - but I am going to dig in to the suggestions - thank you

[Villeroy]

I forgot to add a pivot table.
From the most simple list on Sheet3 I made a pivot table (menu:Data>Pivot>Create...). It includes all the sums that you calculated by means of formulas. Storing raw data in pivot tables makes no sense. You store too much non-information in blank cells. However, you can easily derive all kinds of information from a simple 3-column list by means of formulas, filtering, sorting, pivot tables.

[me231]

this is perfect - i could not wrap my head around the "plan" to get it all as I wanted - did not really occur to me to do it multi steps - still not thinking out of the box... thank you again for the help..

[Villeroy]

Sheet2 uses formulas to transpose data from Sheet1 into a simple list with 3 columns.
I copied the resulting values to Sheet3, filtered by value=zero, deleted all rows with that null information and removed the filter which gives the boiled down list.
Then I created the pivot table from the simple list on Sheet3. A pivot table is a table derived from another table aggregating numbers by categories. In this case it calculates the sums for each case number in rows and each category in columns. This works like a database query without any formulas.
You maintain the simple list by simply inserting a row of new cells for each record. The order of records does not matter at all. You can sort and filter the list any way you want or not at all. The pivot table is updated by right-click>Refresh or Data>Pivot>Refresh.
https://wiki.openoffice.org/wiki/Docume ... /DataPilot