How to calculate Client Repeat Rate? (If any row in a column containing a name corresponds to a particular value then....)

Question

I have a list of deals (usually unsorted) and I'm trying to find out if a company is a new client or a repeat client for each year (2019, 2020, & 2021).

The data is present as follows:

Company	Year
Company 1	2019
Company 2	2019
Company 2	2020
Company 2	2020
Company 3	2020
Company 3	2020
Company 2	2021
Company 4	2021

I need it to be calculated as:

Company	2019	2020	2021
Company 1	New	Lost	Lost
Company 2	New	Repeat	Repeat
Company 3	-	New	Lost
Company 4	-	-	New

I've created the desired outcome by creating a couple of helper columns, but how do I get the outcome without those? You can view the current work here: https://docs.google.com/spreadsheets/d/1jRTb1X4mFpLqyqADayqame-jWe2LoFizmARc7OCF424/edit#gid=1425919264

Struggling with using INDEX MATCH or VLOOKUP, specifically -> If [Company Name] is first signed in 2019, and any row in Column A containing [Company Name] corresponds to 2020, then [Company Name] is "Repeat" else....

Any help is appreciated.

Answer 1

In legacy Excel, this must be entered as an array formula

In Google sheets (assuming the same cell references) then the array aspect must also be made explicit, i.e.

=ARRAYFORMULA(IF(ISNA(MATCH($D2&E$1,$A$2:$A$9&$B$2:$B$9,0)),IF(OR(D2="New",D2="Lost"),"Lost","-"),IF(OR(D2="New",D2="Repeat"),"Repeat","New")))

Answer 2

Filter is Your Friend

On a separate sheet, in cell A1 as a heading lets place the word company and in B1 lets place status.

Now in cell A2 place =UNIQUE('Your Sheet Name'A2:A) to get a unique list of company names. Assuming your company names are on column A and start on the second row.

Before the next part, lets place the year somewhere in the sheet. Lets do in cell D1 and to the left in cell C1 lets type in "Year" for clarification.

Now in cell B2:B1000 put =IF(A2 = "", ,IF(INDEX(SORT(FILTER(Sheet1!$A$2:$B,Sheet1!$A$2:$A = A2),2,FALSE),1,2) >= $D$1,IF(COUNTIF(Sheet1!$A$2:$A,A2) > 1,"Recurring","New"),"Lost"))

We basically are saying If the cell to the left of this formula is not blank and the most recent entry is greater than or equal to the year I placed in D1 then I want to see if this is the first time we have this company in our dataset

If it is, then it would be new development, if not then it would be recurring. And if the most recent entry for this company is not greater than or equal to the year in D1 then it was lost.

Answer 3

grouped deals can be done like:

=INDEX(1*QUERY(A3:C, "select count(A) where A is not null group by A pivot C"))

and status like:

=ARRAYFORMULA(QUERY(IF(ISNUMBER(
 QUERY(A3:C, "select count(A) where A is not null group by A pivot C"))*1=0, "lost", IF(ISNUMBER(
 QUERY(A3:C, "select count(A) where A is not null group by A pivot C"))*1>ISNUMBER(
 QUERY(A3:C, "select A,count(A) where A is not null group by A pivot C"))*1, "new", "repeat")), "offset 1", ))