I've been trying to solve this problem for a while and just can't figure it out. I have a table in google sheet that looks like this: enter image description here
Date Person Number1 Number2 Number3 Number4 Number5 ...
10.05.22 Person1 11 0 0 0 111112
13.05.22 Person2 0 23 12 0 0
12.07.22 Person3 25 0 0 11 0
14.07.22 Person1 21 23 0 0 252
10.05.22 Person2 0 11 0 0 0
13.06.22 Person4 112 0 0 22 0
17.07.22 Person5 0 0 0 112 333
20.08.22 Person3 0 1112 0 0 0
23.09.22 Person3 0 0 0 0 0
...
Now I need to compare the cells in columns "Number 1" through "Number 10" through row 19 and see if there's a match. As soon as there is a number in a row that does not appear anywhere else, this row (including date and person) should only be filtered with this number.
unique + flatten offers me the possibility to output all data that correspond to my specifications. However, only the numbers are shown - but I still need the right date and the person.
If I now apply the code =UNIQUE(FLATTEN(C2:G10)) I get the following result:
11
0
111112
23
12
25
21
225
112
22
333
1112
The first number found in the table is listed here. Duplicates are excluded. How can I now assign the last respective person and the last date in addition to these numbers?
This is what the table should look like after the formula has been applied:
10.05.22 Person2 11
23.09.22 Person3 0
10.05.22 Person1 111112
14.07.22 Person1 23
13.05.22 Person2 12
12.07.22 Person3 25
14.07.22 Person1 21
14.07.22 Person1 252
17.07.22 Person5 112
13.06.22 Person4 22
17.07.22 Person5 333
20.08.22 Person3 1112