Need VBA to arrange data in cells using delimiters? I'm stumped.

[andyfox1979]

So I have some data exported from an ecommerce store. Tabe delimited file. I've created two delimiters, ^ and ##:

^ is before each order number. This indicates notes are over, or new order number

## indicates notes begin. If there is nothing after this across the row (say, 5 rows over) then notes are empty, output should just be order number and blank notes.

Want to join all associated notes after the ## and 5 rows across and before the next ^ in a cell below--- join all of this in a single corresponding cell on an seperate output page.

Example Data


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Notes[/TD]
[TD]Notes2[/TD]
[/TR]
[TR]
[TD]^485 ## 2004 Jun 26 18:51: Marked Cancelled[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]> 2004 Jun 26 18:51: Cancelled; Reason: Customer changed mind[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]> 2004 Jul 6 10:41: Modified by Wayland[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^486## Cancelled[/TD]
[TD]By[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]^487##[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Desired Output

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Notes[/TD]
[TD]Notes2[/TD]
[/TR]
[TR]
[TD]485[/TD]
[TD]2004 Jun 26 18:51: Marked Cancelled by Wayland > 2004 Jun 26 18:51: Cancelled; Reason: Customer changed mind > 2004 Jul 6 10:41: Modified by Wayland[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]486[/TD]
[TD]Cancelled By Tom[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]487[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any hints on where to go from here would be greatly appreciated!

 

[sheetspread]

Each order in its own row with the # adjacent is a much better data structure, do you agree?

 

[andyfox1979]

Each order in its own row with the # adjacent is a much better data structure, do you agree?

Yes, I wish it exported like that, but it exported exactly like this. I removed some consistent junk data and replaced it with delimiters in order to be able to figure it out programatically but i'm stuck.

If you look at the example Data and Output its exactly what i'm trying to achieve here

 

[sheetspread]

This is how I'd refine it:

Excel 2010
	A	B
1	Group	Order
2	485	^485 ## 2004 Jun 26 18:51: Marked Cancelled
3	485	> 2004 Jun 26 18:51: Cancelled; Reason: Customer changed mind
4	485	> 2004 Jul 6 10:41: Modified by Wayland
5	486	^486## Cancelled
6	487	^487##
Sheet11

Cell Formulas
Range	Formula
A2	=IF(LEFT(B2,1)="^",MID(B2,2,3),A1)

You can probably follow the order # pattern without adding delimiters

 

[andyfox1979]

Thanks for taking a crack at it. Extracting the order numbers isn't my challenge, my issue is that i want to keep the notes tied to each order number, so the logical solution is to set some delimiters so that after ## it checks the immediate cell, and then the adjacent cell in the next 5 columns, adding any notes to a single output cell on the 2nd page... it would then return to the next row below and continues adding that to the notes until it reaches another ^, which would then indicate an order number and do the above over again.


[TABLE="class: grid, width: 746"]
<colgroup><col></colgroup><tbody>[TR]
[TD]^549 ## [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^550 ## [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^551 ## 2004 Oct 4 15:40: Marked Pending Review by Wayland[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]> 2004 Oct 4 15:40: Marked Pending Review: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]> 2004 Oct 6 07:24: Marked OK by Wayland[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]> 2004 Oct 6 07:24: Marked OK: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^552 ## [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^553 ## [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^554 ## [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^555 ## [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

How does it look before you add anything?

 

[andyfox1979]

[TABLE="class: grid, width: 746"]
<colgroup><col></colgroup><tbody>[TR]
[TD]^549 ## [/TD]
[/TR]
[TR]
[TD]^550 ## [/TD]
[/TR]
[TR]
[TD]^551 ## 2004 Oct 4 15:40: Marked Pending Review by Wayland[/TD]
[/TR]
[TR]
[TD]> 2004 Oct 4 15:40: Marked Pending Review: [/TD]
[/TR]
[TR]
[TD]> 2004 Oct 6 07:24: Marked OK by Wayland[/TD]
[/TR]
[TR]
[TD]> 2004 Oct 6 07:24: Marked OK: [/TD]
[/TR]
[TR]
[TD]^552 ## [/TD]
[/TR]
[TR]
[TD]^553 ## [/TD]
[/TR]
[TR]
[TD]^554 ## [/TD]
[/TR]
[TR]
[TD]^555 ## [/TD]
[/TR]
</tbody>[/TABLE]



Exactly like this, except there's some useless formatting around the order number (store id, etc). There's nothing unique that encapsulates the notes unfortunately. The only thing I could identify is where the notes begin and when they end (new order numer or ^).

 

[skywriter]

If you don't mind code you can try this.

It assumes your data starts in A1 and there are no blank cells in column A.

It will check the first six columns in each row for data.

Sub andygox1979()
Dim rngData As Range
Dim x, strTemp As String, j, nr As Long, k, r As Long, c As Long
With ActiveSheet.Range("A1").CurrentRegion
    Set rngData = .Offset(1).Resize(.Rows.Count - 1, 6)
End With
x = rngData
Application.ScreenUpdating = False
rngData.ClearContents
    For r = LBound(x, 1) To UBound(x, 1)
        For c = LBound(x, 2) To UBound(x, 2)
            strTemp = strTemp & IIf(x(r, c) = vbEmpty, "", x(r, c) & " ")
        Next c
    Next r

x = Split(strTemp, "^")
nr = 2
    For Each j In x
        If j <> "" Then
        k = Split(j, "##")
        Cells(nr, 1).Value = Trim(k(0))
        Cells(nr, 2).Value = Trim(k(1))
        nr = nr + 1
        End If
    Next j
Columns("A:B").AutoFit
Application.ScreenUpdating = True
End Sub

 

[skywriter]

Here's a before and after running the code.

Sheet1

*	A	B	C	D	E
Order	Notes	Notes2	*	*
^485 ## 2004 Jun 26 18:51: Marked Cancelled	*	*	*	*
> 2004 Jun 26 18:51: Cancelled; Reason: Customer changed mind	*	*	*	*
> 2004 Jul 6 10:41: Modified by Wayland	*	*	*	*
^486## Cancelled	By	Tom and	*	Joe
^487##	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:422.67px;"><col style="width:149.33px;"><col style="width:62.67px;"><col style="width:64px;"><col style="width:64px;"></colgroup>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

Excel Workbook
	A	B
1	Order	Notes
2	485	2004 Jun 26 18:51: Marked Cancelled > 2004 Jun 26 18:51: Cancelled; Reason: Customer changed mind > 2004 Jul 6 10:41: Modified by Wayland
3	486	Cancelled By Tom and Joe
4	487	*
Sheet1

 

[andyfox1979]

Bruce this is awesome, thanks so much... what do do if there are blank cells in column A? (unfortunately there are)