copy data from one sheet to another under conditions

[nico]

I have a table with three columns: Amount, Date, Description
In a second table certain descriptions are grouped under headers: Monthly,
Weekly

I would now like to set up a third table that has the date as a running
number, and
then the columns Monthly, Weekly, Daily. Under these headings I would like
the amount from the first table to appear in the Monthly column if the date
from table one is the same as the date in the row of table 3, and if the
description in table one is entered under the Monthly column of table 2. If
the description is not in table2 either under weekly or monthly, I want the
amount to enter Table 3 in the Daily column, at the right date. Is that
possible?

Table1
[Amount] [Date] [Description]
1200 1-May Rent
300 1-May Transport
12 1-May Stuff
50 3-May More Stuff

Table2
[Monthly] [Weekly]
Rent Transport
Taxes Cleaners

Table3
[Date] [Monthly] [Weekly] [Daily]
31-Apr 0 0 0
1-May 1200 300 12
2-May 0 0 0
3-May 0 0 50

The dates in Table3 are pre-entered.

Any help greatly appreciated!

[Max]

Assume:

Table 1 is in Sheet1, A1:C5
Table 2 is in Sheet2, A1:B3
Table 3 is in Sheet3, A1:D5

In Sheet1
----------
Put in D2:

=IF(ISNUMBER(MATCH($C2,Sheet2!A:A,0)),Sheet2!A$1,IF(ISNUMBER(MATCH($C2,Sheet
2!B:B,0)),Sheet2!B$1,"[Daily]"))

(normal ENTER will do)

Copy down to D5

In Sheet3
----------
Put in formula bar for B2, and array-enter
(i.e. press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH($A2&"_"&B$1,Sheet1!$B$2:$B$10&"_"&Sheet1!$D$2:$D$10,0)),0,IND
EX(Sheet1!$A$2:$A$10,MATCH($A2&"_"&B$1,Sheet1!$B$2:$B$10&"_"&Sheet1!$D$2:$D$
10,0)))

Copy across to D2, fill down to D5 to populate the grid

Sheet3 returns the desired results,
i.e. for the sample data posted:

> [Date] [Monthly] [Weekly] [Daily]
> 30-Apr 0 0 0
> 1-May 1200 300 12
> 2-May 0 0 0
> 3-May 0 0 50
(Note: Typo in date corrected: "30-Apr")

Adapt the ranges in the array formula, viz.:
Sheet1!$B$2:$B$10
Sheet1!$D$2:$D$10
Sheet1!$A$2:$A$10
to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"nico" <nico@discussions.microsoft.com> wrote in message
news:635862B3-B245-48CE-9BA0-9220C3FFDE8D@microsoft.com...
> I have a table with three columns: Amount, Date, Description
> In a second table certain descriptions are grouped under headers: Monthly,
> Weekly
>
> I would now like to set up a third table that has the date as a running
> number, and
> then the columns Monthly, Weekly, Daily. Under these headings I would like
> the amount from the first table to appear in the Monthly column if the
date
> from table one is the same as the date in the row of table 3, and if the
> description in table one is entered under the Monthly column of table 2.
If
> the description is not in table2 either under weekly or monthly, I want
the
> amount to enter Table 3 in the Daily column, at the right date. Is that
> possible?
>
> Table1
> [Amount] [Date] [Description]
> 1200 1-May Rent
> 300 1-May Transport
> 12 1-May Stuff
> 50 3-May More Stuff
>
> Table2
> [Monthly] [Weekly]
> Rent Transport
> Taxes Cleaners
>
> Table3
> [Date] [Monthly] [Weekly] [Daily]
> 31-Apr 0 0 0
> 1-May 1200 300 12
> 2-May 0 0 0
> 3-May 0 0 50
>
> The dates in Table3 are pre-entered.
>
> Any help greatly appreciated!

[nico]

Thanks Max,
this was already very helpful, especially since I have never worked with
arrays before. Just one more question: How do I get the array formula to add
together several entries that are, say [daily] and of the same date?
At the moment the formula seems to only take the first value it finds, is
that right?
Thanks for any help!
Nico

"Max" wrote:

