Data Source using indented data

[soggy_foot]

Hi,


I've got a challenging data set that I'm trying to apply formatting or a macro to but am having a heck of a time solving it. I had a nice little macro working but the report data changed slightly breaking the functional macro. Now it's back to the drawing board trying to solve this interesting issue. Please be my second set of eyes and let me know if you see a solution.


Objective: Reformat data import to so every line counts. Currently sales order lines and sales order header information shares columns. ie Sales order A has 3 lines, a header line and 2 item lines. What I'm shooting for is a reformat which blends the header information into to the order detail lines. One issue is that the number of lines a sales order might have could be as little as 1 and as many as 1001.


Here's an example of how the data shows up after import


Column A - S/O & Line# - this column holds two data sets, the sales order and line number ideally these would be broken to separate columns.


Column B - Customer/Item Number - again shared column - When customer is populated it's a name when item nubmer is populated it's a unique alphanumeric


Column C - Order Date


Column D - Ship Date


Column E - UOM


Column F - Ordered


Column G - Shipped


Column H - Balance


Column I - Price

 

[Hui]

I Would manually break columns containing 2 or more fields into seperate columns using Data, Text to Columns

Then look at putting it into a Data Table or a Pivot Table and apply some filters/sorting

 

[soggy_foot]

Thanks for the note Hui!


For kicks I worked the data manually this morning.


Quick stats:


Imported lines: 3397

After reformatting: 2559

Netting: 727 unique orders

Time spent: 74 minutes


It was good practice and made me really think about the steps I'm doing and what I am trying to achieve with the improvements. So here's what I'm up against in the department of sales/line number sharing the same column.


Present day:


200792

1

2

3

4

265341

1

2

277255

1

2

3


Here's how I'd like it to look:


200792

200792

200792

200792

200792

265341

265341

265341

277255

277255

277255

277255


And so on . . .


Any thoughts on how to get there?


Thanks!!!

 

[Hui]

Add a column

lets say it is Column B

B1: =A1

B2: =if(A2<1000,B1,A2)

Copy B2 Down

Copy Column B and Paste Values over itself

 

[soggy_foot]

Nicely done Hui!


I can't believe how hard I'd been racking my brain on this one and such a simple solution.


Well done, thanks Hui.