How do I count rows that match in order?

And one way to get the file names in the columns would be to do something like this:

Add an "index" column to the first table. In F2, filled down, I have this formula:

=A2&B2

Then in the table with the distinct values for MembershipID I have this in B2, filled right and down:

=XLOOKUP($A2&COLUMN()−1,Table 1::$F,Table 1::$E,"")

Replace the , in the formulas with ; if your region uses , as a decimal separator.

SG

To derive the order you can use COUNTIF with an expanding range, like this:

To get a list of distinct MemberIDs, you can use a filter, like this:

Select the visible cells, command-c to copy, click once in a cell of an existing destination table, and command-v to copy.

SG

Here is another way to get the file names into the table, once you have the list of unique member IDs:

The first table needs a column of index numbers. You can use =ROW()-1

Column B is SGIII's COUNTIF formula

The formula in the second table is

=OFFSET(Table 1::$E$1,SUMIFS(Table 1::$F,Table 1::$A,$A,Table 1::$B,COLUMN()−1),0)

All cells other than the Member ID column and header row get that formula.

If you don't like "FileID" to show as the default value, you can create a custom highlighting rule of "Text is FileID" and set the text opacity to zero or set it to white text.

The formula uses SUMIFS as a way to do a multi-condition lookup.

If your data table is like the one you posted but with the addition of a date column, you can sort it descending according to date then use the same formulas already provided (the first file for each memberID will be the most recent one). Because of a longstanding bug in Numbers, the COUNTIF column will end up with reference errors when sorted so you'll either have to redo that column or use a workaround formula that is sort-safe (it uses OFFSET to create the range in the formula).

It can also be done with the table completely unsorted. Here is one method similar to SGIII's earlier formulas:

Table 1 column G formula =A&(F=MAXIFS(F,A,A))

This locates the row with the maximum date&time value for each Member ID

Table 2 column B formula =XLOOKUP($A&"TRUE",Table 1::$G,Table 1::$E,"",0)&""

For the Table 2 formula I added &"" to the end. Without it the result of the formula will be "0" if Table 1 and Table 2 have any empty rows. With it the result will be "". It forces the result to be a string but that should be okay here.

If you sort the table descending by date, a simple XLOOKUP will do it once you have the list of unique IDs. The lookup will be from top down by default so the first match for an ID will be the most recent.

An alternate formula that does not default to "FileID" is

=XLOOKUP(SUMIFS(Table 1::$F,Table 1::$A,$A,Table 1::$B,COLUMN()−1),Table 1::$F,Table 1::$E,"",0)

Also, the column of index numbers can be actual numbers, not a formula.

SGIII's formula is simpler, though. Maybe this will be useful for some other problem you run into.

In the expanding range make sure the first cell is anchored with a $

SG

If you can post a screenshot of what your table looks like with the multiple files and the result you want then we can try to suggest an approach that might work for you.

SG