Formula Challenge: Find the First Transaction for Each Month

=INDEX(tblSales[Sales Person],MATCH(AGGREGATE(15,6,(–(MONTH(tblSales[Date])=[@Month])*tblSales[Date]/–(MONTH(tblSales[Date])=[@Month])),1),tblSales[Date],0))

The key is the AGGREGATE function, which finds the earliest date for the current month.
AGGREGATE(15,6,(–(MONTH(tblSales[Date])=[@Month])*tblSales[Date]/–(MONTH(tblSales[Date])=[@Month]))

Using the AGGREGATE formula means you don’t need to enter this as an array formula.

The Aggregate expression has arguments 15, which uses SMALL and 6, which says ignore errors. The fourth argument,1, says get the smallest value from the array.

The array is: (–(MONTH(tblSales[Date])=[@Month])*tblSales[Date]/–(MONTH(tblSales[Date])=[@Month])),1)

The numerator gives dates in the current month and zeros otherwise. Since the AGGREGATE is looking for the smallest value, just using the numerator would give the result 0 for each month. So divide by the test that the month of the sale = current month, this will be zero for all dates but current months, giving #div0 values for all dates in the array not in the current month. These are ignored by the AGGREGATE function (due to second argument) so it will find the smallest (earliest) date from the current month.

Having found the earliest date in the current month, find its row number with the MATCH and then the corresponding sales name with INDEX>

Thanks to Mike Girvin (ExcelsFun) who demonstrated the technique in one of his excellent YouTube videos

I have used this (to be entered as array through Ctrl Shift Enter). This can be modified to use INDEX instead of VLOOKUP if data layout so requires.

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

First I added a “month” column in tblSales (=MONTH([@Date]))

Then
=INDEX(tblSales,MATCH(MINIFS(tblSales[Date],tblSales[month],[@Month]),tblSales[Date],0),3)

Cris

will vlookup work for this problem?

Hi,

1- To know the right sale person, we need to know the position of the lowest value of individual month. That can be done by match and index combination, but

2- To know the lowest value of individual month, I have used the array formula MIN(IF(MONTH(tblSales[Date])=[@Month];tblSales[Date]))

So, the complete formula became as shown below.

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

Do not forget to press CTRL-SHIFT-ENTER

Hi John,
My knowledge of formulas is very poor but being your student in VBA-PRO course I sweated out some code to tackle the challenge.

in the cell: =EarliestSale([@Month])

in the VBA module:
Function EarliestSale(month1 As Integer)

Dim Person As String
Person = “nobody”
Dim monthStr As String
Dim location As Integer
Dim latestD As Date
latestD = “1.1.2016”

For i = 8 To 1007

location = InStr(Cells(i, 6).Value, “.”)
monthStr = Mid(Cells(i, 6).Value, location + 1, 2)
If InStr(monthStr, “.”) Then
monthStr = Left(monthStr, 1)
End If
Dim monthInt As Integer
monthInt = CInt(monthStr)
Debug.Print monthInt

If monthInt = month1 Then
If Cells(i, 6).Value < latestD Then
latestD = Cells(i, 6).Value
Person = Cells(i, 7).Value
End If
End If

Next i

EarliestSale = Person

End Function

p.s. my Czech Windows setting throws me date in our shape with days at the begining, so my code will probably need some modification to work in your setting ….

CSE
=INDEX(tblSales[Sales Person];MATCH(SMALL(IF(MONTH(tblSales[Date])=[@Month];tblSales[Date]);1);tblSales[Date];0))

Power Query – but this using sorting 😉
let
Source = Excel.CurrentWorkbook(){[Name=”tblSales”]}[Content],
Sort = Table.Sort(Source,{{“Date”, Order.Ascending}}),
Month = Table.TransformColumns(Sort,{{“Date”, Date.Month, Int64.Type}}),
Duplicates = Table.Distinct(Month, {“Date”}),
Columns = Table.SelectColumns(Duplicates,{“Date”, “Sales Person”})
in
Columns

Hi Jon,
My solution is NOT an array formula, so no need of Shift+Ctrl+Enter.

=VLOOKUP(AGGREGATE(15,6,tblSales[Date]*1/(MONTH(tblSales[Date])=[@Month]),1),tblSales[[Date]:[Sales Person]],2,0)

It uses our familiar VLOOKUP (simpler and shorter than INDEX/MATCH duo) and to find the smallest entry by month, AGGREGATE comes to the rescue. Similar technique to Paul’s above, but with a simpler approach by using 1/array comparison, which eliminates the need to use it twice.
Agreed, Mike Girvin makes Excel fun and credit of the formula technique goes to him.

Thanks,
Pablo

Hi Jon,

I struggled quite a lot with this challenge and I did not succeed fully.
In stead of entering a large formula I figure that it would be best to set up some help columns. I use the Month nrs (Col A), to calculate in Col L (L8), the 1st of each month at midnight (day/time stamp):

=DATEVALUE(tblFirstSale[@Month]&”/1/15″)

For January this gives me: 1/1/15 12:00:00 AM
Then in Col M (M8), I use this info to calculated the closest larger date/tine stamp value (as this would be the first sale):

=SMALL(tblSales[Date],COUNTIF(tblSales[Date],”<”&L8)+1)

For January this gives me: 1/1/15 3:27:20 AM
Then in Col N (N8), I search for the row in the Date column where this date/time stamp value occurs:

=MATCH(M8,tblSales[Date],0)

For January this gives me: 447
Then in cell C8 I entered the following formula:

=INDEX(tblSales[Sales Person],N8)

The formula gave me “Teresa Snyder”, but as it is copied down to C9:C19, it doesn’t change, all cells in C8;C9 show “Teresa Snyder”. I didn’t manage to resolve this.

=INDEX(tblSales[Sales Person],MATCH(AGGREGATE(15,6,(tblSales[Date])/([@Month]=MONTH(tblSales[Date])),1),tblSales[Date],0))

This is the formula I used to get the answer

{=INDEX($G$8:$G$1007,MATCH(MIN(IF(MONTH($F$8:$F$1007)=B8,$F$8:$F$1007)),$F$8:$F$1007,0),)}

=VLOOKUP(MIN(IF(MONTH(tblSales[Date])=[@Month],tblSales[Date])),tblSales[[Date]:[Sales Person]],2,FALSE)
Using the VLOOKUP I was able to make it to work by using the lookup value as follow:
By comparing the MIN value of the TbaleSales Date = to the corresponding value in the tableFirstSale Month and comparing it against the tblSales Date and looking for the corresponding Sales Person within the array of tblSales Sales Person and picking the content of the column and row that matches the MIN date reflecting the Sales Person.
And tried to run and I got the result as requested.

I had started using first a formula that gave me only the first one but then decided to change apply the VLOOKUP instead. I pretty sure that there must be a better way but could not figure out expected the formula listed above, which did the work.

Hi! Jon,
I have a problem in my excel sheet, I didn’t find the function “Aggregate” in my excel 2007. Please, help me on this problem.
Thank you

Thanks for detailed videos and giving challenge where expertness is required.After watching video I will be able to participate in your challenge.If you to send me copy of excel.