Finding a rolling pair of values in a long list

[Roghaltz]

Hello all,
I wasn't sure exactly how to describe this problem and I suspect the solution is simple (however, I have been pulling my hair out trying to find it). We have a bunch of salesmen participating in a variety of sales events. We are trying to find the sum of the outputs by pairs of event types on a rolling basis.

There are three main event types (a,b,c in Column B). The first two events in the example below are an "a" and a "b", thus the first pair (and desired output) is "ab" (in column C). Ultimately I would like to sum the output based on the Last 2 Event Types (i.e. ab= ..., ac=..., ba=..., etc). This is easy using sumif once I have the "Last 2 Event Types" in column C.

There can be a different number of salesmen in each event (usually 6-10). The event types occur in pretty much random order.

There many events in column B so I don't want do Last 2 Event Types (Column C) by hand (if possible).

Excel 2010

	A	B	C	D
1	Salesman	EventType	Last 2 Event Types	Output
2	a1	a
3	a2	a
4	a3	a
5	a4	a
6	a5	a
7	a6	a
8	b1	b
9	b2	b
10	b3	b
11	b4	b
12	b5	b
13	b6	b
14	b7	b	ab	50
15	c1	c
16	c2	c
17	c3	c
18	c4	c
19	c5	c
20	c6	c
21	c7	c
22	c8	c
23	c9	c	bc	62
24	b1	b
25	b2	b
26	b3	b
27	b4	b
28	b5	b
29	b6	b
30	b8	b	cb	47
31	c1	c
32	c2	c
33	c3	c
34	c4	c
35	c5	c
36	c6	c
37	c7	c	bc	39

<tbody>
</tbody>

Sheet1​

Any help would be appreciated.

Thank you.

 

[Roghaltz]

Hi jtakw,
Thanks again for your help. Would it be difficult to extend the above to 3 rolling events? (i.e. E21 would now be "aab" and E30 would now be "abc", E37 would be "bcb" etc?)

Any assistance would be appreciated.

 

[jtakw]

You're welcome, and Welcome back.

Not too difficult, I've updated the formulas for your new request, if you need to include more rolling events in the future (i.e. 4, 5, 6, etc.), you can change the number (highlighted Red) within the formula:

For 2 events: =IF(AND(LEN(G2)>1,D2<>D3),RIGHT(G2,2),"")

For 3 events: =IF(AND(LEN(G2)>1,D2<>D3),RIGHT(G2,3),"")

Etc.

Excel 2010
	A	B	C	D	E	F	G
1	Date	Salesman	EventType	DateEventID	Last 2 Event Types	Last 3 Event Types
2	15-Oct	a1	a	Oct-15-a			a
3	15-Oct	a2	a	Oct-15-a			a
4	15-Oct	a3	a	Oct-15-a			a
5	15-Oct	a4	a	Oct-15-a			a
6	15-Oct	a5	a	Oct-15-a			a
7	15-Oct	a6	a	Oct-15-a			a
8	15-Nov	a1	a	Nov-15-a			aa
9	15-Nov	a2	a	Nov-15-a			aa
10	15-Nov	a3	a	Nov-15-a			aa
11	15-Nov	a4	a	Nov-15-a			aa
12	15-Nov	a5	a	Nov-15-a			aa
13	15-Nov	a6	a	Nov-15-a			aa
14	15-Nov	a7	a	Nov-15-a	aa	aa	aa
15	15-Nov	b1	b	Nov-15-b			aab
16	15-Nov	b2	b	Nov-15-b			aab
17	15-Nov	b3	b	Nov-15-b			aab
18	15-Nov	b4	b	Nov-15-b			aab
19	15-Nov	b5	b	Nov-15-b			aab
20	15-Nov	b6	b	Nov-15-b			aab
21	15-Nov	b7	b	Nov-15-b	ab	aab	aab
22	15-Nov	c1	c	Nov-15-c			aabc
23	15-Nov	c2	c	Nov-15-c			aabc
24	15-Nov	c3	c	Nov-15-c			aabc
25	15-Nov	c4	c	Nov-15-c			aabc
26	15-Nov	c5	c	Nov-15-c			aabc
27	15-Nov	c6	c	Nov-15-c			aabc
28	15-Nov	c7	c	Nov-15-c			aabc
29	15-Nov	c8	c	Nov-15-c			aabc
30	15-Nov	c9	c	Nov-15-c	bc	abc	aabc
31	15-Nov	b1	b	Nov-15-b			aabcb
32	15-Nov	b2	b	Nov-15-b			aabcb
33	15-Nov	b3	b	Nov-15-b			aabcb
34	15-Nov	b4	b	Nov-15-b			aabcb
35	15-Nov	b5	b	Nov-15-b			aabcb
36	15-Nov	b6	b	Nov-15-b			aabcb
37	15-Nov	b8	b	Nov-15-b	cb	bcb	aabcb
Sheet1

