Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 15th, 2013, 02:42 PM
Registered User
 
Join Date: Aug 2012
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re-position data and sum quantity based on matching reference

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
 
Old March 3rd, 2013, 10:23 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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)





Similar Threads
Thread Thread Starter Forum Replies Last Post
muenchian sum based on multiple grouping chopstix21 XSLT 1 November 2nd, 2012 10:28 AM
Copy row data from one file to another based on matching column data djfscouse Excel VBA 1 August 19th, 2012 10:55 PM
Data/Profile matching... sophia ASP.NET 4 General Discussion 0 August 19th, 2012 12:27 PM
Trouble with sum() and position() bbenick XSLT 1 October 23rd, 2009 04:09 PM
sum group values based on cell criteria darkdestroyer Excel VBA 1 January 9th, 2008 09:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.