Retrieve unique data to list

Originally Posted by Jeaz

Hi,

I've been trying to create a function which extracts data into a list. Is it possible to do without macros? If so, how do I do it?

To illustrate:

A B C D
Y AAA A1 11
N AAA A2 22
N AAA A3 33
N BBB B1 44
Y BBB B2 55
N BBB B3 66

I want the function to look in coloumn B for e.g. AAA and where it's Y report C to a multirow-list. If possible, do the same with D at the same time, otherwise I'll just run the function again.
In this example I'd get the following list:
A1 11
B2 55

I'm also looking for a function that searches coloumn B for all values, but only present each finding once. In this example it would report:
AAA
BBB

Many thanks in advance

/Jeaz

I'm not sure how you would do a function without using macro, but I did this by formula.

Using E F and G and the answer columns, in E1 put

=B1

in E2 put
=IF(ISERROR(MATCH(B2,E$1:E1,0)),B2,"")

in F1 put
=IF(AND(A1="Y",(OR(AND(COLUMN()=6,B1="AAA"),(AND(COLUMN()=7,B1="BBB"))))),C1&" "&D1,"")

in G1 put
=IF(AND(A1="Y",(OR(AND(COLUMN()=6,B1="AAA"),(AND(COLUMN()=7,B1="BBB"))))),C1&" "&D1,"")
(similar to F1, but needs $B etc for complete portability)



(or, in F1 put
=IF(AND($A1="Y",(OR(AND(COLUMN()=6,$B1="AAA"),(AND(COLUMN()=7,$B1="BBB"))))),$C1&" "&$D1,"")
and fill to G1, then fill both downwards)
and formula-fill as required.

(see attached)
--------------