Macro Help
 

I need some help writing a macro.

Here is some data similar to mine. Customer 1 made $250 worth of purchases
on 4/4, Customer 2 made $300 of purchases on 4/4, etc. (This information,
essentially, is a lookup from a import tab that will be refreshed daily)


4/4/2006

Customer 1 $250.00
Customer 2 $300.00
Customer 3 $1,000.00


I also have another worksheet that records the transactions over time. The
format is like the following:

Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00

Can a macro lookup the date in B1 and paste the values (transposed of
course) in the second worksheet next to the corresponding date?

Thanks in advance.

Macro Help
 

Change the data in the first sheet to have the date in a column:
Customer 1 4/4/06 $250.00
Customer 2 4/4/06 $300.00
Customer 3 4/4/06 $1,000.00

Then do a sumproduct in the second sheet:
the $250 in the intersection of "Cust 1" (Colum B) and 4/4/06 (Row 2) would
be:

=SUMPRODUCT((Sheet1!A1:A2500=Sheet2!B1)*(Sheet1!B1 :B2500=Sheet2!A2)*(Sheet1!C1:C2500))

Note you can just say A:A, B:B and C:C if using Excel 2007. Otherwise you
have to have a finite limit to the number of rows.


Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00

Macro Help
 

This won't work. The data in the first worksheet is not static. Which is
the reason why I need the macro to paste the values into a separate
spreadsheet.

"Mike H." wrote:

Change the data in the first sheet to have the date in a column:
Customer 1 4/4/06 $250.00
Customer 2 4/4/06 $300.00
Customer 3 4/4/06 $1,000.00

Then do a sumproduct in the second sheet:
the $250 in the intersection of "Cust 1" (Colum B) and 4/4/06 (Row 2) would
be:

=SUMPRODUCT((Sheet1!A1:A2500=Sheet2!B1)*(Sheet1!B1 :B2500=Sheet2!A2)*(Sheet1!C1:C2500))

Note you can just say A:A, B:B and C:C if using Excel 2007. Otherwise you
have to have a finite limit to the number of rows.


Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00

Macro Help
 

So if I understand your response correctly, you're saying you get a new set
of sales periodically (maybe daily) and then you wish to summarize the data
on sheet1 on sheet2. But sheet1 only contains yesterday's sales In that
case:

Sub Addemup
dim DataArray(5000,3) as variant
Dim TheDate as date
Dim X as double
Dim fnd as double
Dim found as integer
Dim Y as double

