ok,got it!thanks Max=)

can i ask you some more?how to copy data to another worksheet?i dont want to
use the usual copy-paste cause i want to make it automatic.
i have a worksheet that contains all the raw data[column A-O] that i got it
from database.i need to copy column B & O to another worksheet and from there
the data will be analyze and display it in a chart.
the raw data will be change everytime i update it and so it will update
copied data in the second worksheet and thus update the chart.can it be
done?how?

your help is really appreciated.
--
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