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.

 

[nparsons75]

Hi, thanks for the numbers, I will try again in the morning as I am away from my work PC. How will the function (macro) work, how is it activated? Thanks,

 

[nparsons75]

Hi, I changed the numbers but still get an error. I am getting 'script out of range'. for the line below.

a(Sum) = Cells(j, k)

 

[oldbrewer]

Sub Macro1()
Dim a(100000), b(100000)
For j = 2 To 10000
For k = 65 To 83 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

because you have 10000 rows the dim statements need to be increased
also you had dim statement on same line as macro name

copy this exactly
to run macro tools macro, select it, run
you must be running it to get that error message

 

[nparsons75]

Hi,

Yes I run it but from within the VBA editor. I assume I would have to create a button for this.

The 10000 rows is a number I gave as the table is continually growing, Im not sure how big it will end up so assume 10000. Currently at 4381. Is there a way to tell it to look to the last row?

I just copied as you said and run the macro, this time without error, thanks. (it did take while to run, maybe a minute or two).

Once the macro had run, I did not see anything, where would the results go.

Again, apologies for my lack of understanding I really do appreciate your patience and help.

Also, how does it know what sheet to work with.

UPDATED

After running a second time (changed the 100000 to 10000) it run very quickly. Must be nearly there now...

 

[oldbrewer]

after for j=2 to 1000 put if cells(j,k)="" then goto 999 and label end sub as 999

 

[nparsons75]

This is how I added it, not sure about the label 999

I got an error where highlighted below. I still cant understand where the table of results will go? or should.

Sub Macro1()Dim a(10000), b(10000)
For j = 2 To 10000
[B][COLOR=#ff0000]If Cells(j, k) = "" Then[/COLOR][/B] GoTo 999
For k = 65 To 83 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
999: End Sub

 

[oldbrewer]

delete the : after 999

the error is my fault - the red line should come immediately after for k =65 to 83 step 2

SORRY!!!!

 

[nparsons75]

No problem, appreciate the help.

Ok so I used the following. I run it and get no apparent errors. However, I dont see any results or understand where the new table should show?

Thanks.

Sub Macro1()Dim a(10000), b(10000)
For j = 2 To 10000
For k = 65 To 83 Step 2
If Cells(j, k) = "" Then GoTo 999
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
999 End Sub

 

[oldbrewer]

Sub Macro1()Dim a(10000), b(10000)
For j = 2 To 10000
For k = 65 To 83 Step 2
If Cells(j, k) = "" Then GoTo 999
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(tot, 105) = a(tot) ################
Cells(tot, 106) = b(tot) ##############
Next Z
999 End Sub

my error again
results should appear starting in AD1