1

Given an Excel table of shape

Col. A   Col B   Col. C   Col. D   Col. E
x        2       x        2        3
x        3       y        7
y        7       z        -5
x        2
z        -5

I want to return the first unique hit in Column B for argument "x" in Column D, the second unique hit in Column B for argument "x" in Column E and so forth.

The formula I'm currently using in cell D1 for this is

{=IFERROR(INDEX($B$1:$B$5,MATCH(0,COUNTIF($C1:C1,$B$1:$B$5)+($A$1:$A$5<>$C1),0)),"")}

which is working.

The problem I'm having is that since this is an array formula and since I'm analyzing a decent amount of data computation time for my sheet is too high.

Is there an alternative for this functionality avoiding an array formula?

Thanks!

Dorian IL
  • 199
  • 2
  • 11

1 Answers1

3

Haven't got time to test this properly, but if you have Excel 365 you can use a single formula per row and it may be faster:

=TRANSPOSE(UNIQUE(FILTER(B1:B10,A1:A10=C1)))

in D1.

EDIT

To pull the formula down, you need static references as OP has pointed out. Probably should check for empty cells in column C as well, so formula becomes:

=IF(C1="","",TRANSPOSE(UNIQUE(FILTER(B$1:B$10,A$1:A$10=C1))))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • This is working. Only thing I had to edit is using static references b$1:b$10, a$1:a$10. – Dorian IL Aug 18 '21 at 10:57
  • Yes, I realised this later as well - will edit my answer. – Tom Sharpe Aug 18 '21 at 12:14
  • 1
    I just wanted to provide an additional comment on computation time: As expected, this is much faster than using my original array formula. – Dorian IL Aug 19 '21 at 04:37
  • is it possible to return the output into an array that is stored in one cell? I have to create a lot of Columns to not get a "SPILL!" error. – Dorian IL Aug 19 '21 at 10:27
  • Yes, you could return it in one cell with separators like 2,3,4 =IF(C1="","",TEXTJOIN(",",,UNIQUE(FILTER(B$1:B$10,A$1:A$10=C1)))) – Tom Sharpe Aug 19 '21 at 10:53