Split the content of a cell into separate rows

Bering

Board Regular
Joined
Aug 22, 2018
Messages
127
Office Version
  1. 2016
Platform
  1. Windows
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


ItemOur quantityTheir quantityDifferenceComment
12030-10Timing difference
230255Under investigation
3960-511. 5 missing instruction 2. 15 incorrect posting, 3. 31 timing difference
41010-No difference

<tbody>
</tbody>


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:


ItemOur quantityTheir quantityDifferenceComment
12030-10Timing difference
230255Under investigation
3960-511. 5 missing instruction
3960-512. 15 incorrect posting,
3960-513. 31 timing difference
41010-No difference

<tbody>
</tbody>

Thanks for any suggestions.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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
ABCDE
1ItemOur quantityTheir quantityDifferenceComment
212030-10Timing difference
3230255Under investigation
43960-511. 5 missing instruction 2. 15 incorrect posting, 3. 31 timing difference
541010-No difference
6
7
8ItemOur quantityTheir quantityDifferenceComment
912030-10Timing difference
10230255Under investigation
113960-511. 5 missing instruction
123960-512. 15 incorrect posting,
133960-513. 31 timing difference
1441010-No difference
Sheet1
 
Last edited:

Bering

Board Regular
Joined
Aug 22, 2018
Messages
127
Office Version
  1. 2016
Platform
  1. Windows
Thank you sooo much!!!! That is exactly what I wanted to achieve!

Have a great day :pray:
 
Last edited by a moderator:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Thank you sooo much!!!! That is exactly what I wanted to achieve!

Have a great day :pray:
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

Board Regular
Joined
Aug 22, 2018
Messages
127
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Duly noted! Sorry for that... :p

Thanks again!
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
127
Office Version
  1. 2016
Platform
  1. Windows
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.

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

Rich (BB code):
Rich (BB code):
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 ..
Rich (BB code):
.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!!
 
Last edited:

Bering

Board Regular
Joined
Aug 22, 2018
Messages
127
Office Version
  1. 2016
Platform
  1. Windows
Thanks a lot Peter, very helpful.
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
127
Office Version
  1. 2016
Platform
  1. Windows
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:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,921
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top