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
Salesman	EventType	Output
a1
a2
a3
a4
a5
a6
b1
b2
b3
b4
b5
b6
b7
c1
c2
c3
c4
c5
c6
c7
c8
c9
b1
b2
b3
b4
b5
b6
b8
c1
c2
c3
c4
c5
c6
c7

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Last 2 Event Types[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"]ab[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"]bc[/TD]
[TD="align: right"]62[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]b[/TD]
[TD="align: center"]cb[/TD]
[TD="align: right"]47[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]c[/TD]
[TD="align: center"]bc[/TD]
[TD="align: right"]39[/TD]

</tbody>

Sheet1​

Any help would be appreciated.

Thank you.

 

[Roghaltz]

Perfect! Thank you.

 

[jtakw]

You're welcome.