> Assume:
>
> Table 1 is in Sheet1, A1:C5
> Table 2 is in Sheet2, A1:B3
> Table 3 is in Sheet3, A1:D5
>
> In Sheet1
> ----------
> Put in D2:
>
> =IF(ISNUMBER(MATCH($C2,Sheet2!A:A,0)),Sheet2!A$1,IF(ISNUMBER(MATCH($C2,Sheet
> 2!B:B,0)),Sheet2!B$1,"[Daily]"))
>
> (normal ENTER will do)
>
> Copy down to D5
>
> In Sheet3
> ----------
> Put in formula bar for B2, and array-enter
> (i.e. press CTRL+SHIFT+ENTER):
>
> =IF(ISNA(MATCH($A2&"_"&B$1,Sheet1!$B$2:$B$10&"_"&Sheet1!$D$2:$D$10,0)),0,IND
> EX(Sheet1!$A$2:$A$10,MATCH($A2&"_"&B$1,Sheet1!$B$2:$B$10&"_"&Sheet1!$D$2:$D$
> 10,0)))
>
> Copy across to D2, fill down to D5 to populate the grid
>
> Sheet3 returns the desired results,
> i.e. for the sample data posted:
>
> > [Date] [Monthly] [Weekly] [Daily]
> > 30-Apr 0 0 0
> > 1-May 1200 300 12
> > 2-May 0 0 0
> > 3-May 0 0 50
> (Note: Typo in date corrected: "30-Apr")
>
> Adapt the ranges in the array formula, viz.:
> Sheet1!$B$2:$B$10
> Sheet1!$D$2:$D$10
> Sheet1!$A$2:$A$10
> to suit ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "nico" <nico@discussions.microsoft.com> wrote in message
> news:635862B3-B245-48CE-9BA0-9220C3FFDE8D@microsoft.com...
> > I have a table with three columns: Amount, Date, Description
> > In a second table certain descriptions are grouped under headers: Monthly,
> > Weekly
> >
> > I would now like to set up a third table that has the date as a running
> > number, and
> > then the columns Monthly, Weekly, Daily. Under these headings I would like
> > the amount from the first table to appear in the Monthly column if the
> date
> > from table one is the same as the date in the row of table 3, and if the
> > description in table one is entered under the Monthly column of table 2.
> If
> > the description is not in table2 either under weekly or monthly, I want
> the
> > amount to enter Table 3 in the Daily column, at the right date. Is that
> > possible?
> >
> > Table1
> > [Amount] [Date] [Description]
> > 1200 1-May Rent
> > 300 1-May Transport
> > 12 1-May Stuff
> > 50 3-May More Stuff
> >
> > Table2
> > [Monthly] [Weekly]
> > Rent Transport
> > Taxes Cleaners
> >
> > Table3
> > [Date] [Monthly] [Weekly] [Daily]
> > 31-Apr 0 0 0
> > 1-May 1200 300 12
> > 2-May 0 0 0
> > 3-May 0 0 50
> >
> > The dates in Table3 are pre-entered.
> >
> > Any help greatly appreciated!
>
>
>

[Max]

Just change the formula in Sheet3 ..

Put instead in B2:
=SUMPRODUCT((Sheet1!$B$2:$B$10=$A2)*(Sheet1!$D$2:$D$10=B$1),Sheet1!$A$2:$A$1
0)

(Normal ENTER will do)

Copy across to D2, fill down to D5 to populate the grid

.... and think this is the formula that should have been suggested in the
first place <g>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"nico" <nico@discussions.microsoft.com> wrote in message
news:AC07CDAD-6B70-4635-9CA4-827A61532C75@microsoft.com...
> Thanks Max,
> this was already very helpful, especially since I have never worked with
> arrays before. Just one more question: How do I get the array formula to
add
> together several entries that are, say [daily] and of the same date?
> At the moment the formula seems to only take the first value it finds, is
> that right?
> Thanks for any help!
> Nico

[nico]

Thanks Max, that works! Brilliant!
Nico

"Max" wrote:

> Just change the formula in Sheet3 ..
>
> Put instead in B2:
> =SUMPRODUCT((Sheet1!$B$2:$B$10=$A2)*(Sheet1!$D$2:$D$10=B$1),Sheet1!$A$2:$A$1
> 0)
>
> (Normal ENTER will do)
>
> Copy across to D2, fill down to D5 to populate the grid
>
> .... and think this is the formula that should have been suggested in the
> first place <g>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "nico" <nico@discussions.microsoft.com> wrote in message
> news:AC07CDAD-6B70-4635-9CA4-827A61532C75@microsoft.com...
> > Thanks Max,
> > this was already very helpful, especially since I have never worked with
> > arrays before. Just one more question: How do I get the array formula to
> add
> > together several entries that are, say [daily] and of the same date?
> > At the moment the formula seems to only take the first value it finds, is
> > that right?
> > Thanks for any help!
> > Nico
>
>
>

[Max]

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"nico" <nico@discussions.microsoft.com> wrote in message
news:71BB5862-7E15-43C3-9D60-49B7FD608F22@microsoft.com...
> Thanks Max, that works! Brilliant!
> Nico