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..

 

[crimson_b1ade]

Book1
	A	B	C	D	E	F	G	H
1	CaseID	Category	Country	SubjectQuestion	ClosingDetails		SubjectCode	ClosingCode
2	2550	EMEA	Denmark	HiPleasehelpmewiththefollwingorders,ticketnumberisJN567786	dd	1	JN567786
3	2550	EMEA	Belgium	HipleasenotethattheproductKA12QTseemstobehavinghardwareproblems.theticketnumberisJN567786.	Technicalissuefixed,DCKJN567786	1	JN567786	JN567786
4
5
6	2876	APJ	Australia	Problemwiththemusicsystem,resolutionrequired.	newCDprovidedtoresolve	1
7				TheticketIdloggedisJN782238			JN782238
Sheet1

 

[Domski]

Maybe like this:

Book2
	D	E	F	G
1	Subject Question	Closing Details	Ticket No
2	Hi Please help me with the follwing orders, ticket number is JN567786	Contact the retail shop	JN567786
3	Hi please note that the product KA12QT seems to be having hardware problems. the ticket number is JN567786.	Technical issue fixed, DCK JN567786	JN567786
4	Problem with the music system, resolution required. The ticket Id logged is JN782238	new CD provided to resolve	JN782238
5			2
Sheet1

Formula in F2 copied down to F4: =MID(D2,SEARCH("JN",D2),8)

Formula in F5 confirmed with Ctrl+Shift+Enter as it's an array formula:

=SUM(IF(FREQUENCY(IF(F2:F4<>"",MATCH("~"&F2:F4,F2:F4&"",0)),ROW(F2:F4)-ROW(F2)+1),1))

Dom

 

[Angel88]

Hi,

I used this formula, "=MID(D2,SEARCH("JN",D2),8", however even the words that start with JN appear as a result.

Example: "Product JNETTING is obsolete now"

I get the result as JNETTING

I also tried applying this formula for other sheets as well, but in those sheets the ticket numbers are different and starts with CA.
For these i get all the words starting with CA (eg: 'Can i borrow....')

Thanks,..

 

[GTO]

Greetings Angel88,

If I am reading correctly, I think we need a good rule that we can follow in identifying a 'ticket ID'.

For instance, here are some guesses:

• All 'Ticket ID's' have two UPPERCASE alpha characters at the beginning.
  • These are always followed by six digits w/no spaces
• On a given sheet, we can input what the two leading alpha characters are, e.g. if a sheet has the ticket ID "AC123456", then all other ticket IDs on that sheet will begin with "AC"

Could you give a similar description to rule(s) one might follow in determining a ticket ID?

Mark

 

[Angel88]

Hi,

You are absolutely correct.... All the ticket Ids in each sheet start with 2 Upper case alpha characters and are same throughout the sheet and are always followed by 7 digit numbers.

 

[GTO]

Hi,

You are absolutely correct.... All the ticket Ids in each sheet start with 2 Upper case alpha characters and are same throughout the sheet and are always followed by 7 digit numbers.

I am counting 6 digits in the examples?

 

[Angel88]

Hi,

It is 7 digits, since it was an example i gave it as 6 digits. The numeric digits would always remain 7 in all sheets.

 

[crimson_b1ade]

Book1.xlsm
	A	B	C	D	E	F	G	H
1	CaseID	Category	Country	SubjectQuestion	ClosingDetails		SubjectNo	ClosingNO
2	2550	EMEA	Denmark	HiPleasehelpmewiththefollwingorders,ticketnumberisJN5677869	Contacttheretailshop	1	JN5677869
3	2550	EMEA	Belgium	HipleasenotethattheproductKA12QTseemstobehavinghardwareproblems.theticketnumberisJN5677869.	Technicalissuefixed,DCKJN5677869	1	JN5677869	JN5677869
4	2876	APJ	Australia	Problemwiththemusicsystem,resolutionrequired.TheticketIdloggedisJN7822389	newCDprovidedtoresolve	1	JN7822389
5	2876	APJ	Australia	Problemwiththemusicsystem,resolutionrequired.TheticketIdloggedisCA7822389	newCDprovidedtoresolve	1	CA7822389
6	2550	EMEA	Belgium	HipleasenotethattheproductKA12QTseemstobehavinghardwareproblems.theticketnumberisCA5677869.	Technicalissuefixed,DCKCA5677869	1	CA5677869	CA5677869
Sheet3

 

[crimson_b1ade]

the UDF code for the above mySearch function is:

Private Function mySearch(myString As String, myHeader As String)
 
For i = 1 To Len(myString)
 
    If Mid(myString, i, 9) Like myHeader & "[0-9][0-9][0-9][0-9][0-9][0-9][0-9]" Then
        mySearch = Mid(myString, i, 9)
        Exit Function
    End If
 
Next
 
mySearch = ""
 
End Function