Find customers who switched from A to B

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
24
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,285
Office Version
  1. 365
Platform
  1. Windows
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.
 

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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
 

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ah! Ok I need a solution that you might've used back in the day. (I have 2013.)
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,285
Office Version
  1. 365
Platform
  1. Windows
Oh, wow. Didn't realize CONCAT was so new. Back to the drawing board.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,541

ADVERTISEMENT

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.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,285
Office Version
  1. 365
Platform
  1. Windows
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
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,285
Office Version
  1. 365
Platform
  1. Windows
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"))
 
Solution

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.

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"))
The sumproduct solution seems to have worked. Thanks!!
 

Forum statistics

Threads
1,144,162
Messages
5,722,848
Members
422,460
Latest member
VBA_Noob01

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
Top