Counting Unique ticket Ids

[Angel88]

Hello All,

I have a data which contains the number of customers who have logged cases with us to find a solution. So the data looks like as shown below;

<TABLE style="WIDTH: 688pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=916 border=0><COLGROUP><COL style="WIDTH: 52pt" width=69><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 52pt" width=69><COL style="WIDTH: 315pt; mso-width-source: userset; mso-width-alt: 15360" width=420><COL style="WIDTH: 133pt; mso-width-source: userset; mso-width-alt: 6473" width=177><COL style="WIDTH: 52pt" width=69><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=69 height=20>Case ID</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>Category</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=69>Country</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 315pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=420>Subject Question</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 133pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=177>Closing Details</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=69></TD></TR><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: transparent" align=right height=63>2550</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Denmark</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 315pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=420>Hi Please help me with the follwing orders, ticket number is JN567786</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Contact the retail shop</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 47.25pt; mso-height-source: userset" height=63><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: transparent" align=right height=63>2550</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Belgium</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 315pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=420>Hi please note that the product KA12QT seems to be having hardware problems. the ticket number is JN567786.


</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 133pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=177>Technical issue fixed, DCK JN567786</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 42pt; mso-height-source: userset" height=56><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 42pt; BACKGROUND-COLOR: transparent" align=right height=56>2876</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">APJ</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Australia</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 315pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=420>Problem with the music system, resolution required.
The ticket Id logged is JN782238
</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 133pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=177>new CD provided to resolve</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR></TBODY></TABLE>

What i need to do is find out the unique ticket ID (eg: JN782238) from all the description provided from column 'Subject Question' and 'Closing Details'. All the ticket numbers start from JN... , however it is very manual for to check every row has a ticket number. I have to do this for many sheets and the data contains atleast 20 to 25000 lines.

Inorder to find this unique ticket number i search through filter by giving the criteria as JN1 and keep doing untill i reach JN9. Once i filter through each of these criteria, i add a number "1" in the last column indicating that this row contains a ticket number. After completing for all the rows, I add a pivot to check the final count of the the unique numbers, But again i need to remove the duplicates from these count, as there might be several descriptions with the same ticket Id (eg:JN789899). So i add field "Case ID" to the row area in pivot and count of these numbers in the 'Value area' (pivot). Then i look for case IDs with numbers more than 1. I f i find 2 or 3 for a particular case Id then i search this case id in the data and delete one of the count ('1') in the last column.

Is there an easier option for me to do this process of counting the unique tickets.

Thank You..

 

[GTO]

Well, no gaurantees and this is a tad sloppy, but maybe close to what you are after.

To return the number of unique ticket IDs, and optionally, what the unique IDs are, like:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Case ID	Category	Country	Subject Question	Closing Details		Count	Opt List
2	2550	EMEA	Denmark	Hi Please help me with the follwing orders, ticket number is JN5677869	Contact the retail shop	1	3	JN5677869
3	2550	EMEA	Belgium	Hi please note that the product KA12QT seems to be having hardware problems. the ticket number is JN5677869.	Technical issue fixed, DCK JN567786	1		JN7822389
4								JN5889779
5
6	2876	APJ	Australia	Problem with the music system, resolution required.	new CD provided to resolve	1
7				The ticket Id logged is JN7822389
8	27900	APJ	Australia	Please help with JN5889779, it is not resolved
JN Tickets

Excel 2000

In a Standard Module:<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> CallIt()<br>    Call CountUniques(2, 4, "JN", Range("G2"), "JN Tickets", Range("H2"))<br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> CountUniques(FirstRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, Col<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, LeadCharacters<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _<br>                 AnswerCell<SPAN style="color:#00007F">As</SPAN> Range,<SPAN style="color:#00007F">Optional</SPAN> ShName<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _<br>                <SPAN style="color:#00007F">Optional</SPAN> ListingStart<SPAN style="color:#00007F">As</SPAN> Range)<br>Dim _<br>wks            <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br>rngLRow        <SPAN style="color:#00007F">As</SPAN> Range, _<br>rngSearch      <SPAN style="color:#00007F">As</SPAN> Range, _<br>vntElement      <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>, _<br>arySearchRange  <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>, _<br>strReturn      <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _<br>REX            <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>, _<br>DIC            <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> ShName = vbNullString<SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wks = ThisWorkbook.ActiveSheet<br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wks = ThisWorkbook.Worksheets(ShName)<br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngLRow = wks.Range(wks.Cells(FirstRow, Col), _<br>                            wks.Cells(Rows.Count, Col)).Find(What:="*", _<br>                                                             After:=wks.Cells(FirstRow, Col), _<br>                                                             LookIn:=xlValues, _<br>                                                             LookAt:=xlPart, _<br>                                                             SearchOrder:=xlByRows, _<br>                                                             SearchDirection:=xlPrevious)<br>    <SPAN style="color:#00007F">If</SPAN> rngLRow<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN><br>    <br>    arySearchRange = wks.Range(wks.Cells(FirstRow, Col), wks.Cells(rngLRow.Row, Col)).Value<br>    <SPAN style="color:#00007F">Set</SPAN> REX = CreateObject("VBScript.RegExp")<br>    <SPAN style="color:#00007F">Set</SPAN> DIC = CreateObject("Scripting.Dictionary")<br>    <br>    <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> vntElement<SPAN style="color:#00007F">In</SPAN> arySearchRange<br>        <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> FindString(REX, LeadCharacters, vntElement) = vbNullString<SPAN style="color:#00007F">Then</SPAN><br>            strReturn = FindString(REX, LeadCharacters, vntElement)<br>            DIC.Item(strReturn) = strReturn<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    wks.Range(AnswerCell.Address) = DIC.Count<br>    <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> ListingStart<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><br>        wks.Range(ListingStart.Address).Resize(DIC.Count).Value = Application.Transpose(DIC.Items)<br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Function</SPAN> FindString(REX<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>, LeadChar<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>,<SPAN style="color:#00007F">ByVal</SPAN> CurString<SPAN style="color:#00007F">As</SPAN> String)<SPAN style="color:#00007F">As</SPAN> String<br>Dim _<br>oMatches        <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">ReDim</SPAN> aryRet(0<SPAN style="color:#00007F">To</SPAN> 0)<br>    <SPAN style="color:#00007F">With</SPAN> REX<br>        .Global =<SPAN style="color:#00007F">False</SPAN><br>        .Pattern = LeadChar & "[0-9]{7}"<br>        <SPAN style="color:#00007F">If</SPAN> .Test(CurString)<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> oMatches = .Execute(CurString)<br>            FindString = oMatches(0)<br>        <SPAN style="color:#00007F">Else</SPAN><br>            FindString = vbNullString<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Hope that helps,

Mark