CODEWITHSUNDEEP
excelexcel-formula
Joe Johnson
Joe Johnson

Reputation: 73

How to identiy new/repeat customers

I have a list of ~55k customer sales. I need to create a column to identify if a sale was repeat or new business based on customer name. I have columns A/B and I want to create column C as shown below:

Excel Sample

Upvotes: 0

Views: 780

Answers (3)

VBasic2008
VBasic2008

Reputation: 54873

MINIFS Beats COUNTIFS?

If there is only one first date associated with each customer then the data has not to be sorted and you can use the following:

=IF(A2>MINIFS($A:$A,$B:$B,B2),"Repeat","New")

Upvotes: 0

JvdV
JvdV

Reputation: 75940

Alternatively:

=IF(COUNTIF(B$2:B2,B2)>1,"Repeat","New")

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152595

This assumes that Column A is sorted ascending:

Put this in C2 and copy down:

=IF(ISNUMBER(MATCH(B2,$B$1:B1,)),"Repeat", "New")

If the data is not sorted we need to switch to COUNTIFS(), which is not as optimized as MATCH and will cause issues in the calc if the data set it too large(>10,000)

=IF(COUNTIFS($A:$A,"<"&A2,$B:$B,B2),Repeat", "New")

Again put that in C2 and copy down the dataset.

Upvotes: 1

Related Questions