Hello,
Been scratching my head with this one this morning and hope - true to form - you have the answer.
Dummy data (sorted by Purchase Date/Time):
We're needing to isolate purchases that occur after a voucher has been used. We'll add "Transaction" column ("Before Voucher"/"After Voucher"/"Voucher") and delete the "Before Voucher" items.
In the data above, Row 10 and Rows 18-19 are Transactions that happened after a Voucher Purchase. Rows 2-7 are not.
For customer Lily Edwards, we're not interested in seeing Row 16 (before Voucher Purchase), but do care about Row 18 and 19 (and 17 because it has the voucher purchase). We're not interested in seeing customers where there are no Voucher Purchases (e.g. Cathy Brown).
If there are multiple Voucher Purchases (like John Williams), we're interested in all Transaction occurring after a Voucher has been purchased, so we'd not care for Row 11, but we do for Rows 12-15 (Purchases after the first Voucher purchase, including the Voucher purchases themselves).
All my (feeble) attempts have ended in frustration, so hoping you guys can help!
Thanks,
Kree
Been scratching my head with this one this morning and hope - true to form - you have the answer.
Dummy data (sorted by Purchase Date/Time):
Excel 2007 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Customer Name | First Name | Last Name | Purchase Date | Amount | Item | TYPE | ||
2 | Customer F | Roger | Jones | 5/30/2012 16:58 | 11 | BBBB | Cash Purchase | ||
3 | Customer F | Roger | Jones | 6/2/2012 21:03 | 11 | BBBB | Cash Purchase | ||
4 | Customer F | Roger | Jones | 6/3/2012 17:13 | 10 | CCCC | Cash Purchase | ||
5 | Customer F | Roger | Jones | 6/4/2012 18:17 | 10 | DDDD | Cash Purchase | ||
6 | Customer F | Roger | Jones | 6/5/2012 19:18 | 11 | EEEE | Cash Purchase | ||
7 | Customer F | Roger | Jones | 6/6/2012 19:01 | 10 | BBBB | Cash Purchase | ||
8 | Customer F | Roger | Jones | 6/6/2012 22:10 | 355 | BBBB | Voucher Purchase | ||
9 | Customer A | Bob | Smith | 6/1/2012 22:54 | 20 | AAAA | Voucher Purchase | ||
10 | Customer A | Bob | Smith | 6/1/2012 22:56 | 30 | BBBB | Cash Purchase | ||
11 | Customer C | John | Williams | 5/31/2012 9:08 | 30 | EEEE | Cash Purchase | ||
12 | Customer C | John | Williams | 6/2/2012 15:57 | 400 | DDDD | Voucher Purchase | ||
13 | Customer C | John | Williams | 6/5/2012 3:46 | 30 | EEEE | Cash Purchase | ||
14 | Customer C | John | Williams | 6/4/2012 22:33 | 50 | DDDD | Voucher Purchase | ||
15 | Customer C | John | Williams | 6/5/2012 22:18 | 800 | DDDD | Voucher Purchase | ||
16 | Customer D | Lily | Edwards | 6/5/2012 3:46 | 30 | DDDD | Cash Purchase | ||
17 | Customer D | Lily | Edwards | 6/6/2012 1:41 | 500 | BBBB | Voucher Purchase | ||
18 | Customer D | Lily | Edwards | 6/7/2012 7:06 | 100 | BBBB | Cash Purchase | ||
19 | Customer D | Lily | Edwards | 6/7/2012 8:05 | 100 | EEEE | Cash Purchase | ||
20 | Customer G | Cathy | Brown | 6/5/2012 11:04 | 20 | BBBB | Cash Purchase | ||
Sheet1 |
We're needing to isolate purchases that occur after a voucher has been used. We'll add "Transaction" column ("Before Voucher"/"After Voucher"/"Voucher") and delete the "Before Voucher" items.
In the data above, Row 10 and Rows 18-19 are Transactions that happened after a Voucher Purchase. Rows 2-7 are not.
For customer Lily Edwards, we're not interested in seeing Row 16 (before Voucher Purchase), but do care about Row 18 and 19 (and 17 because it has the voucher purchase). We're not interested in seeing customers where there are no Voucher Purchases (e.g. Cathy Brown).
If there are multiple Voucher Purchases (like John Williams), we're interested in all Transaction occurring after a Voucher has been purchased, so we'd not care for Row 11, but we do for Rows 12-15 (Purchases after the first Voucher purchase, including the Voucher purchases themselves).
All my (feeble) attempts have ended in frustration, so hoping you guys can help!
Thanks,
Kree