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,COMPANYNAME,CURNTDATE,QUANTITY,ITE MNUMBER,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:

"COMPANYNAME",
"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

Hi David

You have the logic to split the same. Apply some VBA to copy content to a new Excel workbook based on that.

Once you are advanced in VBA, you can use XSLT / ADO to do the same

Happy coding

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)