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.

 

[jtakw]

Hi,

Actually, I don't think the solution is as easy as you might think, at least not for me.

My solution requires you to add a helper column, Column D in my sample below.
Copy formula in D2 down, and formula in C2 down, then hide Column D.

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

Cell Formulas
Range	Formula
C2	=IF(AND(LEN(D2)>1,D2<>D3),D2,"")
D2	=IF(D1="",B2,IF(B2<>B1,B1&B2,D1))

PS. No idea how your Column E Output numbers work, they don't seem to add up.

 

[Roghaltz]

Thank you. That worked great.

The output column comes from somewhere else in the model, forgot to mention that part.You answered my question completely.

Thanks again.

 

[Roghaltz]

Actually, was just looking it over more carefully and it works well as long the same event type doesn't occur twice in a row. Unfortunately, In my model it often does (i.e. aa, bb, cc). In these cases Column C is blank. Should have mentioned that.

Any thoughts?

Thanks.

 

[jtakw]

Can you show a sample of these occurrences?
Does the Salesman column (A) change accordingly?

 

[Roghaltz]

Here is an example. I also have a DateID column (added below) to show that every event is unique (i.e. there were back to back "a" events below, but one was in October and then one in November). Thus, the first pairing is "aa".

Excel 2010

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Hope this helps. Let me know if you need additional info.

 

[jtakw]

Hi Roghaltz,

Thanks for providing the additional info, that helps.

Use the following updated formulas, let me know if it works for your scenarios, and if you have further requirements.

Excel 2010
	A	B	C	D	E	F
1	Date	Salesman	EventType	DateEventID	Last 2 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		ab
16	15-Nov	b2	b	Nov-15-b		ab
17	15-Nov	b3	b	Nov-15-b		ab
18	15-Nov	b4	b	Nov-15-b		ab
19	15-Nov	b5	b	Nov-15-b		ab
20	15-Nov	b6	b	Nov-15-b		ab
21	15-Nov	b7	b	Nov-15-b	ab	ab
22	15-Nov	c1	c	Nov-15-c		bc
23	15-Nov	c2	c	Nov-15-c		bc
24	15-Nov	c3	c	Nov-15-c		bc
25	15-Nov	c4	c	Nov-15-c		bc
26	15-Nov	c5	c	Nov-15-c		bc
27	15-Nov	c6	c	Nov-15-c		bc
28	15-Nov	c7	c	Nov-15-c		bc
29	15-Nov	c8	c	Nov-15-c		bc
30	15-Nov	c9	c	Nov-15-c	bc	bc
31	15-Nov	b1	b	Nov-15-b		cb
32	15-Nov	b2	b	Nov-15-b		cb
33	15-Nov	b3	b	Nov-15-b		cb
34	15-Nov	b4	b	Nov-15-b		cb
35	15-Nov	b5	b	Nov-15-b		cb
36	15-Nov	b6	b	Nov-15-b		cb
37	15-Nov	b8	b	Nov-15-b	cb	cb
Sheet2

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

Copy F2 formula down, E2 formula down, hide Column F.

 

[jtakw]

Hi again,

Ignore the above post, it won't work if you have the same event 3 times or more in a row, I've made changes to the formula, use these instead.
These should work even when you have the same event occurring more than twice in a row, as long as the dates are different, let me know if it works for your scenarios, and if you have further requirements.

Excel 2010
	A	B	C	D	E	F
1	Date	Salesman	EventType	DateEventID	Last 2 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		ab
16	15-Nov	b2	b	Nov-15-b		ab
17	15-Nov	b3	b	Nov-15-b		ab
18	15-Nov	b4	b	Nov-15-b		ab
19	15-Nov	b5	b	Nov-15-b		ab
20	15-Nov	b6	b	Nov-15-b		ab
21	15-Nov	b7	b	Nov-15-b	ab	ab
22	15-Nov	c1	c	Nov-15-c		bc
23	15-Nov	c2	c	Nov-15-c		bc
24	15-Nov	c3	c	Nov-15-c		bc
25	15-Nov	c4	c	Nov-15-c		bc
26	15-Nov	c5	c	Nov-15-c		bc
27	15-Nov	c6	c	Nov-15-c		bc
28	15-Nov	c7	c	Nov-15-c		bc
29	15-Nov	c8	c	Nov-15-c		bc
30	15-Nov	c9	c	Nov-15-c	bc	bc
31	15-Nov	b1	b	Nov-15-b		cb
32	15-Nov	b2	b	Nov-15-b		cb
33	15-Nov	b3	b	Nov-15-b		cb
34	15-Nov	b4	b	Nov-15-b		cb
35	15-Nov	b5	b	Nov-15-b		cb
36	15-Nov	b6	b	Nov-15-b		cb
37	15-Nov	b8	b	Nov-15-b	cb	cb
Sheet2

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

Copy F2 formula down, E2 formula down, hide Column F.

 

[Roghaltz]

That worked perfectly. I appreciate your help!

 

[jtakw]

Glad it all worked out.