jonnyp138
Board Regular
- Joined
- May 2, 2015
- Messages
- 50
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- 2003 or older
- Platform
- Windows
- MacOS
- Mobile
- Web
Hi there, just wondering if someone can assist, basically I have a spreadsheet containing approx 150,000 lines of event information along with each time a note was added to that particular event ID, Here is an example of a small part of that table
<colgroup><col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:13019;width:267pt" width="356"> <col style="mso-width-source:userset;mso-width-alt:13641;width:280pt" width="373"> </colgroup><tbody>
[TD="class: xl66, width: 111"]Date[/TD]
[TD="class: xl67, width: 85"]Yes/No[/TD]
[TD="class: xl68, width: 356"]UUID[/TD]
[TD="class: xl69, width: 373"]Notes[/TD]
[TD="class: xl70, align: right"]22/07/2018 03:07[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11159[/TD]
[TD="class: xl73"]Auto Ticket: Event Closed adding worklog on Incident[/TD]
[TD="class: xl74, align: right"]22/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11159[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]22/07/2018 22:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11163[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event[/TD]
[TD="class: xl74, align: right"]22/07/2018 22:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11163[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl70, align: right"]24/07/2018 10:08[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11235[/TD]
[TD="class: xl73"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]24/07/2018 10:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11235[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event[/TD]
[TD="class: xl70, align: right"]25/07/2018 07:23[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11255[/TD]
[TD="class: xl73"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11379[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event[/TD]
[TD="class: xl74, align: right"]29/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11379[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 22:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11383[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl78, align: right"]29/07/2018 22:07[/TD]
[TD="class: xl79"]Y[/TD]
[TD="class: xl80"]10.220.34.177:3181.1531762471.11383[/TD]
[TD="class: xl81"]Related Ticket Closed so Closing Event
[/TD]
</tbody>
As you can see there are duplicate UUID's on seperate lines as they have a different note added, what I would like to do is parse through the lines and amalgomate all duplicate event Id's with respective notes seperated by a pipe on the same line so it is like this:
<colgroup><col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:8557;width:176pt" width="234"> <col style="mso-width-source:userset;mso-width-alt:26075;width:535pt" width="713"> </colgroup><tbody>
[TD="class: xl66, width: 111"]Date[/TD]
[TD="class: xl67, width: 85"]Yes/No[/TD]
[TD="class: xl68, width: 234"]UUID[/TD]
[TD="class: xl69, width: 713"]Notes[/TD]
[TD="class: xl70, align: right"]22/07/2018 03:07[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11159[/TD]
[TD="class: xl73"]Auto Ticket: Event Closed adding worklog on Incident | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]22/07/2018 22:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11163[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]24/07/2018 10:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11235[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11379[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 22:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11383[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3 | Related Ticket Closed so Closing Event[/TD]
</tbody>
Can anyone assist with the vba to acheive this?
<colgroup><col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:13019;width:267pt" width="356"> <col style="mso-width-source:userset;mso-width-alt:13641;width:280pt" width="373"> </colgroup><tbody>
[TD="class: xl66, width: 111"]Date[/TD]
[TD="class: xl67, width: 85"]Yes/No[/TD]
[TD="class: xl68, width: 356"]UUID[/TD]
[TD="class: xl69, width: 373"]Notes[/TD]
[TD="class: xl70, align: right"]22/07/2018 03:07[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11159[/TD]
[TD="class: xl73"]Auto Ticket: Event Closed adding worklog on Incident[/TD]
[TD="class: xl74, align: right"]22/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11159[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]22/07/2018 22:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11163[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event[/TD]
[TD="class: xl74, align: right"]22/07/2018 22:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11163[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl70, align: right"]24/07/2018 10:08[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11235[/TD]
[TD="class: xl73"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]24/07/2018 10:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11235[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event[/TD]
[TD="class: xl70, align: right"]25/07/2018 07:23[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11255[/TD]
[TD="class: xl73"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11379[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event[/TD]
[TD="class: xl74, align: right"]29/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11379[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 22:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11383[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl78, align: right"]29/07/2018 22:07[/TD]
[TD="class: xl79"]Y[/TD]
[TD="class: xl80"]10.220.34.177:3181.1531762471.11383[/TD]
[TD="class: xl81"]Related Ticket Closed so Closing Event
[/TD]
</tbody>
As you can see there are duplicate UUID's on seperate lines as they have a different note added, what I would like to do is parse through the lines and amalgomate all duplicate event Id's with respective notes seperated by a pipe on the same line so it is like this:
<colgroup><col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:8557;width:176pt" width="234"> <col style="mso-width-source:userset;mso-width-alt:26075;width:535pt" width="713"> </colgroup><tbody>
[TD="class: xl66, width: 111"]Date[/TD]
[TD="class: xl67, width: 85"]Yes/No[/TD]
[TD="class: xl68, width: 234"]UUID[/TD]
[TD="class: xl69, width: 713"]Notes[/TD]
[TD="class: xl70, align: right"]22/07/2018 03:07[/TD]
[TD="class: xl71"]Y[/TD]
[TD="class: xl72"]10.220.34.177:3181.1531762471.11159[/TD]
[TD="class: xl73"]Auto Ticket: Event Closed adding worklog on Incident | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]22/07/2018 22:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11163[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]24/07/2018 10:08[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11235[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 03:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11379[/TD]
[TD="class: xl77"]Related Ticket Closed so Closing Event | Auto Ticket: Incident requested for L3_SQL - PRIORITY_3[/TD]
[TD="class: xl74, align: right"]29/07/2018 22:07[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl76"]10.220.34.177:3181.1531762471.11383[/TD]
[TD="class: xl77"]Auto Ticket: Incident requested for L3_SQL - PRIORITY_3 | Related Ticket Closed so Closing Event[/TD]
</tbody>
Can anyone assist with the vba to acheive this?