copy data from one sheet to another under conditions

G

Guest

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!
 
M

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
Click to expand...
(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 said:
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!
Click to expand...
 
G

Guest

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 said:
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
Click to expand...
(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 said:
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!
Click to expand...
Click to expand...
 
M

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>
 
G

Guest

Thanks Max, that works! Brilliant!
Nico

Max said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to export table from outlook into ms access table? 0
data from sheet to sheet automatically 1
Pivot Table Set Up 2
Use icon sets relatively in a pivot table 1
Convert Daily Data to Monthly Data 3
how to put qty & date of the item 0
Pivot Table Summary Error? 6
SUM Daily to Month 4

Top