PDA

View Full Version : A macro that adds up prices and sorts them in categories



Mr Blonde
02-07-2014, 08:50 AM
I am pretty much a complete beginner when it comes to VBA in excel, and I apologize in advance. But one has to start somewhere. :)

The thing I want to achieve is a macro that can go through a big list and collect the prices in three different categories. Example:



1 000
2


2 000
1


3 000
3




So imagine this list continues with lots of the prices and numbers as shown above.

I want to summarize all prices in category 1, 2 and, 3.

Did that make any sense?

Thanks :)

ashleyuk1984
02-07-2014, 01:41 PM
Yeah it kinda makes sense, but it's hard to do something with it without actually seeing the data...
Would it be possible for you to give us an example workbook? If your workbook is confidential, then replace the important bits, with random data. Enough so that we can still work from it.
Just 10 rows of data will do, before... and AFTER results.

Then we can program something so that it's easy for you to repeat the process.

Mr Blonde
02-08-2014, 01:44 AM
Sure thing, but I can't until Monday. I'll be back with more then.

snb
02-08-2014, 07:39 AM
Using the builtin Pivottables will do, I bet.

Mr Blonde
02-09-2014, 11:55 PM
OK! So i have some data for you all. Not sure if there is a better way of posting tables from excel, so i just copy paste it in here.

Below i got a random section of my document. The stuff that's interesting is columns E and K. In E we have the prices, and in K we have the category. What I need is something that can identify the categories in column K, and get the prices from column E. When the cell in column K is empty i do not want the price from that column.

We should end up with the total sum for each category. -- And! As a bonus it would be cool to know how many times each category occurred.

Did that make more sense? :)





A
B
C
D
E
F
G
H
I
J
K


1
20110013
beskrivelse
OA
1
942,00
20314
20311
10280
10280
K
2


2
20110013
beskrivelse
OA
7
6 594,00
20314
20311
10280
10280
K
2


3
Totalt 20110013



7 536,00








4
20110014
beskrivelse
OA
192
196 497,00
20314
20311
10280
10280
K
2


5
20110014
beskrivelse
OA
9
8 973,00
20314
20311
10280
10280
K
2


6
20110014
beskrivelse
OA
2
1 994,00
20314
20311
10280
10280
K
2


7
Totalt 20110014



207 464,00








8
20110015
beskrivelse
OA
4,5
4 455,00
20314
20311
10335
10335
P
3


9
20110015
beskrivelse
OA
111
109 890,00
20314
20311
10335
10335
P
3


10
Totalt 20110015



114 345,00








11
20110017
beskrivelse
OA
30
30 600,00
20314
20311
10359
10359
S
1


12
20110017
beskrivelse
OA
1
1 050,00
20314
20311
10359
10359
S
1


13
Totalt 20110017



31 650,00








14
20110075
beskrivelse
OA
30,5
27 755,00
20314
20313
10121
10121
K
2


15
20110075
beskrivelse
OA
0,5
464,00
20314
20313
10121
10121
K
2


16
Totalt 20110075



28 219,00








17
20110078
beskrivelse
OA
31,5
27 720,00
20314
20313
10137
10137
K
2


18
Totalt 20110078



27 720,00








19
20110091-03
beskrivelse
OA
14,5
14 862,50
20314
20313
10280
10280
K
2


20
Totalt 20110091-03



14 862,50








21
20110093
beskrivelse
OA
2
1 840,00
20314
20313
10307
10307
P
3


22
Totalt 20110093



1 840,00








23
20110095
beskrivelse
OA
32,5
29 250,00
20314
20313
10307
10307
P
3


24
20110095
beskrivelse
OA
2
1 920,00
20314
20313
10307
10307
P
3


25
20110095
beskrivelse
OA
40,5
35 437,50
20314
20313
10307
10307
P
3

Mr Blonde
02-10-2014, 12:03 AM
Hah! Pivottables did the trick, never used those before! Although it would still be interesting to see how this is possible in VBA if any would want to give it a go :)

Aussiebear
02-10-2014, 01:01 AM
Sometimes re inventing the wheel can be exciting, but surely not using an already built in feature in Excel is kind of backward thinking....