Organizing Data

vsimco

New Member
Joined
Feb 3, 2014
Messages
14
This is in Excel 2007

I got a large raw database and want to organize it in a better way

right now the raw data look like
TYPEIDMATERIALTYPEIDQUANTITY
1834256
1835512
1836256
193471000
19359000
1940140
2034386
2036773
2037386
2135290
2137708
2138354
213932

<colgroup><col><col><col></colgroup><tbody>
</tbody>


I would like to organize it in such a fashion

Type ID34353637383940
18
2565122560000
197100090000000140
203860773386000

<tbody>
</tbody>


and so forth

I'm not sure how many different types of columns there are and the the pivot table doesn't like it.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The pivot table seems to give you what you want, except that the 0's in your expected format show up as blanks. isn't the pivot what you want, TYPE ID in row label, MATERIAL TYPE ID in column label and QUANTITY in values as sum
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
Hi vsimco,

With the sample that you've shown I'm able to produce it via Pivot Table as shown below by selecting A1:C14, did you dragTYPEID to [Row Labels], MATERIALTYPEID to [Column Labels] and QUANTITY to [Values]

EFGHIJKLM
1Sum of QUANTITYColumn Labels
2Row Labels34353637383940Grand Total
3182565122561024
41971000900014080140
5203867733861545
621290708354321384
7Grand Total716429802102910943543214084093

<tbody>
</tbody>
 

vsimco

New Member
Joined
Feb 3, 2014
Messages
14
For small subsets of the data the pivot table seems to work fine... Though when I select the whole data set MaterialtypeID in the column label excel gives an error of more than 256 results.

Was just wondering if there was a lazy way to organize the data? (Not necessarily as above with all those zeroes) .
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
Hi vsimco,

Maybe try this and copy the formula across F3:L6?


ABCDEFGHIJKL
1TYPEIDMATERIALTYPEIDQUANTITY
2183425634353637383940
31835512182565122560000
41836256197100090000000140
5193471000203860773386000
6193590002102900708354320
71940140
82034386
92036773
102037386
112135290
122137708
132138354
14213932

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
F3=SUMPRODUCT(($A$2:$A$14=$E3)*($B$2:$B$14=F$2)*$C$2:$C$14)

<tbody>
</tbody>

<tbody>
</tbody>
 

vsimco

New Member
Joined
Feb 3, 2014
Messages
14
Thanks a lot this is basically what I was looking for in terms of making the new data set.. It is quite processor heavy my computer just spent 15 minutes going through the half the data set. But really just need to do it once (I hope).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,467
Members
416,029
Latest member
CSM1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top