Logging First occurrence in calendar year

[Bedlam]

Hi

I'm trying to do a count of people first using a process within a calendar year but perhaps it is best to look at the problem in smaller steps. The log will contain older data but it is only 2024 data that I am currently interested in. I see the data that I have inherited contains multiple names occasionally against a date. On the left is the log. On the right is the expected output. Joe's first entry in 2024 is 1 Jan. I can disregard Joe thereafter. Bob and Sue are both on 2 Feb and could be parsed out. Jan has only one entry in the year. I'm looking for ideas as to how I might turn data on left into data on right. Could some combination of FIND, MID, FILTER and TRANSPOSE functions work?

Date	Names		For 2024
01 February 2023​	Joe		First Occurrence Date for name in year of interest	Name
01 January 2024​	Joe		01 January 2024​	Joe
02 February 2024​	Bob Sue		02 February 2024​	Bob
03 February 2024​	Bob		02 February 2024​	Sue
04 February 2024​	Jan		04 February 2024​	Jan
05 February 2024​	Joe

 

[KRice]

If a space is used as the delimiter for people listed in the Names column, how would you know if Bobby Sue and Billie Jo represents two people or four? The actual data do not use commas or something other than a space?

 

[Bedlam]

This is sample data. Space will be a sufficient delimiter as it will actually be email addresses

 

[KRice]

That's a significant detail. Otherwise, more complicated measures would be needed to discern between, say, Pat, Patrick, Patricia. A simple search for "Pat" could find any of those names, whereas an email that is unique to only Pat would find the correct records.

 

[KRice]

Give this a try to see if it works for you...

MrExcel_20240301.xlsx
	A	B	C	D	E
1	Date	Names		2024	<- Enter Year
2	1-Feb-23	Joe@email.com		Date of 1st Occurrence	Name
3	1-Jan-24	Joe@email.com		1-Jan-24	Joe@email.com
4	2-Feb-24	Bob@email.com Sue@email.com		2-Feb-24	Bob@email.com
5	3-Feb-24	Bob@email.com		2-Feb-24	Sue@email.com
6	4-Feb-24	Jan@email.com		4-Feb-24	Jan@email.com
7	5-Feb-24	Joe@email.com
8
Sheet3

Cell Formulas
Range	Formula
D3:E6	D3	=LET(data,$A$2:$B$7,dt,CHOOSECOLS(data,1),nm,CHOOSECOLS(data,2), names,TOCOL(TEXTSPLIT(TEXTJOIN("|",TRUE,nm)," ","|",,,"")), unames,UNIQUE(FILTER(names,names<>"")), firstdt,BYROW(unames,LAMBDA(r,TAKE(SORT(FILTER(dt,(YEAR(dt)=$D$1)*(ISNUMBER(SEARCH(r,nm))),"")),1))), res,HSTACK(firstdt,unames),FILTER(res,CHOOSECOLS(res,1)<>""))
Dynamic array formulas.

Stepping through the formula, this assigns your source data in A2:B7 to a variable called "data". Then for convenience, the date and name/email columns are assigned to variables "dt" and "nm", respectively. Next, the names are joined together, separated by a pipe (|) character, and then split back apart using the space and "|" delimiters...this effectively breaks the names into multiple columns of an array, and then that multi-column array is stacked into a single column (that's what the "names" variable does). "names" is then filtered to remove blanks and then return only unique names (unames). Next, the unames list is fed row-by-row into a BYROW LAMBDA function, where the main data Name column (nm) is searched for whichever unique name has been passed into the function, and the dates in the main data Date column (dt) are filtered, showing only those dates that are associated with the specified year and the name passed into the function. We then SORT this list of dates (smallest to largest) and TAKE only the first one (the earliest date). So the "firstdt" variable is an array of first dates associated with each of the unique names (unames). We stack those two arrays together horizontally and then filter them to exclude any where a date does not exist (because the name searched for does not exist during the specified year).

 

[Bedlam]

Fantastic work and it will take me a while to work through an understanding

 

[KRice]

Post back with any questions, or if you see something that doesn't look right.

 

[Bedlam]

I'll work through it on Monday when back in the office. Thanks for the offer

 

[Bedlam]

@KRice All is working well - thanks once again

 

[KRice]

You're welcome....happy to help.