Help with a function

[cox]

I need help with a problem, I have a spreadsheet with the following columns:

non-standard acct name 29 jun 30 jun
y my_acct 2 6
y your_acct 0 0
n test_acct 0 0
y new_acct 3 0
y junk_acct 5 3

the non-standard column has a yes or no value, the acct name is just the
acct name and the 29 jun is a date that I enter in a transaction count.

My issue is this, on a different worksheet I want to display by date just
the acct name and the number of transactions, so the 2nd worksheet looks
like this

Acct Name 29 jun acct name 30 jun
my_acct 2 my_acct 6
new_acct 3 junk_acct 3
junk_acct 5


what function am I looking for and how do I make this work?

Thanks in advance,
Bob W.

 

[Max]

One way ..

Assuming

In Sheet1
-------------
Your source table below is in A16

non-standard acct name 29 jun 30 jun
y my_acct 2 6
y your_acct 0 0
n test_acct 0 0
y new_acct 3 0
y junk_acct 5 3

Put in E2: =IF(C2=0,"",C2)

Copy E2 across to F2, then down to F6
(or down as many rows as you have data in cols A)

In Sheet2
-------------
In A11 are the labels:

Acct Name 29 jun acct name 30 jun

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$E:$E,ROW()-1),Sheet1!$E:$E,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$E:$E,ROW()-1),Sheet1!$E:$E,0)-1,MATCH(A$1,Sheet
1!$1:$1,0)-1))

Copy A2 across to B2

Put in C2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$F:$F,ROW()-1),Sheet1!$F:$F,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$F:$F,ROW()-1),Sheet1!$F:$F,0)-1,MATCH(C$1,Sheet
1!$1:$1,0)-1))

Copy C2 across to D2

Now select A22 and copy down to D6

This should return the desired table