How to Programmatically Split and Manipulate Rows of Data From Excel
- by Charlene
I am hoping one of you will be able to help get me started on this issue. I need to create some sort of macro or VBA code to split and manipulate rows of data in Excel.
For this example, we have 5 rows of data. The first 3 rows are item information for Order # 0000000000-00 and the last 2 rows are item information for order # 0000000000-01. I need one row ("HDR") for each order number, and one row ("ITM") for each product per order. I have included an example below showing the data I will receive and the desired outcome.
Raw Data:
order-id product-num date buyer-name product-name quantity-purchased
0000000000-00 10000000000000 5/29/2014 John Doe Product 0 1
0000000000-00 10000000000001 5/29/2014 John Doe Product 1 2
0000000000-00 10000000000002 5/29/2014 John Doe Product 2 1
0000000000-01 10000000000002 5/30/2014 Jane Doe Product 2 1
0000000000-01 10000000000003 5/30/2014 Jane Doe Product 3 1
Desired Outcome:
HDR 0000000000-00 John Doe 5/29/2014
ITM 10000000000000 Product 0 1
ITM 10000000000001 Product 1 2
ITM 10000000000002 Product 2 1
HDR 0000000000-01 Jane Doe 5/30/2014
ITM 10000000000002 Product 2 1
ITM 10000000000003 Product 3 1
Any and all help would be much appreciated!!! Thank you.