A macro that adds up prices and sorts them in categories

[Mr Blonde]

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]

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]

Sure thing, but I can't until Monday. I'll be back with more then.

[snb]

Using the builtin Pivottables will do, I bet.

[Mr Blonde]

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]

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]

Sometimes re inventing the wheel can be exciting, but surely not using an already built in feature in Excel is kind of backward thinking....