Solved: Array formula to extract multiple values

[Digita]

Hi guys,

I'm a bit stuck in writing an array formula to return multiple values for unique entries (A, B & C) from a dynamic list in columns A & B . I couldn't find an answer in this forum so I need someone's help.

I attach an example file for your viewing. I have manually input the answer for A in range E3:E7. It would be good if we can make the formula dynamic as well.

Thanks in advance.

Kind regards


kp

[Bob Phillips]

Try this

[figment]

parden my ignorance but i was looking at your sulution xld and i was wonder what exatly the folowing code dose

COUNTIF($E$2:E$2,$B$1:$B$20&"")

other then those three characters it seems like a normal countif statment

[Bob Phillips]

Its not a normal COUNTIF, it is an array COUNTIF, which counts all occurrences of the second array in the first. The &"" is to cater for empty cells.

[figment]

thanks

[Digita]

Beautiful. Thanks so much XLD. Appreciate your help.

Regards


kp

[Digita]

Hi All,

I just made a minor amendment to XLD's solution to make the array formulae dynamic. I enclose an example file for everyone's benefit. Also given the size of the formula, I have turned off the formula bar for easy viewing.

Thanks again XLD.

Regards


kp