Hi,
I'm trying to get my head around this one. I have a table of sales data, and need to extract the names of customers who have made a purchase before 2011 but who have not made a purchase in 2011. See sample data.
To explain what is required on a filtered list, the name A Gazley should appear because they made a purchase prior to 2011, but not in 2011. (Same for A Williams and A Wilson.) Adam D made more than one purchase prior to 2011, but the name should appear only once. Adrian H made purchases both before 2011 and in 2011, so the name should not appear. So we'd get:
billing_name
A Gazley
A Williams
A Wilson
Adam D
Adam M
I'm thinking that the Advanced Filter will provide the answer but I'm not sure how to set up the filter criteria to get the right result. (If that's the way to go, the results should be copied to another location.)
On the other hand, there might be an alternative solution I haven't thought of.
Hoping someone can provide a simple solution for this.
Thanks,
I'm trying to get my head around this one. I have a table of sales data, and need to extract the names of customers who have made a purchase before 2011 but who have not made a purchase in 2011. See sample data.
orders extract_IJ.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | |||||||
2 | RawData | ||||||
3 | billing_name | date | $ | ||||
4 | AGazley | 20101205 | 103 | ||||
5 | AWilliams | 20100908 | 397 | ||||
6 | AWilson | 20101108 | 103 | ||||
7 | AdamD | 20091104 | 187 | ||||
8 | AdamD | 20091214 | 297 | ||||
9 | AdamD | 20100929 | 604 | ||||
10 | AdamM | 20101208 | 153 | ||||
11 | AdiC | 20091013 | 297 | ||||
12 | AdiC | 20110306 | 29 | ||||
13 | AdrianH | 20090503 | 360 | ||||
14 | AdrianH | 20091212 | 535 | ||||
15 | AdrianH | 20110207 | 210 | ||||
16 | AdrianH | 20110329 | 299 | ||||
17 | AdrianH | 20110421 | 438 | ||||
Sheet4 |
To explain what is required on a filtered list, the name A Gazley should appear because they made a purchase prior to 2011, but not in 2011. (Same for A Williams and A Wilson.) Adam D made more than one purchase prior to 2011, but the name should appear only once. Adrian H made purchases both before 2011 and in 2011, so the name should not appear. So we'd get:
billing_name
A Gazley
A Williams
A Wilson
Adam D
Adam M
I'm thinking that the Advanced Filter will provide the answer but I'm not sure how to set up the filter criteria to get the right result. (If that's the way to go, the results should be copied to another location.)
On the other hand, there might be an alternative solution I haven't thought of.
Hoping someone can provide a simple solution for this.
Thanks,