CODEWITHSUNDEEP
excelexcel-formula
Michael Cat
Michael Cat

Reputation: 3

Excel: Print exact name found from the list for exact date

Let's say I have a table with dates (all the calendar dates for 2024) and names (divided into columns):

AllNames

Date Names
01 Jan Aaa Bbb Vvv
02 Jan Ccc
03 Jan Ddd

I also have a list of some employee' names (current employees, names can be changed in future):

employees

Employees
Aaa
Vvv
Ccc

I would like to check if employee from the sheet Employees ("Aaa" for example) is in the AllNames sheet/table for an exact date. And if so, then print their name (or multiple names if several employees found for that date) near to that date:

calendar

Date Math?
01 Jan Aaa, Vvv
02 Jan Ccc
03 Jan -

Format of columns, sheets and rows is not important.

=IF(COUNTIF(AllNames!2:2,Employees!A:A)>0,"Yes","No")

I tried this and it shows if a name is found from AllNames table, but that's my top of idea that did not come further.

Upvotes: 0

Views: 74

Answers (3)

nkalvi
nkalvi

Reputation: 2614

A bit verbose:

=LET(
    comment_1, "Adjust the following to match max names per date",
    cols_with_names, 'All Names'!$B:$AA,
    dates, 'All Names'!$A2:INDEX('All Names'!$A:$A, COUNTA('All Names'!$A:$A)),
    current_employees, TOCOL(Employees!A:A, 1),
    HSTACK(
        dates,
        MAP(
            dates,
            LAMBDA(date_,
                LET(
                    names, TOCOL(INDEX(cols_with_names, ROW(date_), ), 1),
                    current, FILTER(names, ISNUMBER(XMATCH(names, current_employees))),
                    IFERROR(ARRAYTOTEXT(current), "No current employees")
                )
            )
        )
    )
)

Formula and result

Upvotes: 0

JvdV
JvdV

Reputation: 75840

enter image description here

Formula in I2:

=GROUPBY(TOCOL(IFS(B2:D4<>"",A2:A4),3),TOCOL(B2:D4,1),ARRAYTOTEXT,,0,,COUNTIF(F2:F4,TOCOL(B2:D4,1)))

Upvotes: 0

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

You could try using the following formula:

enter image description here

=MAP(A1:A34,LAMBDA(b,
 LET(a, TOCOL(Employees!A:A,1),
 TEXTJOIN(", ",1,IF(FILTER(AllNames!B2:G7,AllNames!A2:A7=b,"")=a,a,"")))))

But it is better to use Structured References aka Tables here instead of using the whole array. Therefore convert the each sources in sheets AllNames and Employees to tables.

=MAP(A1:A34,LAMBDA(b,
 LET(a, TOCOL(Employees,1),
 TEXTJOIN(", ",1,IF(FILTER(DROP(AllNames,,1),TAKE(AllNames,,1)=b,"")=a,a,"")))))

And if you don't have access to LAMBDA() helper functions could use the following:

=IFERROR(TEXTJOIN(", ",1,XLOOKUP(INDEX(AllNames[[Name1]:[Name6]],MATCH(Calendar!A1,AllNames[Date],0),),Employees[Emp],Employees[Emp],"")),"")

Upvotes: 0

Related Questions