How to summarise multiple entries of data using VB code?

[Sebastian]

The spreadsheet shows a number of entries of various products. Some of the products have multiple entries.

Bulletin Board stuff.xls
	A	B	C	D	E	F	G
1	DATE	PARTNUMBER	PARTNAME	CUSTOMER	QUANTITY	$Price	$VALUE
2	30/08/02	2683	Product1	Customer1	1,380	$0.6146	$848.15
3	05/08/02	20072	Product2	Customer2	88	$0.7377	$64.92
4	30/08/02	20072	Product2	Customer2	715	$0.7377	$527.46
5	05/08/02	20073	Product3	Customer2	563	$0.7046	$396.69
6	15/08/02	2290313	Product4	Customer3	1	$7.1633	$7.16
7	15/08/02	2290315	Product5	Customer3	3	$4.7733	$14.32
8	15/08/02	2290313	Product4	Customer3	17	$7.1633	$121.78
9	26/08/02	6200142	Product6	Customer3	26	$2.7602	$71.77
10	26/08/02	2290313	Product4	Customer3	4	$7.1633	$28.65
11	26/08/02	6200154	Product7	Customer3	2	$9.9926	$19.99
12	26/08/02	2290315	Product5	Customer3	21	$4.7733	$100.24
13	31/07/02	0038-400-565	Product8	Customer4	40	$10.1076	$404.30
14	14/08/02	20072	Product2	Customer2	543	$0.7377	$400.57
15	26/08/02	20073	Product3	Customer2	138	$0.7046	$97.23
16	14/08/02	20073	Product3	Customer2	732	$0.7046	$515.77
17	06/08/02	4302-047-00130	Product9	Customer5	46	$0.1210	$5.57
18	26/07/02	4302-047-00130	Product9	Customer5	3,000	$0.1210	$363.00
19	19/08/02	4302-047-1790	Product10	Customer5	4	$0.8193	$3.28
20	19/08/02	4302-047-21780	Product11	Customer5	142	$0.7430	$105.51
21	19/08/02	4302-047-1790	Product10	Customer5	29	$0.8193	$23.76
22	19/08/02	4302-047-1790	Product10	Customer5	4	$0.8193	$3.28
23					7,498		$4,123.38
Sheet1

What I need to do is add the multiple entries so I end up with a single entry per product as below (The date column can be disregarded)<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=8><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Bulletin Board stuff.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2002 XP : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=8><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb658016><INPUT onclick='window.clipboardData.setData("Text",document.formFb431795.sltNb305190.value);' type=button value="Copy Formula" name=btCb074442></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=8><TABLE border=0><TBODY><TR><FORM name=formFb431795><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value" name=sltNb305190><OPTION value==E27*F27 selected>G27<OPTION value==E28*F28>G28<OPTION value==E29*F29>G29<OPTION value==E30*F30>G30<OPTION value==E31*F31>G31<OPTION value==E32*F32>G32<OPTION value==E33*F33>G33<OPTION value==E34*F34>G34<OPTION value==E35*F35>G35<OPTION value==E36*F36>G36<OPTION value==E37*F37>G37<OPTION value==SUM(E27:E37)>E38<OPTION value==SUM(G27:G37)>G38</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==E27*F27 name=txbFb428726></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>26</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 2pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">DATE</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 2pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">PART NUMBER</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 2pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">PART NAME</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: top; COLOR: #000000; BORDER-BOTTOM: #000000 2pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">CUSTOMER</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 2pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">QUANTITY</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 2pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $ Price </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 2pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $ VALUE  </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>27</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">30/08/02</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">2683</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Product 1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: top; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Customer1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1,380</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $      0.6146 </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $         848.15 </FONT></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>28</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">05/08/02</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">20072</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Product 2</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: top; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Customer2</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1,346</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $      0.7377 </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $         992.94 </FONT></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>29</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">05/08/02</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">20073</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Product 3</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: top; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Customer2</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1,433</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $      0.7046 </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> $      1,009.69 </FONT></TD></TR><TR><TD style="BORDER-TOP: #000000

 

[Sebastian]

Ooops. Don't know what happened there.
The second table should look like this

Bulletin Board stuff.xls
	A	B	C	D	E	F	G
26	DATE	PARTNUMBER	PARTNAME	CUSTOMER	QUANTITY	$Price	$VALUE
27	30/08/02	2683	Product1	Customer1	1,380	$0.6146	$848.15
28	05/08/02	20072	Product2	Customer2	1,346	$0.7377	$992.94
29	05/08/02	20073	Product3	Customer2	1,433	$0.7046	$1,009.69
30	15/08/02	2290313	Product4	Customer3	22	$7.1633	$157.59
31	15/08/02	2290315	Product5	Customer3	24	$4.7733	$114.56
32	26/08/02	6200142	Product6	Customer3	26	$2.7602	$71.77
33	26/08/02	6200154	Product7	Customer3	2	$9.9926	$19.99
34	31/07/02	0038-400-565	Product8	Customer4	40	$10.1076	$404.30
35	06/08/02	4302-047-00130	Product9	Customer5	3,046	$0.1210	$368.57
36	19/08/02	4302-047-1790	Product10	Customer5	37	$0.8193	$30.31
37	19/08/02	4302-047-21780	Product11	Customer5	142	$0.7430	$105.51
38					7,498		$4,123.38
Sheet1

Can it be done using VB code?

 

[stevebausch]

Okay, "you want to consolidate multiple entries". After sorting on what? Part Number? Customer? Part Name? Or a combination of all three, and in what sequence?

While doing so, you need to sum up columns E,F,G. Or, maybe not....

Actually, I am so confused. Having it displayed on two monitors, for comparision, didn't help, either.

Let's present this challenge my way, which is sequential:

Column A, the date: Keep the most recent date?
Column B, part number: unique identifier, having multiple citations of the same part number is what you want to eliminate.

Column C, part name: if there is a duplicate part number in the list, the duplicate part name is exactly the same in both rows, right?

Column D, Customer: sort on customer, before consolidating entries?

Column E, Quantity: after sorting on customer, sum up each customer's quantity.

Column F, Price: customers may have different prices quoted, don't lose the relationship.

Column G, Value: After sorting on customer, sum up the cumulative value of each part's purchase history.

Are any of these assumptions correct?
This message was edited by stevebausch on 2002-10-09 04:19

 

[Sebastian]

On 2002-10-09 04:15, stevebausch wrote:
Okay, "you want to consolidate multiple entries". After sorting on what? Part Number? Customer? Part Name? Or a combination of all three, and in what sequence?

While doing so, you need to sum up columns E,F,G. Or, maybe not....

Actually, I am so confused. Having it displayed on two monitors, for comparision, didn't help, either.

Let's present this challenge my way, which is sequential:

Column A, the date: Keep the most recent date? (Sebastian) The date doesn't matter.
Column B, part number: unique identifier, having multiple citations of the same part number is what you want to eliminate.

Column C, part name: if there is a duplicate part number in the list, the duplicate part name is exactly the same in both rows, right?
(Sebastian) That's correct.

Column D, Customer: sort on customer, before consolidating entries? (Sebastian) Not necessary.

Column E, Quantity: after sorting on customer, sum up each customer's quantity. (Sebastian) No, I need to sum up each product's quantity so at the end I have one entry of each product.

Column F, Price: customers may have different prices quoted, don't lose the relationship. (Sebastian) Each product has one price. The relationship between product and customer is one to one. ie. Product 3 belongs to Customer 2 and nobody else.

Column G, Value: After sorting on customer, sum up the cumulative value of each part's purchase history. (Sebastian) No.

Are any of these assumptions correct?

Firstly my apologies for a delayed reply. I posted the original question few hours ago when I was still at work.

I suppose I wasn't very clear with my explanations.
What I use the table for is - after all the calculations, I sort descending by column G (Value) and display top ten products on a chart.

That's why I need only one entry per product. For example: last month there were three entries for Product 3 (563, 138 and 732). I would add the quantities up, type the sum (1433) in one of the rows, overwriting the old entry and delete the other two. So when you you look at the total sum of column E it's the same in both tables.

Does that make any sense?
This message was edited by stevebausch on 2002-10-09 04:19

 

[stevebausch]

Excellent!

One reason I asked all the questions, is I don't know all the answers; others do, but we needed to make it easy for others to participate. My current strength is VBA, chances are Excel has features to handle your needs, without VBA. Chances are I don't know those features, yet


To reiterate:

Column A, the date:The date doesn't matter.

Column B, having multiple citations of the same part number is what you want to eliminate.

Column C, part name: duplicate part names are exactly the same? That's correct.

Column D, Customer: sort on customer Not necessary.

Column E, Quantity: I need to sum up each product's quantity so at the end I have one entry of each product.

Column F, Price: Each product has one price. The relationship between product and customer is one to one. ie. Product 3 belongs to Customer 2 and nobody else.

Column G, Value: Total of purchases, for the respective part number.


What I use the table for is - after all the calculations, I sort descending by column G (Value) and display top ten products on a chart.

I need only one entry per product.

For example: last month there were three entries for Product 3 (563, 138 and 732). I would add the quantities up, type the sum (1433) in one of the rows, overwriting the old entry and delete the other two. So when you you look at the total sum of column E it's the same in both tables.

 

[ThePencilQueen]

Create a pivot table with product in the row section and sum of quantity in the data section.

 

[Sebastian]

How easy it is to overlook the simple solutions. Thanks Pencil Queen.
I can now record a macro that will create a pivot table automatically.