Hi folks,
I need a formula column to aggregate the codes billed per customer visit (solution would look like E2:E10 below). Then later I can include the results in a PivotTable to see the combo frequency of codes billed at this store.
There is a large list of customer transactions. Each row includes a specific 4 character billing code that the customer was charged. Some customers may have many rows for a single visit (e.g. 6 or 7 distinct billing codes). Other customers may have just 1 row.
I've tried variations with FREQUENCY, SIGN and nested IF's with no luck. I'm sure there's a VBA solution using a For each loop, but I'm not there yet to figure out.
I've already prepped the data; it's filtered by customer name and a helper column is included to mark a "1" when it's a distinct customer visit (i.e. subsequent rows immediately below would be blank if it's the same customer/same day).
<tbody>
</tbody>
Many thanks for reading through and your time!
James
I need a formula column to aggregate the codes billed per customer visit (solution would look like E2:E10 below). Then later I can include the results in a PivotTable to see the combo frequency of codes billed at this store.
There is a large list of customer transactions. Each row includes a specific 4 character billing code that the customer was charged. Some customers may have many rows for a single visit (e.g. 6 or 7 distinct billing codes). Other customers may have just 1 row.
I've tried variations with FREQUENCY, SIGN and nested IF's with no luck. I'm sure there's a VBA solution using a For each loop, but I'm not there yet to figure out.
I've already prepped the data; it's filtered by customer name and a helper column is included to mark a "1" when it's a distinct customer visit (i.e. subsequent rows immediately below would be blank if it's the same customer/same day).
A | B | C | D | E | |
1 | Customer | Date | Distinct customer visit? (yes = 1) | Code billed | Codes billed per customer visit |
2 | ABC | Sep 01 | 1 | 499J | 499J, 550M |
3 | ABC | Sep 01 | 550M | ||
4 | DEF | Sep 02 | 1 | 425H | 425H |
5 | DEF | Sep 05 | 1 | 690B | 690B, 701T, 499J, 880Q |
6 | DEF | Sep 05 | 701T | ||
7 | DEF | Sep 05 | 499J | ||
8 | DEF | Sep 05 | 880Q | ||
9 | GHH | Sep 08 | 1 | 701T | 701T, 499J |
10 | GHH | Sep 08 | 499J |
<tbody>
</tbody>
Many thanks for reading through and your time!
James