Find customers who switched from A to B

B

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I want to find & count people who switched from apples to oranges. ??
I don't care about peaches, pears or bananas. (I don't care about Customer B who switched from Oranges to Peaches.)
I only want to find (out of tens of thousands of jumbled up transactions involving hundreds of customers buying from a selection of 10 fruits) any and all customers like A and E who were at least one-time (or maybe loyal) apple buyers, but decided to switch to oranges.
What we're looking for is the answer to this question: "We're discontinuing oranges. What current orange buyers can we convince to go back to their old habit of buying apples?"

Thanks for any help! (I can't seem to find the answer anywhere.)

1615484379908.png
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
How about this?

Book1 (version 2).xlsb
ABCDEFG
1Cust. IDDateProductCust. IDCount
2Customer A1/1/2020AppleCustomer A1
3Customer A2/1/2020AppleCustomer B2
4Customer A3/1/2020AppleCustomer C0
5Customer A4/1/2020OrangeCustomer D0
6Customer A5/1/2020OrangeCustomer E1
7Customer B6/1/2020Apple
8Customer B7/1/2020Orange
9Customer B8/1/2020Apple
10Customer B9/1/2020Orange
11Customer C10/1/2020Pear
12Customer D11/1/2020Banana
13Customer D12/1/2020Banana
14Customer D1/1/2021Orange
15Customer D2/1/2021Orange
16Customer E3/1/2021Apple
17Customer E4/1/2021Apple
18Customer E5/1/2021Orange
Sheet11
Cell Formulas
RangeFormula
G2:G6G2=(LEN(CONCAT(IF($A$2:$A$18=F2,$C$2:$C$18,"")))-LEN(SUBSTITUTE(CONCAT(IF($A$2:$A$18=F2,$C$2:$C$18,"")),"AppleOrange","@")))/10
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
OK, never in million years would I have come up with that. But...
I'm getting a #NAME? error.
I set everything up the same, substituted my actual bottom cell reference (A113824) for your A18.
In F I have all the Cust. ID numbers (with duplicates removed).
I replaced your concat of the dummy products "AppleOrange" with a concat of my actual two product codes SDP and CCP.
What am I missing?

=(LEN(CONCAT(IF($A$2:$A$113824=F2,$C$2:$C$113824,"")))-LEN(SUBSTITUTE(CONCAT(IF($A$2:$A$113824=F2,$C$2:$C$113824,"")),"SDPCCP","@")))/10
 
Upvote 0
Ah! Ok I need a solution that you might've used back in the day. (I have 2013.)
 
Upvote 0
Oh, wow. Didn't realize CONCAT was so new. Back to the drawing board.
 
Upvote 0
Maybe:

Book1
ABCDEFGH
1Cust. IDDateProductCust. IDCountApple before Orange?
2Customer A1/1/2020AppleCustomer A1TRUE
3Customer A2/1/2020AppleCustomer B2TRUE
4Customer A3/1/2020AppleCustomer C0FALSE
5Customer A4/1/2020OrangeCustomer D0FALSE
6Customer A5/1/2020OrangeCustomer E1TRUE
7Customer B6/1/2020Apple
8Customer B7/1/2020Orange
9Customer B8/1/2020Apple
10Customer B9/1/2020Orange
11Customer C10/1/2020Pear
12Customer D11/1/2020Banana
13Customer D12/1/2020Banana
14Customer D1/1/2021Orange
15Customer D2/1/2021Orange
16Customer E3/1/2021Apple
17Customer E4/1/2021Apple
18Customer E5/1/2021Orange
Sheet3
Cell Formulas
RangeFormula
G2:G6G2=(LEN(CONCAT(IF($A$2:$A$18=F2,$C$2:$C$18,"")))-LEN(SUBSTITUTE(CONCAT(IF($A$2:$A$18=F2,$C$2:$C$18,"")),"AppleOrange","@")))/10
H2:H6H2=IFERROR(MATCH(F2&"|apple",$A$2:$A$18&"|"&$C$2:$C$18,0)<MATCH(F2&"|orange",$A$2:$A$18&"|"&$C$2:$C$18,0),FALSE)
Press CTRL+SHIFT+ENTER to enter array formulas.


This formula just returns TRUE if it finds an Apple before it finds an Orange for a given customer. This might work for you, but there are lots of scenarios where it might not work. Like if a customer has Apple, Orange, Apple, Apple, Apple, Apple, etc. They might have tried Orange but stuck with Apple. This will return TRUE, but might not be what you want to see.
 
Upvote 0
If you have the PowerQuery Add-in, you can do this.

Book1 (version 2).xlsb
ABCDEF
1Cust. IDDateProductCust. IDCount
2Customer A1/1/2020SDPCustomer A1
3Customer A2/1/2020SDPCustomer B2
4Customer A3/1/2020SDPCustomer D0
5Customer A4/1/2020CCPCustomer E1
6Customer A5/1/2020CCP
7Customer B6/1/2020SDP
8Customer B7/1/2020CCP
9Customer B8/1/2020SDP
10Customer B9/1/2020CCP
11Customer C10/1/2020Pear
12Customer D11/1/2020Banana
13Customer D12/1/2020Banana
14Customer D1/1/2021CCP
15Customer D2/1/2021CCP
16Customer E3/1/2021SDP
17Customer E4/1/2021SDP
18Customer E5/1/2021CCP
Sheet11


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Cust. ID"}, {{"Count", each Table.AddIndexColumn(_,"Index",1), type table [Cust. ID=text, Date=datetime, Product=text]}}),
    IDX = Table.TransformColumns(Group,{{"Count",(tbl)=> Table.AddColumn(tbl,"TF", each tbl[Product]{[Index]})}}),
    Expand = Table.ExpandTableColumn(IDX, "Count", {"Date", "Product", "Index", "TF"}, {"Date", "Product", "Index", "TF"}),
    RE = Table.RemoveRowsWithErrors(Expand, {"TF"}),
    Count = Table.AddColumn(RE, "Custom", each Number.From([Product]="SDP" and [TF] = "CCP")),
    Total = Table.Group(Count, {"Cust. ID"}, {{"Count", each List.Sum([Custom]), type number}})
in
    Total
 
Upvote 0
Or like this should work.

Book1 (version 2).xlsb
ABCDEFG
1Cust. IDDateProductCust. IDCount
2Customer A1/1/2020SDPCustomer A1
3Customer A2/1/2020SDPCustomer B2
4Customer A3/1/2020SDPCustomer C0
5Customer A4/1/2020CCPCustomer D0
6Customer A5/1/2020CCPCustomer E1
7Customer B6/1/2020SDP
8Customer B7/1/2020CCP
9Customer B8/1/2020SDP
10Customer B9/1/2020CCP
11Customer C10/1/2020Pear
12Customer D11/1/2020Banana
13Customer D12/1/2020Banana
14Customer D1/1/2021CCP
15Customer D2/1/2021CCP
16Customer E3/1/2021SDP
17Customer E4/1/2021SDP
18Customer E5/1/2021CCP
Sheet14
Cell Formulas
RangeFormula
G2:G6G2=SUMPRODUCT(($A$2:$A$18=F2)*($A$3:$A$19=F2)*($C$2:$C$18="SDP")*($C$3:$C$19="CCP"))
 
Upvote 0
Solution
Eric W said:
Maybe:

Book1
ABCDEFGH
1Cust. IDDateProductCust. IDCountApple before Orange?
2Customer A1/1/2020AppleCustomer A1TRUE
3Customer A2/1/2020AppleCustomer B2TRUE
4Customer A3/1/2020AppleCustomer C0FALSE
5Customer A4/1/2020OrangeCustomer D0FALSE
6Customer A5/1/2020OrangeCustomer E1TRUE
7Customer B6/1/2020Apple
8Customer B7/1/2020Orange
9Customer B8/1/2020Apple
10Customer B9/1/2020Orange
11Customer C10/1/2020Pear
12Customer D11/1/2020Banana
13Customer D12/1/2020Banana
14Customer D1/1/2021Orange
15Customer D2/1/2021Orange
16Customer E3/1/2021Apple
17Customer E4/1/2021Apple
18Customer E5/1/2021Orange
Sheet3
Cell Formulas
RangeFormula
G2:G6G2=(LEN(CONCAT(IF($A$2:$A$18=F2,$C$2:$C$18,"")))-LEN(SUBSTITUTE(CONCAT(IF($A$2:$A$18=F2,$C$2:$C$18,"")),"AppleOrange","@")))/10
H2:H6H2=IFERROR(MATCH(F2&"|apple",$A$2:$A$18&"|"&$C$2:$C$18,0)<MATCH(F2&"|orange",$A$2:$A$18&"|"&$C$2:$C$18,0),FALSE)
Press CTRL+SHIFT+ENTER to enter array formulas.


This formula just returns TRUE if it finds an Apple before it finds an Orange for a given customer. This might work for you, but there are lots of scenarios where it might not work. Like if a customer has Apple, Orange, Apple, Apple, Apple, Apple, etc. They might have tried Orange but stuck with Apple. This will return TRUE, but might not be what you want to see.
Click to expand...

lrobbo314 said:
Or like this should work.

Book1 (version 2).xlsb
ABCDEFG
1Cust. IDDateProductCust. IDCount
2Customer A1/1/2020SDPCustomer A1
3Customer A2/1/2020SDPCustomer B2
4Customer A3/1/2020SDPCustomer C0
5Customer A4/1/2020CCPCustomer D0
6Customer A5/1/2020CCPCustomer E1
7Customer B6/1/2020SDP
8Customer B7/1/2020CCP
9Customer B8/1/2020SDP
10Customer B9/1/2020CCP
11Customer C10/1/2020Pear
12Customer D11/1/2020Banana
13Customer D12/1/2020Banana
14Customer D1/1/2021CCP
15Customer D2/1/2021CCP
16Customer E3/1/2021SDP
17Customer E4/1/2021SDP
18Customer E5/1/2021CCP
Sheet14
Cell Formulas
RangeFormula
G2:G6G2=SUMPRODUCT(($A$2:$A$18=F2)*($A$3:$A$19=F2)*($C$2:$C$18="SDP")*($C$3:$C$19="CCP"))
Click to expand...
The sumproduct solution seems to have worked. Thanks!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Formula to work out the average value of the most recent entries
2
Replies
10
Views
1K
Jonny_71
J
S
  • Question
Dynamic multi-level sorting with array formulas
Replies
6
Views
7K
squishy2010
S
C
  • Question
Filter results according to a list of values generated from another filter
Replies
1
Views
303
MickG
M

Forum statistics

Threads
1,214,924
Messages
6,122,293
Members
449,077
Latest member
Rkmenon

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top