Cell Formulas
Range	Formula
E2	=IF(AND(LEN(G2)>1,D2<>D3),RIGHT(G2,2),"")
F2	=IF(AND(LEN(G2)>1,D2<>D3),RIGHT(G2,3),"")
G2	=IF(G1="",C2,IF(D2<>D1,G1&C2,G1))

 

[Roghaltz]

That worked great! Thanks!

 

[jtakw]

That worked great! Thanks!

You're Welcome.

 

[Roghaltz]

Ran into a minor snafu in column G. The cells in Column G seems to get wider & wider as more items are appended to it. I have roughly 1,000 events in my dataset so eventually column G reaches its max capacity before the end of the dataset. Any way to trim it to keep that from happening?

Thanks!

 

[jtakw]

How many event types are we going to max out at? Is it 3, or ??

 

[Roghaltz]

There are 6 event types total (a-f). I am interested in the last 3 events (as noted earlier). The same event can occur all three times (i.e. aaa) or there can be any mix of the event types (i.e. abd, dbf, aaf, etc)

 

[jtakw]

Do you still want to be able to do 2 event types also, or you only want 3?

 

[Roghaltz]

Only 3 now.

 

[jtakw]

Only 3 now.

Excel 2010
	A	B	C	D	E	F
1	Date	Salesman	EventType	DateEventID	Last 3 Event Types
2	15-Oct	a1	a	Oct-15-a		a
3	15-Oct	a2	a	Oct-15-a		a
4	15-Oct	a3	a	Oct-15-a		a
5	15-Oct	a4	a	Oct-15-a		a
6	15-Oct	a5	a	Oct-15-a		a
7	15-Oct	a6	a	Oct-15-a		a
8	15-Nov	a1	a	Nov-15-a		aa
9	15-Nov	a2	a	Nov-15-a		aa
10	15-Nov	a3	a	Nov-15-a		aa
11	15-Nov	a4	a	Nov-15-a		aa
12	15-Nov	a5	a	Nov-15-a		aa
13	15-Nov	a6	a	Nov-15-a		aa
14	15-Nov	a7	a	Nov-15-a	aa	aa
15	15-Nov	b1	b	Nov-15-b		aab
16	15-Nov	b2	b	Nov-15-b		aab
17	15-Nov	b3	b	Nov-15-b		aab
18	15-Nov	b4	b	Nov-15-b		aab
19	15-Nov	b5	b	Nov-15-b		aab
20	15-Nov	b6	b	Nov-15-b		aab
21	15-Nov	b7	b	Nov-15-b	aab	aab
22	15-Nov	c1	c	Nov-15-c		abc
23	15-Nov	c2	c	Nov-15-c		abc
24	15-Nov	c3	c	Nov-15-c		abc
25	15-Nov	c4	c	Nov-15-c		abc
26	15-Nov	c5	c	Nov-15-c		abc
27	15-Nov	c6	c	Nov-15-c		abc
28	15-Nov	c7	c	Nov-15-c		abc
29	15-Nov	c8	c	Nov-15-c		abc
30	15-Nov	c9	c	Nov-15-c	abc	abc
31	15-Nov	b1	b	Nov-15-b		bcb
32	15-Nov	b2	b	Nov-15-b		bcb
33	15-Nov	b3	b	Nov-15-b		bcb
34	15-Nov	b4	b	Nov-15-b		bcb
35	15-Nov	b5	b	Nov-15-b		bcb
36	15-Nov	b6	b	Nov-15-b		bcb
37	15-Nov	b8	b	Nov-15-b	bcb	bcb
Sheet2

Cell Formulas
Range	Formula
E2	=IF(AND(LEN(F2)>1,D2<>D3),F2,"")
F2	=IF(F1="",C2,IF(D2<>D1,RIGHT(F1,2)&C2,F1))