Formula for trouble ticket reassignment

[carog]

Hello, I'm looking for a formula to count trouble ticket case reassignments. Our software allows case reassignment back and forth, but the export of the case history is a little goofy.

Each Case ID is printed in Column A. Column B contains the person the case was reassigned to. Each time a case is reassigned, a new row is added with the same Case ID in Column A, and an updated user in Column B.

I'd like to know for each unique case in Column A how many times it was reassigned to a unique person. Is this possible?

 

[Fluff]

Hi & welcome to MrExcel.
With that data would A-1 =3 & A-2 =2?

 

[carog]

Hi & welcome to MrExcel.
With that data would A-1 =3 & A-2 =2?

Yes, that's correct.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	A-1	User A		A-1	3
3	A-1	User B		A-2	2
4	A-1	User C		A-3	1
5	A-1	User A		A-4	2
6	A-1	User C
7	A-2	User A
8	A-2	User B
9	A-2	User A
10	A-3	User A
11	A-4	User A
12	A-4	User B
13	A-4	User B
14	A-4	User B
15	A-4	User A
16	A-4	User B
17	A-4	User A
18
Main

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(A2:A17)
E2:E5	E2	=ROWS(UNIQUE(FILTER($B$2:$B$17,$A$2:$A$17=D2)))
Dynamic array formulas.

 

[carog]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	A-1	User A		A-1	3
3	A-1	User B		A-2	2
4	A-1	User C		A-3	1
5	A-1	User A		A-4	2
6	A-1	User C
7	A-2	User A
8	A-2	User B
9	A-2	User A
10	A-3	User A
11	A-4	User A
12	A-4	User B
13	A-4	User B
14	A-4	User B
15	A-4	User A
16	A-4	User B
17	A-4	User A
18
Main

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(A2:A17)
E2:E5	E2	=ROWS(UNIQUE(FILTER($B$2:$B$17,$A$2:$A$17=D2)))
Dynamic array formulas.

Wow, that's great! That is really helpful. Just to take it a step farther (as I'm sure our customers will ask), would there be any way to identify the counts of assignments to specific users?

For example, looking up how many times case A-1 was assigned to User C. The answer using the above example for case A-1 would be 2.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1							User C	User A
2	A-1	User A		A-1	3	A-1	2	2
3	A-1	User B		A-2	2	A-4	0	3
4	A-1	User C		A-3	1
5	A-1	User A		A-4	2
6	A-1	User C
7	A-2	User A
8	A-2	User B
9	A-2	User A
10	A-3	User A
11	A-4	User A
12	A-4	User B
13	A-4	User B
14	A-4	User B
15	A-4	User A
16	A-4	User B
17	A-4	User A
Main

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(A2:A17)
G2:H3	G2	=IFERROR(ROWS(FILTER($B$2:$B$17,($B$2:$B$17=G$1)*($A$2:$A$17=$F2))),0)
E2:E5	E2	=ROWS(UNIQUE(FILTER($B$2:$B$17,$A$2:$A$17=D2)))
Dynamic array formulas.

 

[carog]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1							User C	User A
2	A-1	User A		A-1	3	A-1	2	2
3	A-1	User B		A-2	2	A-4	0	3
4	A-1	User C		A-3	1
5	A-1	User A		A-4	2
6	A-1	User C
7	A-2	User A
8	A-2	User B
9	A-2	User A
10	A-3	User A
11	A-4	User A
12	A-4	User B
13	A-4	User B
14	A-4	User B
15	A-4	User A
16	A-4	User B
17	A-4	User A
Main

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(A2:A17)
G2:H3	G2	=IFERROR(ROWS(FILTER($B$2:$B$17,($B$2:$B$17=G$1)*($A$2:$A$17=$F2))),0)
E2:E5	E2	=ROWS(UNIQUE(FILTER($B$2:$B$17,$A$2:$A$17=D2)))
Dynamic array formulas.

Perfect, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.