List Unique Values Based On Criteria

[tangmere.milli]

Hi all,

I am hoping for some assistance with a formula that I am trying to create.

I have a query that is returning many rows of data, but I need a formula to do the following;

I need a list of all unique values in column E, if the corresponding rows in column D equals a value in $A$1.

Would then want these unique values to list across columns from G1 to T1 (or maybe more depending on the number of values).

I have attached an example.Checks.xlsx

I hope this makes sense.

Thanks in advance.

TM.

[XOR LX]

Hi.

In G1, array formula**:

=IFERROR(SMALL(IF(FREQUENCY(IF($D$2:$D$9=$A$1,$E$2:$E$9),$E$2:$E$9),$E$2:$E$9),COLUMNS($A:A)),"")

Copy across as required.

I used a rather lazy IFERROR approach since it appears that you do not have a very large dataset.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[tangmere.milli]

Superb! Exactly what I needed, thanks

The spreadsheet that I attached was only an example, my actual dataset is far, far larger.

What other approach might be better other than the 'IFERROR'?

TM

[XOR LX]

No problems, but in that case shouldn't you be arranging to have these values returned into a single column, rather than a single row?

Regards

[tangmere.milli]

Normally, yes.

I just need these values to return in a row as it will form part of a report. I will then attached lookups to this row to return additional data.

[XOR LX]

Then the most efficient method would be to put a single, non-array formula in e.g. F1, used to determine the expected number of returns:

=SUMPRODUCT((D2:D9=A1)/COUNTIF(E2:E9,E2:E9))

(Assumes there are no blanks in the range, just as in your attached example.)

Your array formula** in G1 is then:

=IF(COLUMNS($A:A)>$F$1,"",SMALL(IF(FREQUENCY(IF($D$2:$D$9=$A$1,$E$2:$E$9),$E$2:$E$9),$E$2:$E$9),COLUMNS($A:A)))

and copied to the right.

Regards