Hi Max!
regarding your posted sumproduct function..i tried to create 2 tables in a
worksheet.like this..i have 1 worksheet contain all the raw
data[338R*15C].i'll create another worksheet that contain analyze data[i use
the sumproduct function to get it]..i limit the first table[ws1.columnB *
ws1.columnO] in the second worksheet as 40R*40C and another table[ws1.columnH
* ws1.columnN] will be create on A41 and i limit it to another 40R*40C.but
the data for second table not appear eventhough there's no error.
is it the sumproduct function cant be used in the same worksheet?cause it is
ok when i try create it in a new single worksheet.hope my explaination is
clear.
thanks in advanced!
--
Regards,
Linda
"Max" wrote:
You could try this in a copy of the earlier sample file provided ..
In Sheet1,
In C1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)1,"",ROW()))
Copy C1 to D1, fill down to cover the max expected extent of source data in
cols A and B
In Sheet2,
In B1:
=IF(COLUMNS($A:A)COUNT(Sheet1!$D:$D),"",INDEX(She et1!$B:$B,SMALL(Sheet1!$D:$D,COLUMNS($A:A))))
Copy B1 across by as many cols as there are unique mods expected
In A2:
=IF(ROWS($1:1)COUNT(Sheet1!C:C),"",INDEX(Sheet1!A :A,SMALL(Sheet1!C:C,ROWS($1:1))))
Copy A2 down by as many rows as there are unique custs expected
In B2:
=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1:$ A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
Copy B2 across/fill down to populate the grid
(above is a slightly revised version of the earlier formula)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"linda" wrote:
Thanks Max!its work!=)
but,did i need to fill in myself the title of columns & rows?can it be auto
calculate too?cause right now i have 24 customer,23 module and around 350
rows of data that would change anytime.the data will only display customer
with open module.
if the customer have close all module at particular time,i dont want it to
be in the table..only customer with open module will be display.if i fill it
myself,there's may be customer with close module included.hope that i've
explain you clearly.
Your help is really appreciated.
--
Regards,
Linda