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!

 

[Peter_SSs]

Give this a try in a copy of your workbook.
It assumes ..
- Data is on 'Sheet1' in columns A:F.
- Results go on 'Sheet2'.
- 'Sheet2' already exists but any data on it can be removed.

Sub ArrangeData()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim Order As String, Notes As String
  
  With Sheets("Sheet1")
    a = .Range("A1", .Range("A" & .Rows.Count).End(xlUp).Offset(1)).Resize(, 6).Value
  End With
  ReDim b(1 To UBound(a), 1 To 2)
  a(UBound(a), 1) = "^##"
  Order = a(1, 1): Notes = a(1, 2)
  For i = 2 To UBound(a)
    If Left(a(i, 1), 1) = "^" Then
      k = k + 1: b(k, 1) = Order: b(k, 2) = Application.Trim(Notes)
      Order = Trim(Mid(Left(a(i, 1), InStr(1, a(i, 1), "#") - 1), 2))
      Notes = Split(Join(Application.Index(a, i, 0)), "##")(1)
    Else
      Notes = Notes & Mid(Join(Application.Index(a, i, 0)), 2)
    End If
  Next i
  With Sheets("Sheet2")
    .UsedRange.ClearContents
    With .Range("A1").Resize(k, 2)
      .Value = b
      .Columns.AutoFit
    End With
  End With
End Sub

Sample Data & Results

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

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
4	487
Sheet2

 

[andyfox1979]

I'm getting runtime error, type: mismatch

Notes = Split(Join(Application.Index(a, i, 0)), "##")(1)


On this line. My data starts on A2, i switched that. I made this sample file. See below:


https://www.dropbox.com/s/fb7y1x34m4t8isy/sample.xlsx?dl=0

Thanks for the help, this is driving me insane!

 

[andyfox1979]

Oh wait, i think my tabs weren't capitalized properly.. one moment...

 

[andyfox1979]

hey peter, hmm no still getting that error.. not sure why

 

[Peter_SSs]

In your sample file I ..
- Renamed the 'sample' sheet to 'Sheet1'
- Added a blank sheet and named it 'Sheet2'
- Ran the code exactly as posted in post #11

The result was that the code ran without error & the first part of the results are shown below.

Excel Workbook
	A	B
1	Order	Notes
2	485	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
3	486	372883268032017, Exp. 8/2005"
4	487
5	488
6	489
7	490
8	491
9	492	2004 Jul 2 08:56: Marked OK by Wayland 2004 Jul 2 08:56: Marked OK:
10	493
11	494
12	495	2004 Jul 6 08:12: Marked OK by Wayland 2004 Jul 6 08:12: Marked OK:
13	496
Sheet2

Comments:
1. You originally talked about having data split across several (5 or 6?) columns. Your sample had nothing outside of column A. Which scenario is your real situation?

2. "My data starts on A2, i switched that." But you have headings in row 1. My code was using that fact so I suggest you switch it back to A1 again - unless you sample file is not realistic.

3. Run the code again on your real data. When you get the error, click 'Debug' then hover your cursor over the variable "i" and see what number appears in the pop-up box. This is the row number on the sheet where the problem is (assuming the change back to row 1 mentioned above). Have a look at what is in that row. If a problem is not obvious, please post that data from that row in a table like you did in post #1. Copy/Paste the data from your sheet, don't manually type it into your post.

 

[andyfox1979]

Peter I really appreciate your help and patience.

To simplify things, I joined any text in the other columns to column A, so now there's only one column with data that needs to be extracted/rearranged.

Essentially order number is in between ^ and ##. After ## is notes that I want to put into corresponding cell on sheet 2, column B (Notes). Sometimes there are several rows of notes. See below in red.

Here's a sample of my data:


[TABLE="class: grid, width: 479"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Order & Notes[/TD]
[/TR]
[TR]
[TD]^300000 ## [/TD]
[/TR]
[TR]
[TD]^300001 ## [/TD]
[/TR]
[TR]
[TD]^300002 ## [/TD]
[/TR]
[TR]
[TD]^300003 ## eb-address was approved by google. 10/28/13" [/TD]
[/TR]
[TR]
[TD]^300004 ## [/TD]
[/TR]
[TR]
[TD]^300005 ## th-10/28/13-cancelled/discontinued. refunded and informed customer. [/TD]
[/TR]
[TR]
[TD]> 2013 Oct 28 08:30: Marked Cancelled by Travis [/TD]
[/TR]
[TR]
[TD]> 2013 Oct 28 08:30: Cancelled; Reason: Could not be fulfilled [/TD]
[/TR]
[TR]
[TD]Credit 230.88 [/TD]
[/TR]
[TR]
[TD]Card xxxxxxxxxxxx8579 xx/xxxx [/TD]
[/TR]
[TR]
[TD]Result OK [/TD]
[/TR]
[TR]
[TD]Details Invoice = 2627 [/TD]
[/TR]
[TR]
[TD]> 2013 Oct 28 08:32: Modified by Travis" [/TD]
[/TR]
[TR]
[TD]^300006 ## [/TD]
[/TR]
[TR]
[TD]^300007 ##[/TD]
[/TR]
[TR]
[TD]^300008 ## [/TD]
[/TR]
[TR]
[TD]^300009 ## Marked OK by Travis[/TD]
[/TR]
[TR]
[TD]^300010 ## [/TD]
[/TR]
[TR]
[TD]^300011 ## [/TD]
[/TR]
[TR]
[TD]^300012 ##


[/TD]
[/TR]
</tbody>[/TABLE]

On Page Two I'd like my output to be:

[TABLE="class: grid, width: 1907"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Orders [/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]300000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300003[/TD]
[TD]eb-address was approved by google. 10/28/13" [/TD]
[/TR]
[TR]
[TD]300004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300005[/TD]
[TD]th-10/28/13-cancelled/discontinued. refunded and informed customer.&"> 2013 Oct 28 08:30: Marked Cancelled by Travis > 2013 Oct 28 08:30: Cancelled; Reason: Could not be fulfilled Credit 230.88 Card xxxxxxxxxxxx8579 xx/xxxx Result OK Details Invoice = 2627 > 2013 Oct 28 08:32: Modified by Travis" [/TD]
[/TR]
[TR]
[TD]300006[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300009[/TD]
[TD]Marked OK by Travis[/TD]
[/TR]
[TR]
[TD]300010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300012[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

First, I have removed the final comment from your post. Refer to #5 of the Forum Rules.

Try

Sub ArrangeData_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim Order As String, Notes As String
  
  With Sheets("Sheet1")
    a = .Range("A1", .Range("A" & .Rows.Count).End(xlUp).Offset(1)).Value
  End With
  ReDim b(1 To UBound(a), 1 To 2)
  a(UBound(a), 1) = "^##"
  Order = "Order": Notes = "Notes"
  For i = 2 To UBound(a)
    If Left(a(i, 1), 1) = "^" Then
      k = k + 1: b(k, 1) = Order: b(k, 2) = Application.Trim(Notes)
      Order = Trim(Mid(Left(a(i, 1), InStr(1, a(i, 1), "#") - 1), 2))
      Notes = Mid(a(i, 1), InStr(1, a(i, 1), "#") + 3)
    Else
      Notes = Notes & " " & a(i, 1)
    End If
  Next i
  With Sheets("Sheet2")
    .Columns("A:B").ClearContents
    With .Range("A1").Resize(k, 2)
      .Value = b
      .Columns.AutoFit
    End With
  End With
End Sub