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
<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:
<tbody>
</tbody>
Thanks for any suggestions.
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
Item | Our quantity | Their quantity | Difference | Comment |
1 | 20 | 30 | -10 | Timing difference |
2 | 30 | 25 | 5 | Under investigation |
3 | 9 | 60 | -51 | 1. 5 missing instruction 2. 15 incorrect posting, 3. 31 timing difference |
4 | 10 | 10 | - | 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:
Item | Our quantity | Their quantity | Difference | Comment |
1 | 20 | 30 | -10 | Timing difference |
2 | 30 | 25 | 5 | Under investigation |
3 | 9 | 60 | -51 | 1. 5 missing instruction |
3 | 9 | 60 | -51 | 2. 15 incorrect posting, |
3 | 9 | 60 | -51 | 3. 31 timing difference |
4 | 10 | 10 | - | No difference |
<tbody>
</tbody>
Thanks for any suggestions.