multiple columns in a pivot table

[nparsons75]

Hi,

I have a table of contents that I need to create a pivot table for. The issue is that for each row of data in the table i need to record a quantity against multiple part types. Please see example table below.

PART	QTY	PART	QTY	PART	QTY	PART	QTY	PART	QTY
001	10	002	12	003	1
004	9	005	6	002	4	001	2

<tbody>
</tbody>

So, from a pivot table I need to be able to find the overall total quantity per each part type. The result of the pivot table will be hopefully like this.

PART	QTY
001	12
002	16
003	1
004	9
005	6
006	0
007	0

<tbody>
</tbody>

Really hope this is possible, thanks in advance for any help.

 

[oldbrewer]

I would write a macro that puts parts in column A and qtys in col B, then a basic pivot table will suffice.

 

[nparsons75]

That sounds great, however I would not have a clue how to write a macro like that...well above my level...

I can edit VBA and paste code in the right places etc but wouldn't know how to write from scratch.

 

[oldbrewer]

PART	QTY	PART	QTY	PART	QTY	PART	QTY	PART	QTY
1	10	2	12	3	1
4	9	5	6	2	4	1	2
part	qty
1	10					this macro produces the new table
2	12
3	1					Dim a(100), b(100)
4	9					For j = 2 To 3
5	6					For k = 1 To 9 Step 2
2	4					If Cells(j, k) <> "" Then GoTo 20 Else GoTo 100
1	2					20 Sum = Sum + 1
						a(Sum) = Cells(j, k)
						b(Sum) = Cells(j, k + 1)
						100 Next k
						Next j
						For z = 10 To 10 + Sum
						tot = tot + 1
						Cells(z, 1) = a(tot)
						Cells(z, 2) = b(tot)
						Next z
						End Sub
		Sum of qty
		part	Total
		1	12
		2	16			then a pivot table for the totals for each part
		3	1
		4	9
		5	6
		Grand Total	44

<colgroup><col span="2"><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[nparsons75]

wow, thank you so much, where do i add this macro? would it be in a module of its on , this workbook or the sheet where the pivots are?

 

[oldbrewer]

the simple way is tools record macro, select A1, stop macro
then tools macro edit macro delete the single row starting Range and paste in my code, delete one of the end subs

 

[nparsons75]

Hi, I have recorded the macro and saved. Im not sure how the macro relates to the table etc.

this is the macro

Sub Macro1()
Dim a(100), b(100)
For j = 2 To 3
For k = 1 To 9 Step 2
If Cells(j, k) <> "" Then GoTo 20 Else GoTo 100
20 Sum = Sum + 1
a(Sum) = Cells(j, k)
b(Sum) = Cells(j, k + 1)
100 Next k
Next j
For Z = 10 To 10 + Sum
tot = tot + 1
Cells(Z, 1) = a(tot)
Cells(Z, 2) = b(tot)
Next Z
End Sub

 

[oldbrewer]

j=2 to 3 means it looks at row 2 first then row 3
k=1 to 9 step 2 means it looks at cols 1,3,5,7,9
check if cell is blank if it is set j to 3 and run through again
the z loop prints out the values starting in row 10

run it

if you have say 5 rows make it for j= 2 to 6
if you have 9 columns make it for k=1 to 17 step 2

if you want to start on row 15 make it for z=15 to 15 +sum

 

[nparsons75]

Hi oldbrewer, thank you very much or the explanation, I am trying but seems to be struggling. My spreadsheet contains a lot of data. For your macro it will start on row 2 and will continue for many rows, 10,000 maybe. The columns, these will start at column BM through to CF (65 to 84).

I have edited the macro to suit (I think) see below, however it stops on line a(sum) = cells (j,k)

Also, how does i know where to paste the new table. Maybe I am misreading the instruction..

sorry about this. and thank very much for our patience.

Sub Macro1()Dim a(100), b(100)
For j = 2 To 10000
For k = 65 To 84 Step 2
If Cells(j, k) <> "" Then GoTo 20 Else GoTo 100
20 Sum = Sum + 1
a(Sum) = Cells(j, k)
b(Sum) = Cells(j, k + 1)
100 Next k
Next j
For Z = 2 To 2 + Sum
tot = tot + 1
Cells(Z, 1) = a(tot)
Cells(Z, 2) = b(tot)
Next Z
End Sub

 

[oldbrewer]

it is 65 to 83 step 2
I will look at it in an hour if that fix does not help