1

In a Calc or Excel sheet I have a column with bunch of names. Each name can occur one or multiple times.

Each name has a 'first occurrence' i.e. the first row in which it appears.

I'm looking for an expression or formula to find the name with the latest first occurrence.

An example to clarify:

Name
1 Alice
2 Charlie
3 Bob
4 Eve
5 Charlie
6 David
7 Bob
8 Alice
9 David
10 Charlie
11 Eve

David first occurs is row 6, all other names appear earlier (and also again further down, but I'm only interested in their first occurrence). So in this case the name I want is David.

I can probably think of a very over-engineered solution with spare columns and sorting mechanisms, but I feel there's probably an easier way to do this.

Improve this question
3
  • The example would be easier to use as text instead of an image. However, the question is clear.
    – Jim K
    Commented Jun 30, 2022 at 15:31
  • @JimK Thanks, wasn't really familiar with how to edit tables in markdown, hopefully this makes the question more useful for others in the future.
    – RocketNuts
    Commented Jun 30, 2022 at 19:35
  • Very nice table. I was expecting you to simply indent the text, but that works. :)
    – Jim K
    Commented Jul 1, 2022 at 13:05

2 Answers 2

Reset to default
2

For LibreOffice, enter the following as an array formula.

=INDIRECT("B"&2+MAX(MATCH(B3:B13;B$3:B$13;0)))

To avoid array formulas, use SUMPRODUCT instead.

=INDIRECT("B"&2+SUMPRODUCT(MAX(MATCH(B3:B13;B$3:B$13;0))))
Improve this answer
1
  • Wow, thanks, that's indeed a lot more compact that I could have imagined :) I'm unfamiliar with array formulas as well as sumproduct and match and the indirect syntax. From the context I can sortof grasp what they each do but I'll look into it to fully understand. Thanks again, this helps a lot, very insightful!
    – RocketNuts
    Commented Jun 30, 2022 at 19:39
0

For Excel, just use:

=XLOOKUP("*", UNIQUE(B1:B11), UNIQUE(B1:B11), , 2, -1)

It does not automatically sort the list, so the last item from UNIQUE() is also the last "first instance of a new name" on the list. Then using XLOOKUP() to look for anything by looking for "*" (need the doublequote characters) with "2" for the 5th parameter so it knows that's a wildcard, and "-1" for the last parameter so it looks from the end so it will find the last such name does the trick.

The key to it all is UNIQUE() generating the list, then NOT sorting it. Very aggravatingly (usually) it does not. But for your case here, that is a nice thing all around.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .