0

A & B are source columns, C is desired result.

A B C
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 7 9
9 8 10
10 9 11
11 10 12
12 11 13
13 12 14
14 13 15
15 14 16
16 15 17
17 16 18
18 17 19
19 18 20
20 19 21
21 20 22
22 21 23
23 22 24
24 23 26
25 23
26 24

I'm using =FILTER($A1:$A27,$B2:$B28<>$B1:$B27) - which does remove the duplicates, but keeps the second instance. (Also copies the header row, which is useful.)

I suspect there's going to be a better way of doing this altogether though. Thanks for any help.

TrevorA
  • 27
  • 5
  • 3
    So you are saying there could be more than two duplicates in column B:B and you wish to keep the very last one (from its neighboring A:A cell)? If so, try: `=FILTER(A1:A26,ROW(A1:A26)=XMATCH(B1:B26,B1:B26,0,-1))` which should still keep the header. – JvdV Oct 24 '22 at 19:12
  • 2
    @JvdV good solution, but why XMATCH for reverse search instead MATCH? `=FILTER(A1:A26,ROW(A1:A26)=MATCH(B1:B26,B1:B26,0))` generate the desiderate list – Terio Oct 24 '22 at 20:36
  • @Terio, from what I understood op was looking to leave the latest of each dupe in the filtered list. Could be wrong ofcourse – JvdV Oct 24 '22 at 20:40
  • @JvdV seeing the example *C* column I understood he wanted the first value: eg. 6, 7, **9**, 10 and not 6, 8, 9, 10 – Terio Oct 24 '22 at 20:54
  • @Terio, then just match would do yes – JvdV Oct 24 '22 at 21:00
  • @TrevorA can you explain your question? We have a doubt ... – Terio Oct 25 '22 at 06:13
  • Sorry for not being ckear, yes the request was to keep the first instance. My original formula kept the 2nd instance. The xmatch reverse search does the job. Thanks all. – TrevorA Oct 25 '22 at 07:00
  • 1
    @JvdV Sir please post as an `Answer` for benefit of the community. – Mayukh Bhattacharya Oct 25 '22 at 08:10

1 Answers1

4

To close the question; as per comments, use MATCH():

=FILTER(A1:A26,ROW(A1:A26)=MATCH(B1:B26,B1:B26,0))
JvdV
  • 70,606
  • 8
  • 39
  • 70