Home » Forums » Open Source Learning Center » Question and Answer

Re-position data and sum quantity based on matching reference

Submitted by djfscouse on 15 February, 2013 - 18:12

Hi,

I'm using excel 2003 and using VBA I want to convert data from my original data below.

The first row is just to identify the columns below it.

REFR1,REFERENC2,COMPANYNAME01,CURNTDATE,QUANTITY,ITEMNUMBER,PRICE
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 TB,65.9900
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 CH,86.5800
71721,121221142,EXAPLECOMPANY,2-13-2013,1.0000,MPG PB01,75.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,2.0000,MPLC001,146.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,1.0000,MPMC001,120.9900
71720,121238748,EXAPLECOMPANY,2-13-2013,1.0000,MPS007,63.9900

To be converted to the following:

"COMPANYNAME01",
"TOTALITEMS","REFR1","REFERENC2","CURNTDATE","0",,"0.00","0.00"
"ITEMNUMBER","QUANTITY","PRICE","TOTALPRICE",

"EXAPLECOMPANY",
"2","71723","121222010","2-13-2013","0",,"0.00","0.00"
"MPG TBS01 TB","1.0000","65.9900","65.99",
"MPG TBS01 CH","1.0000","86.5800","86.58",

"EXAPLECOMPANY",
"1","71721","121221142","2-13-2013","0",,"0.00","0.00"
"MPG PB01","1.0000","75.9900","75.99",

"EXAPLECOMPANY",
"3","71718","121234503","2-13-2013","0",,"0.00","0.00"
"MPLC001","2.0000","146.9900","293.98",
"MPMC001","1.0000","120.9900","120.99",

"EXAPLECOMPANY",
"1","71720","121238748","2-13-2013","0",,"0.00","0.00"
"MPS007","1.0000","63.9900","63.99",

The converted output data above can be within the same document or output to a text file.

Thanks for any help,

David

‹ Combobox, need for VBA for changing fillrange and changing linked cell Numbers ›
»

1. Put your data table

Submitted by AndyLitch on 16 March, 2013 - 15:01.

1. Put your data table starting from A:21 onwards
Note - I added a total items column as the first column of the table
2. Add a textbox and set the following properties
Multiline = true
Enterkeybehaviour = true
3. Add a worksheet command button

In the sheet code module put the following:-

Option Explicit
Const Delim1 As String = """"
Const Delim2 As String = ""","""

Private Sub CommandButton1_Click()
Dim Y As Integer, OpTxt As String

Sheet1.TextBox1.Text = ""

For Y = 21 To 10000
If Cells(Y, 4).Value = "" Then Exit For
OpTxt = OpTxt & Delim1 & Cells(Y, 4).Value & Delim1 & "," & Chr(10)
OpTxt = OpTxt & Chr(34) & Cells(Y, 1).Value & Delim2 & Cells(Y, 2).Value & Delim2 & Cells(Y, 3).Value & Delim2 & Cells(Y, 5).Value & Delim2
OpTxt = OpTxt & "0" & Delim2 & "0.00" & Delim2 & "0.00" & Chr(10)
OpTxt = OpTxt & Delim1 & Cells(Y, 7).Value & Delim2 & Cells(Y, 6).Value & Delim2 & Cells(Y, 8).Value & Delim2
OpTxt = OpTxt & Cells(Y, 6).Value * Cells(Y, 8).Value & Delim1 & ","
OpTxt = OpTxt & Chr(10) & Chr(10)
Next Y

Sheet1.TextBox1.Text = OpTxt

End Sub

Now when you click the command button, the data will be compiled to the textbox in the format specified.

»

Example Output

Submitted by AndyLitch on 16 March, 2013 - 15:04.

"Example Company 1",
"2","71723","121222010","2-13-2013","0","0.00","0.00
"MPG TBS01 TB","1","65.99","65.99",

"Example Company 2",
"1","71723","121222010","2-13-2013","0","0.00","0.00
"MPG TBS01 CH","1","86.58","86.58",

"Example Company 3",
"3","71721","121221142","2-13-2013","0","0.00","0.00
"MPG PB01","1","75.99","75.99",

"Example Company 4",
"1","71718","121234503","2-13-2013","0","0.00","0.00
"MPLC001","2","146.99","293.98",

"Example Company 5",
"4","71718","121234503","2-13-2013","0","0.00","0.00
"MPMC001","1","120.99","120.99",

"Example Company 6",
"6","71720","121238748","2-13-2013","0","0.00","0.00
"MPS007","1","63.99","63.99",

»