Split the content of a cell into separate rows

[Bering]

Hello,

I have to review a reconciliation file similar to the one below. The problem is that in column 5 (Comment) there could be cells that include a number of items, which makes it impossible for me to filter/analyse the data, (i.e. comments for item #3 ).

Whenever a comment contains several items, each item is identified by a consecutive number followed by a dot.

For example, for item 3, the comment provides a break-down of the -51 difference as:

1. 5 missing instruction
2. 15 incorrect posting,
3. 31 timing difference


[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Our quantity[/TD]
[TD]Their quantity[/TD]
[TD]Difference[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]-10[/TD]
[TD]Timing difference[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]Under investigation[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]60[/TD]
[TD]-51[/TD]
[TD]1. 5 missing instruction 2. 15 incorrect posting, 3. 31 timing difference[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]-[/TD]
[TD]No difference[/TD]
[/TR]
</tbody>[/TABLE]


Is there a way (vba, formulae, text to column..) to split the content of such cells into separate rows so that the result looks be something like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Our quantity[/TD]
[TD]Their quantity[/TD]
[TD]Difference[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]-10[/TD]
[TD]Timing difference[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]Under investigation[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]60[/TD]
[TD]-51[/TD]
[TD]1. 5 missing instruction[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]60[/TD]
[TD]-51[/TD]
[TD]2. 15 incorrect posting,[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]60[/TD]
[TD]-51[/TD]
[TD]3. 31 timing difference[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]-[/TD]
[TD]No difference[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any suggestions.

 

[Peter_SSs]

Give this a try in a copy of your workbook.

I have assumed that, on average, there will be no more than 10 numbered items per row in column E.

Sub Split_Rows()
  Dim a As Variant, b As Variant, m As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A1", Range("E" & Rows.Count).End(xlUp)).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To UBound(a) * 10, 1 To uba2)
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(\d+\.)(.+?)(?=(\d+\.)|$)"
    For i = 1 To UBound(a)
      If .test(a(i, uba2)) Then
        For Each m In .Execute(a(i, uba2))
          k = k + 1
          For j = 1 To uba2 - 1
            b(k, j) = a(i, j)
          Next j
          b(k, uba2) = m
        Next m
      Else
        k = k + 1
        For j = 1 To uba2
          b(k, j) = a(i, j)
        Next j
      End If
    Next i
  End With
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, uba2).Value = b
End Sub

Sample data in rows 1:5, result of code in rows 8:14

Excel Workbook
	A	B	C	D	E
1	Item	Our quantity	Their quantity	Difference	Comment
2	1	20	30	-10	Timing difference
3	2	30	25	5	Under investigation
4	3	9	60	-51	1. 5 missing instruction 2. 15 incorrect posting, 3. 31 timing difference
5	4	10	10	-	No difference
6
7
8	Item	Our quantity	Their quantity	Difference	Comment
9	1	20	30	-10	Timing difference
10	2	30	25	5	Under investigation
11	3	9	60	-51	1. 5 missing instruction
12	3	9	60	-51	2. 15 incorrect posting,
13	3	9	60	-51	3. 31 timing difference
14	4	10	10	-	No difference
Sheet1

 

[Bering]

Thank you sooo much!!!! That is exactly what I wanted to achieve!

Have a great day ray:

 

[Peter_SSs]

Thank you sooo much!!!! That is exactly what I wanted to achieve!

Have a great day ray:

You are very welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only. So I've removed the quote from your post.

 

[Bering]

Duly noted! Sorry for that...

Thanks again!

 

[Bering]

May I ask you to explain the meaning of this bit of the code? I have many VBA books but have never seen anything like this. I would be very interested in learning more: is there any book that you would suggest?

Thank you.

[/COLOR]Pattern = "(\d+\.)(.+?)(?=(\d+\.)|$)"

 

[Peter_SSs]

May I ask you to explain the meaning of this bit of the code? I have many VBA books but have never seen anything like this. I would be very interested in learning more: is there any book that you would suggest?

Thank you.

Pattern = "(\d+\.)(.+?)(?=(\d+\.)|$)"

To understand my code you need to learn about "Regular Expressions". I do not find them easy, or easy to explain, but I will try.

(\d+\.) This says to look for a digit (\d) 1 or more times (+) followed immediately by a "." (\.)
(.+?)(?=(\d+\.)|$) This says to then look for any character (.) 1 or more times (+) but as few as possible (?) until when you look ahead (?=) you see either [a digit (\d) 1 or more times (+) followed immediately by a "." (\.)] or (|) [the end of the string ($)]

When this part of the code is run ..

.Execute(a(i, uba2))

.. it tries to find as many patters as it can (.Global = True) that fit the above. So for cell E4 in my post, it finds that pattern 3 times as indicated by the different colours here.

1. 5 missing instruction 2. 15 incorrect posting, 3. 31 timing difference

I do have Sams Teach Yourself Regular Expressions in 10 Minutes (don't believe the title) by Ben Forta, but much of my learning on Regular Expressions has been in this forum or via Googling.

Good luck with it!!

 

[Bering]

Thanks a lot Peter, very helpful.

 

[Peter_SSs]

Thanks a lot Peter, very helpful.

No problem.

 

[Bering]

Hi Peter,

me again... I hope you can help me with fixing the code for the example below (the real thing..).

Here there are 14 numbered items: I fixed that by changing this bit of the code:

ReDim b(1 To UBound(a) * 20, 1 To uba2)

As the sentences are longer than in my first example I have also amended this (as few as possible) (.+?) to this (.+)

I am not quite there tough, as the result is still an incomplete sentence. Any suggestions, please?


1. -67.57 P&L discrepancy. As per statement for 280218 there is a P&L of 96015, yet it is posted in ICON as 95947.4 which leaves a discrepancy of 67,57
2. -13.09 P&L Discrepancy. As per statement as at 14032018 there is a P&L of 519,725, yet it is posted in ICON as 519,738.09 which leaves a discrepancy of 13.09-
3. -3.05 Commission discrepancy. As per statement as at 20042018 there is a commission of 161.65 but yet it is posted as 158.60. Hence this leaves a discrepancy of 3,05
4. 54.60 Incorrect commission posted as at 27/04/2018 SJ036309 to be reversed.
5. 48 P&L discrepancy. As per statement as at 04052018 there is a P&L of 453,815.98 but yet it is posted as 453,767.96. Hence this leaves a discrepancy of 48.
6. -25 P&L discrepancy. As per statement as at 31052018 there is a P&L of 56,845. But yet it is posted as 56,820. Hence this leaves a discrepancy of 25.
7. -16.89 P&L discrepancy. As per statement as at 13062018 there is a P&L of 45362.50 but yet it is posted as 45345.61. Hence this leaves a discrepancy of 16.89.
8. -3.85 Commission discrepancy. As per statement as at 11062018 there is a commission of 204.05 but yet it is posted as 200.20. Hence this leaves a discrepancy of 3.85
9. 161.4 P&L discrepancy. As per statement as at 11072018 there is a P&L of 310,245 but yet it is posted as 310,406.40. Hence this leaves a discrepancy of 161.4
10. 28.42 - P&L discrepancy. As per statement as at 180718 there is a P&L of 1,395, yet it is posted in ICON as 1,423.42. This leaves a discrepancy of 28.42
11. -75.46 - P&L Discrepancy. As per statement as at 260718 there is a P&L of 87,620, yet it is posted as 87,695.46. This leaves a discrepancy of 75.46
12. -113.90 - P&L Discrepancy. As per statement as at 030818 there is a P&L of 113,185, yet it is posted as 113,298.90. This leaves a discrepancy of 113.90
13. 71.1 P&L Discrepancy. As per statement as at 07.18.18 there is a P&L of 23,780, yet it is posted as 23,708.9. This leaves a discrepancy of 71.1
14. -71 - P&L Discrepancy as at 16.08.18. As per statement there is a P&L of 120,830, yet it is posted in ICON as 120,759. This leaves a discrepancy of 71