S
SamDev
I have two spreadsheets in a workbook as follows:
Source Worksheet contains by customer each product it purchases and the
sales for each product (see sample below). Not all customers buy each
product. There are also new customers added/deleted each month and new
products added/deleted each month.
Column A Column B Column C
January February.....
Customer A Product Code 1 Customer A Product Code 1
100
Customer A Product Code 2
200
Customer A Product Code 3
300
Customer B Product Code 1
500
Customer B Product Code 3
600
Customer B Product Code 4
250
Customer C Product Code 2
350
I have added a column (C) in the above sheet that combines column A & B
(=A1&" "&B1) - I need this for what I need to do below (at least that is
what I came up with).
And, what I want to do in the 2nd sheet of the spreadsheet is list by each
customer in a column and then in the correpsponding row show the total sales
for each product the customer purchases. So Column A will contain a list of
all customers. Row 1 will consist of all the product codes (see sample
below).
The first empty cell in row 2 (so Customer A's purchases) will add up all of
those purchases. Column B will have a formula that adds the total sales of
Product Code 1 etc.....
I'm OK with getting the customer names & product codes to the 2nd sheet
(deleting duplicates etc) where I'm having a problem, because the number of
products and customers will change each month is trying to create a macro
that will copy the formula the correct number of times and changing the
formulas - it can be a straight copy because of absolute references in
formula.. Right now what I have done is create vlookups. So for example in
cell B2 (in the 2nd sheet) I have a formula that reads:
=IF(ISNA(VLOOKUP(A2&" "&$B$1,DATA,2,FALSE)),"",VLOOKUP(A2&"
"&$B$1,DATA,2,FALSE))
Range "data" is in the source sheet and the first column in the range is
Column C.
Second Sheet:
Column A Column B Column C
Product Code 1 Product Code 2 (assume
this is row 1)
Customer A 100 200
Customer B 500
Customer C 350
The formula above works great except I want to set up a macro that enters
the formula in the 2nd sheet in cell B2 and have it copy down and across
equivalent to the number of rows (customers)and number of columns (product
codes). The problem is I can't do a straight copy because the formula in
cell B2 has the B1 as absolute so that when it's copied down column B the
product code remains the same but for Column C the formula is
=IF(ISNA(VLOOKUP(A2&" "&$C$1,DATA,2,FALSE)),"",VLOOKUP(A2&"
"&$C$1,DATA,2,FALSE)). All of this would be OK if I knew the number of
columns I needed to perform the copy and and change the absolute value.
Maybe I have went about this the wrong way??? I'm very new to vba and figure
there must be away of having it count the number of rows in range and
telling it to repeat the same task (move over a cell and replace the
absolute cell reference and then copy)....
I have looked at arrays and crosstabs and tried those but no luck....
Any help would be appreciated....
Much thanks!!!
P.S. Sorry this is so long - I was trying to get as much info as possible to
explain my situation.
Source Worksheet contains by customer each product it purchases and the
sales for each product (see sample below). Not all customers buy each
product. There are also new customers added/deleted each month and new
products added/deleted each month.
Column A Column B Column C
January February.....
Customer A Product Code 1 Customer A Product Code 1
100
Customer A Product Code 2
200
Customer A Product Code 3
300
Customer B Product Code 1
500
Customer B Product Code 3
600
Customer B Product Code 4
250
Customer C Product Code 2
350
I have added a column (C) in the above sheet that combines column A & B
(=A1&" "&B1) - I need this for what I need to do below (at least that is
what I came up with).
And, what I want to do in the 2nd sheet of the spreadsheet is list by each
customer in a column and then in the correpsponding row show the total sales
for each product the customer purchases. So Column A will contain a list of
all customers. Row 1 will consist of all the product codes (see sample
below).
The first empty cell in row 2 (so Customer A's purchases) will add up all of
those purchases. Column B will have a formula that adds the total sales of
Product Code 1 etc.....
I'm OK with getting the customer names & product codes to the 2nd sheet
(deleting duplicates etc) where I'm having a problem, because the number of
products and customers will change each month is trying to create a macro
that will copy the formula the correct number of times and changing the
formulas - it can be a straight copy because of absolute references in
formula.. Right now what I have done is create vlookups. So for example in
cell B2 (in the 2nd sheet) I have a formula that reads:
=IF(ISNA(VLOOKUP(A2&" "&$B$1,DATA,2,FALSE)),"",VLOOKUP(A2&"
"&$B$1,DATA,2,FALSE))
Range "data" is in the source sheet and the first column in the range is
Column C.
Second Sheet:
Column A Column B Column C
Product Code 1 Product Code 2 (assume
this is row 1)
Customer A 100 200
Customer B 500
Customer C 350
The formula above works great except I want to set up a macro that enters
the formula in the 2nd sheet in cell B2 and have it copy down and across
equivalent to the number of rows (customers)and number of columns (product
codes). The problem is I can't do a straight copy because the formula in
cell B2 has the B1 as absolute so that when it's copied down column B the
product code remains the same but for Column C the formula is
=IF(ISNA(VLOOKUP(A2&" "&$C$1,DATA,2,FALSE)),"",VLOOKUP(A2&"
"&$C$1,DATA,2,FALSE)). All of this would be OK if I knew the number of
columns I needed to perform the copy and and change the absolute value.
Maybe I have went about this the wrong way??? I'm very new to vba and figure
there must be away of having it count the number of rows in range and
telling it to repeat the same task (move over a cell and replace the
absolute cell reference and then copy)....
I have looked at arrays and crosstabs and tried those but no luck....
Any help would be appreciated....
Much thanks!!!
P.S. Sorry this is so long - I was trying to get as much info as possible to
explain my situation.