sheets("sheet1").select
let TheDate = ??? (cell containing the date in sheet1
let x=1

do while true
if cells(x,1).value=empty then exit do
let found =0
for y=1 to fnd
if dataarray(y,1)=cells(x,1).value then
dataarray(Y,2)=dataarray(Y,2)+cells(x,2) 'add amounts for same
customer!
found=1
exit for
end if
next
if found=0 then
fnd=fnd+1
dataarray(Fnd,1)=cells(x,1).value 'populate customer # into array
DataArray(Fnd,2)=cells(x,2).value 'populate Sales Amt #1 into Array
end if
x=x+1
Loop

sheets("sheet2").select
Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is
empty!
x=activecell.row+1
cells(x,1).value=TheDate
let y=2
do while true
if cells(1,y).value=empty then exit do
for z=2 to fnd
if cells(1,z).value=dataarray(Y,1)
cells(X,Z).value=dataarray(y,2)
dataarray(Y,3)="FOUND"
exit for
end if
next
y=Y+1
Loop
z=z-1
'now make sure all customers are in sheet2
for Y=1 to fnd
if dataarray(Y,3)<"FOUND" then
z=z+1
cells(1,z).value=dataarray(Y,1) 'put customer # on row 1
cells(x,z).value=dataarary(Y,2)
end if
Next


end sub

"Peanut" wrote:

This won't work. The data in the first worksheet is not static. Which is
the reason why I need the macro to paste the values into a separate
spreadsheet.

"Mike H." wrote:

Change the data in the first sheet to have the date in a column:
Customer 1 4/4/06 $250.00
Customer 2 4/4/06 $300.00
Customer 3 4/4/06 $1,000.00

Then do a sumproduct in the second sheet:
the $250 in the intersection of "Cust 1" (Colum B) and 4/4/06 (Row 2) would
be:

=SUMPRODUCT((Sheet1!A1:A2500=Sheet2!B1)*(Sheet1!B1 :B2500=Sheet2!A2)*(Sheet1!C1:C2500))

Note you can just say A:A, B:B and C:C if using Excel 2007. Otherwise you
have to have a finite limit to the number of rows.


Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00

Macro Help
 

Yes, you are understanding me correctly. There is one piece of your code
that doesn't work. Its in the second loop in sheet2. I am getting a syntax
error on the following statement:

if cells(1,z).value=dataarray(Y,1)






"Mike H." wrote:

So if I understand your response correctly, you're saying you get a new set
of sales periodically (maybe daily) and then you wish to summarize the data
on sheet1 on sheet2. But sheet1 only contains yesterday's sales In that
case:

Sub Addemup
dim DataArray(5000,3) as variant
Dim TheDate as date
Dim X as double
Dim fnd as double
Dim found as integer
Dim Y as double

sheets("sheet1").select
let TheDate = ??? (cell containing the date in sheet1
let x=1

do while true
if cells(x,1).value=empty then exit do
let found =0
for y=1 to fnd
if dataarray(y,1)=cells(x,1).value then
dataarray(Y,2)=dataarray(Y,2)+cells(x,2) 'add amounts for same
customer!
found=1
exit for
end if
next
if found=0 then
fnd=fnd+1
dataarray(Fnd,1)=cells(x,1).value 'populate customer # into array
DataArray(Fnd,2)=cells(x,2).value 'populate Sales Amt #1 into Array
end if
x=x+1
Loop

sheets("sheet2").select
Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is
empty!
x=activecell.row+1
cells(x,1).value=TheDate
let y=2
do while true
if cells(1,y).value=empty then exit do
for z=2 to fnd
if cells(1,z).value=dataarray(Y,1)
cells(X,Z).value=dataarray(y,2)
dataarray(Y,3)="FOUND"
exit for
end if
next
y=Y+1
Loop
z=z-1
'now make sure all customers are in sheet2
for Y=1 to fnd
if dataarray(Y,3)<"FOUND" then
z=z+1
cells(1,z).value=dataarray(Y,1) 'put customer # on row 1
cells(x,z).value=dataarary(Y,2)
end if
Next


end sub

"Peanut" wrote:

This won't work. The data in the first worksheet is not static. Which is
the reason why I need the macro to paste the values into a separate
spreadsheet.

"Mike H." wrote:

Change the data in the first sheet to have the date in a column:
Customer 1 4/4/06 $250.00
Customer 2 4/4/06 $300.00
Customer 3 4/4/06 $1,000.00

Then do a sumproduct in the second sheet:
the $250 in the intersection of "Cust 1" (Colum B) and 4/4/06 (Row 2) would
be:

=SUMPRODUCT((Sheet1!A1:A2500=Sheet2!B1)*(Sheet1!B1 :B2500=Sheet2!A2)*(Sheet1!C1:C2500))

Note you can just say A:A, B:B and C:C if using Excel 2007. Otherwise you
have to have a finite limit to the number of rows.


Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00

Macro Help
 

Add the word Then after it. I typed all that code in hte response box and
did not try to compile it. There is a dataarray mis-spelled toward to bottom
also. Good luck!

"Peanut" wrote:

Yes, you are understanding me correctly. There is one piece of your code
that doesn't work. Its in the second loop in sheet2. I am getting a syntax
error on the following statement:

if cells(1,z).value=dataarray(Y,1)






"Mike H." wrote:

So if I understand your response correctly, you're saying you get a new set
of sales periodically (maybe daily) and then you wish to summarize the data
on sheet1 on sheet2. But sheet1 only contains yesterday's sales In that
case:

Sub Addemup
dim DataArray(5000,3) as variant
Dim TheDate as date
Dim X as double
Dim fnd as double
Dim found as integer
Dim Y as double

sheets("sheet1").select
let TheDate = ??? (cell containing the date in sheet1
let x=1

do while true
if cells(x,1).value=empty then exit do
let found =0
for y=1 to fnd
if dataarray(y,1)=cells(x,1).value then
dataarray(Y,2)=dataarray(Y,2)+cells(x,2) 'add amounts for same
customer!
found=1
exit for
end if
next
if found=0 then
fnd=fnd+1
dataarray(Fnd,1)=cells(x,1).value 'populate customer # into array
DataArray(Fnd,2)=cells(x,2).value 'populate Sales Amt #1 into Array
end if
x=x+1
Loop

sheets("sheet2").select
Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is
empty!
x=activecell.row+1
cells(x,1).value=TheDate
let y=2
do while true
if cells(1,y).value=empty then exit do
for z=2 to fnd
if cells(1,z).value=dataarray(Y,1)
cells(X,Z).value=dataarray(y,2)
dataarray(Y,3)="FOUND"
exit for
end if
next
y=Y+1
Loop
z=z-1
'now make sure all customers are in sheet2
for Y=1 to fnd
if dataarray(Y,3)<"FOUND" then
z=z+1
cells(1,z).value=dataarray(Y,1) 'put customer # on row 1
cells(x,z).value=dataarary(Y,2)
end if
Next


end sub

"Peanut" wrote:

This won't work. The data in the first worksheet is not static. Which is
the reason why I need the macro to paste the values into a separate
spreadsheet.

"Mike H." wrote:

Change the data in the first sheet to have the date in a column:
Customer 1 4/4/06 $250.00
Customer 2 4/4/06 $300.00
Customer 3 4/4/06 $1,000.00

Then do a sumproduct in the second sheet:
the $250 in the intersection of "Cust 1" (Colum B) and 4/4/06 (Row 2) would
be:

=SUMPRODUCT((Sheet1!A1:A2500=Sheet2!B1)*(Sheet1!B1 :B2500=Sheet2!A2)*(Sheet1!C1:C2500))

Note you can just say A:A, B:B and C:C if using Excel 2007. Otherwise you
have to have a finite limit to the number of rows.


Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00