0

I have a list of entries in columns H, I, J. The column K is a helper column which concatenates the three. This is done to check the number of repeats within the range (Column L).

Now that I got the number of repeats, I entered formula in the column M:

=IF(COUNTIF(L3,">0"),K3,"")

And I got the following picture of unique values from column L:

non-blanks only:

I would like the next column (N) to enlist all the unique combinations of these values, but without blank cells between them.

Is it possible to do with a WS formula?

3

1 Answer 1

1

Basically the formula you need is:

N4=INDEX($M$3:$M$200, MATCH(0, COUNTIF($N$1:N1, $M$3:$M$200), 0))

Then press ctrl+shift+enter (or it will not work using a array formula).

Two important things to keep in mind here: The complete list is in cells M3:M200, then this formula has to be pasted in cell N4 (Not N1 or N2 as that will give you circular reference). Secondly this is an array formula, so you need to press ctrl+shift+enter or it will not work correctly.

I have modified the reference points from original answer,Many thanks to the linked author for the solution.

0

You must log in to answer this question.

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