Guys,
please help me! I struggle with some problem:
I have a list:
date no code
12/4/6 101 AA
12/4/6 101 AB
12/4/6 102 AA
12/4/6 102 AA
12/4/6 102 AA
13/4/6 101 AA
13/4/6 101 AC
13/4/6 101 AB
13/4/6 102 AA
....
....

I'm trying to generate a list of codes matching to criteria, e.g.:
date no
13/4/6 101
Unfortunately function 'MATCH' can find a record using just one criteria,
and 'DGET' returns #NUM if there's more then one record matching the criteria.
What I need to get is:
AA
AC
AB
e.g. as array. Or if it's not possible at least first of the matching.
Is there any way to do it?
Thanks for any help.

Assuming that A2:C10 contains the data...

Let E2 contain the date of interest, such as 13/4/06

Let F2 contain the number of interest, such as 101

Then try the following formulas...

G2:

=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2))

H2, copied down:

=IF(ROWS(H$2:H2)<=$G$2,INDEX(C$2:C$10,SMALL(IF($A$ 2:$A$10=$E$2,IF($B$2:$B
$10=$F$2,ROW(C$2:C$10)-ROW(C$2)+1)),ROWS(H$2:H2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
realspido wrote:

Guys,
please help me! I struggle with some problem:
I have a list:
date no code
12/4/6 101 AA
12/4/6 101 AB
12/4/6 102 AA
12/4/6 102 AA
12/4/6 102 AA
13/4/6 101 AA
13/4/6 101 AC
13/4/6 101 AB
13/4/6 102 AA
...
...

I'm trying to generate a list of codes matching to criteria, e.g.:
date no
13/4/6 101
Unfortunately function 'MATCH' can find a record using just one criteria,
and 'DGET' returns #NUM if there's more then one record matching the criteria.
What I need to get is:
AA
AC
AB
e.g. as array. Or if it's not possible at least first of the matching.
Is there any way to do it?
Thanks for any help.

Pivot Table, with totals hidden, will give you this:

date no AA AB AC
4/12/2006 101 1 1
102 3
4/13/2006 101 1 1 1
102 1

Name the 4x3 data field array1
and the 1x3 column field code2.
The array formula
=IF(array1=1,code2,"")
will give you this:

AA AB
AA
AA AB AC
AA

I was thinking about Pivot Table, but see, the problem is I need my
spreadsheet to work as fast as possible because it actually works as
database. All data are stored in one sheet as table and using macros and
second sheet containing formulas you can retrieve information from the table.
So all formulas must be as simple as posslible, otherwise moving from one
record to another takes ages...


"Herbert Seidenberg" wrote:

Pivot Table, with totals hidden, will give you this:

date no AA AB AC
4/12/2006 101 1 1
102 3
4/13/2006 101 1 1 1
102 1

Name the 4x3 data field array1
and the 1x3 column field code2.
The array formula
=IF(array1=1,code2,"")
will give you this:

AA AB
AA
AA AB AC
AA