Formula Challenge: Find the First Transaction for Each Month

Hi

I tried using Hepler Column:

=INDEX(tblSales2[[#All],[Sales Person]],MATCH(CONCATENATE(CHOOSE(B8,”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”),”|”,1),tblSales2[[#All],[Unique Key]],0))

This seems to do the trick (as an array formula):

{=INDEX(tblSales[Sales Person],
MATCH(MIN(IF((MONTH(tblSales[Date])=tblFirstSale[@Month])*(tblSales[Date])
0,tblSales[Date])
),
tblSales[Date],
0)
)}

A short array formula:

{=VLOOKUP(MIN(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date])),tblSales[[Date]:[Sales Person]],2,FALSE)}

{=INDEX(tblSales,MATCH(MIN(IF(MONTH(tblSales[Date])=INDEX(B:B,ROW()),tblSales[Date])),tblSales[Date],0),3)}

Must be entered as an array formula!

This is my second submission since I did not see my earlier one posted. This works when entered as and array (CTRL-SHIFT-ENTER)

=VLOOKUP(MIN(IF(MONTH(tblSales[Date])=$B8,tblSales[Date])),tblSales[[Date]:[Sales Person]],2,0)

Hi,

Here’s my solution an array formula:
=INDEX(tblSales[Sales Person],MATCH(MIN(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date])),tblSales[Date],FALSE))

I did it with a Power Query query and I had a tie also in April

{=VLOOKUP(MIN(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date])),tblSales[[Date]:[Sales Person]],2,0)}

=INDEX(tblSales[Sales Person],MATCH(SMALL(tblSales[Date],COUNTIF(tblSales[Date],”<"&DATE(2015,[@Month],1))+1),tblSales[Date],0))

This is how I solved it, have never used the SMALL function before but it was useful here.

Hi Jon,

My formula would be an array formula:

{=INDEX(tblSales[Sales Person],MATCH(SMALL(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date],””),1),tblSales[Date],0))}

I have added the {} to the above to clarify that it is an array.

Here we go, that was fun!

=INDEX(tblSales[Sales Person],MATCH(MIN(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date],FALSE)),tblSales[Date],0),1)

Hi Jon & XLarium & Everyone,

Here is my array:
=INDEX(tblSales3[Sales Person],MATCH(MIN(IF(MONTH(tblSales3[Date])=[@Month],tblSales3[Date],””)),IF(MONTH(tblSales3[Date])=[@Month],tblSales3[Date],””),0))

Thanks for the Excel puzzle Jon!
Cheers,
Kevin

Here you go:

=INDEX(tblSales[Sales Person],MATCH(MIN(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date],””)),tblSales[Date],0))

Used as an Array of course.

Cheers,

Adrian

=VLOOKUP(MINIFS($F$8:$F$1007,$F$8:$F$1007,”=”&DATE(YEAR($F$8),B8,1)),$F$8:$G$1007,2,FALSE)

Relies on year in the first row of data as well as MINIFS function from Office 2016/365.

I’ve got this – array formula (CSE entered):

=INDEX(tblSales[Sales Person],MATCH(MIN(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date],”A”)),tblSales[Date],0))

Then realised it was basically the same as Deniz Aksen’s. Hey ho!

Another way you can get the result by using LOOKUP function.

with CSE

=LOOKUP(2,1/(MIN(IF((MONTH(tblSales[Date])=[@Month])*tblSales[Date]>0,(MONTH(tblSales[Date])=[@Month])*tblSales[Date]))=tblSales[Date]),tblSales[Sales Person])

Jon,

My solution:

=ÍNDEX(tblSales[Sales Person];CORRESP(MIN(IF(MONTH(tblSales[Date])=B8;tblSales[Date]);1);tblSales[Date];0))

Hi Jon,

I’m in the habit of trying to avoid array formulas because no one else in my office knows what they are or how they work. That added another level of challenge. I also noticed that there was a TIE for the first sale in April, and it wasn’t specified if the first transaction ID was actually first, so I built in a contingency for a tie, to return the two people who technically could have had the first sale based on the date/time stamp.

Here is the formula for C8:

=IF(COUNTIF(tblSales[Date],MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1)))>1,CONCATENATE(COUNTIF(tblSales[Date],MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1))),”-Way Tie Between “,INDEX(tblSales[#All],MATCH(MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1)),tblSales[[#All],[Date]],0),MATCH(tblFirstSale[[#Headers],[Sales Person]],tblSales[#Headers],0)),” & “,INDEX(tblSales[#All],MATCH(MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1)),tblSales[[#All],[Date]],0)+MATCH(MINIFS(OFFSET(tblSales[Date],MATCH(MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1)),tblSales[[#All],[Date]],0),0),OFFSET(tblSales[Date],MATCH(MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1)),tblSales[[#All],[Date]],0),0),”>=”&DATE(2015,B8,1)),OFFSET(tblSales[[#All],[Date]],MATCH(MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1)),tblSales[[#All],[Date]],0),0),0),MATCH(tblFirstSale[[#Headers],[Sales Person]],tblSales[#Headers],0))),INDEX(tblSales[#All],MATCH(MINIFS(tblSales[Date],tblSales[Date],”>=”&DATE(2015,B8,1)),tblSales[[#All],[Date]],0),MATCH(tblFirstSale[[#Headers],[Sales Person]],tblSales[#Headers],0)))

Breaking it down –
The first step was to identify the date of the first sale in the given month. I used the minifs function to identify the smallest date which was greater than or equal to the date with 2015 as the year, the variable in column B as month, and the 1st as the day.

The second step was to use an index match to find that date and return the Sales Person.

The third step (which if I was Samuel Vasquez I would protest heavily was missing!!!) was to check to see if there were any repeating dates/times that would indicate a tie. I used the count function to test if the smallest date I found was unique. If it WASN’T unique, a x-way tie was identified, and I used concatenate to return a count of duplicate values (x), the name of the first salesperson I had already found, and the name of the second with the repeated date. Since I decided not to use an array formula, I used “offset” to set the index range just below the first match value for the second match. Technically this formula could be expanded to accommodate a 3-way, 4-way, ect tie, but I figured since a 2 way tie was rare enough, it would be enough for the formula to identify any “x-Way Tie Between”s and for someone to manually enter any additional names beyond the first two (rather than further over complicate the formula).

Thanks for the challenge 🙂 Love this stuff!!