PDA

View Full Version : Solved: Array formula to extract multiple values



Digita
12-03-2007, 10:51 PM
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 :banghead: . 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
12-04-2007, 01:45 AM
Try this

figment
12-04-2007, 06:55 AM
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
12-04-2007, 07:14 AM
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
12-04-2007, 07:31 AM
thanks

Digita
12-04-2007, 02:06 PM
Beautiful. Thanks so much XLD. Appreciate your help.

Regards


kp

Digita
12-04-2007, 03:06 PM
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 :hi:


kp