How to extract a list of records with certain criteria met in one table while identifiers extracted from another table?

Question

In Microsoft Excel, I have a table named TableTwo, with fields:

tName
dDate
tStatus

tName is key-identifier of the records.

We extract a list of tName, named tNames, from table TableOne. Then for each tName in tNames, we want to find records from TableTwo that its dDate is the maximum value of corresponding records with this tName, and their tStatus is not Accepted.

TableOne

tName	Category	startDate
asset1	cat1
asset2	cat1
asset3	cat1
asset4	cat2
asset5	cat3

TableTwo

tName	dDate	tStaus
asset1	14010201	Accepted
asset1	13990304	Accepted
asset2	14010420	Accepted
asset3	14020512	Accepted
asset1	14030305	Rejected
asset1	14030306	Accepted
asset1	14030307	Postponed
asset2	14030307	Rejected
asset3	14030307	Accepted
asset4	14030307	Accepted
asset4	14030308	Rejected
asset5	14030309	Accepted
asset2	14030310	Accepted
asset2	14030311	Rejected

Final Results:

tName	dDate	tStatus
asset1	14030307	Postponed
asset2	14030311	Rejected

I've tried to use LET and Filter function, first finding tNames, then inspecting TableTwo to find records that met criterias.

Image of the tables, and expected results:

Attempt 1 — Output: #N/A

=LET(
    tNames, FILTER(TableOne[tName],(TableOne[Category]="cat1"),"NoAsset"),
    dDates, TableTwo[dDate],
    tStatuses, TableTwo[tStatus],
    maxDates, MAXIFS(TableTwo[dDate], TableTwo[tName], tNames),
    result, IF((TableTwo[dDate]=maxDates)*(TableTwo[tStatus]<>"Accepted"), TableTwo[tName], ""),
    FILTER(result, result<>"")
)

Attempt 2 — Output: #Value!

=LET(tNames,FILTER(TableOne[tName],(TableOne[Category]="cat1"),"NoAsset"),
cntr, SEQUENCE(ROWS(tNames)),
rngTwo,FILTER(tNames,(TableTwo[tName]=INDEX(tNames,cntr))*(TableTwo[dDate]=MAXIFS(TableTwo[dDate],TableTwo[tName],INDEX(tNames,cntr)))*(TableTwo[tStatus]<>"Accepted")),
r,SEQUENCE(ROWS(rngTwo)),
output,INDEX(rngTwo,r),
output)

Mayukh Bhattacharya · Accepted Answer · 2024-11-05 08:09:33Z

2

To get the desired output, here is what you should do, refer the following formula:

=LET(
     a, FILTER(TableOne[tName],TableOne[Category]="cat1","NoAsset"),
     b, MAXIFS(TableTwo[dDate],TableTwo[tName],a),
     c, (1-ISNA(XMATCH(TableTwo[dDate]&TableTwo[tName],b&a))),
     FILTER(TableTwo,c*(TableTwo[tStaus]<>"Accepted")))

In place of 1-ISNA( one can also use ISNUMBER(

You can also use MMULT() function:

=LET(
     a, FILTER(TableOne[tName],TableOne[Category]="cat1","NoAsset"),
     b, MAXIFS(TableTwo[dDate],TableTwo[tName],a),
     c, MMULT((TableTwo[tName]=TRANSPOSE(a))*
              (TableTwo[dDate]=TRANSPOSE(b))*
              (TableTwo[tStaus]<>"Accepted"),
        {1;1;1}),
     FILTER(TableTwo,c))

edited Nov 5, 2024 at 8:09

answered Nov 5, 2024 at 8:00

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rachel · Accepted Answer · 2024-11-05 08:48:47Z

2

I think you can sort TableTwo first and then use "search from first to last" option of XMATCH to display the results:

Updated, since you have only Excel 2021, so you will stick to INDEX and FILTER. (cannot use CHOOSEROWS, TOCOL etc).

=LET(
    tNames, FILTER(TableOne[tName], (TableOne[Category] = "cat1"), "NoAsset"),
    sorted, SORT(FILTER(TableTwo, TableTwo[tStaus] <> "Accepted", ""), 2, -1),
    row_nums, XMATCH(tNames, INDEX(sorted, , 1), 0, 1),
    filtered_row_nums, FILTER(row_nums, ISNUMBER(row_nums), ""),
    INDEX(sorted, filtered_row_nums, {1, 2, 3})
)

edited Nov 5, 2024 at 8:48

answered Nov 5, 2024 at 8:11

rachel

1,9941 gold badge12 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to extract a list of records with certain criteria met in one table while identifiers extracted from another table?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related