Help with an array

[cox]

Hey you gurus, I need help.

I have a spreadsheet with the following worksheets:
detail and con account

The detail spreadsheet has the following info:

d6:d748 - account names
t2:dh2 - date range Jun 28 - Sept 28
t6:dh748 number of transactions per day

I want to use the con account spreadsheet to report just the names of the
accounts in detail!d6:d748 where the value in detail!t6:dh748 <> null

How can I do this?

Thanks in advance,
Bob W.

 

[Domenic]

Hi Bob,

Here's a work of art I just picked up from Aladin.


Detail Worksheet
--------------------

DI1 contains your condition, in this case Null.

DI2 must contain a 0.

DI6, copied down:

=IF((D6<>"")*(COUNTIF(T6H6,"Null")=0),LOOKUP(9.999999999E+307,$DI$2I5
)+1,"")

DJ1:

=LOOKUP(9.999999999E+307,DII)

DK6, copied down:

=IF(ROW()-ROW($DK$6)+1<=$DJ$1,MATCH(ROW()-ROW($DK$6)+1,DII,0),"")


Con Account Worksheet
-----------------------------

A1, copied down:

=IF(N(Detail!$DK6),INDEX(Detail!D,Detail!$DK6),"")


**Note that DI3 to DI5 should not contain numeric values.


Hope this helps!

 

[Max]

Here's one pitch ..

In sheet: detail
---------------------
In an empty column, say col DJ

Put in DJ6:
=IF(SUMPRODUCT(ISBLANK(T6H6)*1)<>0,"",ROW())
Copy down to DJ748

(The above will check that all the cells
in cols T to DH per account contain values)

--------------------
Alternatively,
--------------------
Put in DJ6:
=IF(SUMPRODUCT(--(T6H6<>"")*1)=93,ROW(),"")
Copy down to DJ748

(The above will check that all the cells
in cols T to DH per account* are <> "")
*93 cells per row in cols T to DH

In sheet: con account
-----------------------------

Put in say A2:

=IF(ISERROR(MATCH(SMALL(detail!$DJ$6:$DJ$14,ROW(A1)),detail!$DJ$6:$DJ$14,0))
,"",OFFSET(detail!$D$6,MATCH(SMALL(detail!$DJ$6:$DJ$14,ROW(A1)),detail!$DJ$6
:$DJ$14,0)-1,))

Copy A2 down as many rows as you have accounts listed in D6748

Col A will return all the desired account names

 

[Max]

Oops, sorry, correction to formula in A2
(forgot to sync the test range back to row 748) ..

In sheet: con account
-----------------------------

Put in say A2:

=IF(ISERROR(MATCH(SMALL(detail!$DJ$6:$DJ$748,ROW(A1)),detail!$DJ$6:$DJ$748,0
)),"",OFFSET(detail!$D$6,MATCH(SMALL(detail!$DJ$6:$DJ$748,ROW(A1)),detail!$D
J$6:$DJ$748,0)-1,))


($DJ$14 in earlier formula corrected to $DJ$748)

 

[cox]

I will try this today and let you know this evening how I made out.

Thanks,
Bob W.

 

[cox]

Max,
I will give this a try today and let you know how I make out when I get
home tonight. Thanks for your help on this.

Bob W.

 

[Max]

You're welcome, Bob!
Hope it'll work for you.