hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi linda,

You need to take help of pivot table. Just Go to the pivot table wizard put
columnA in rows, colmnB in columns and again columnB in data area. I think it
will give you the desired result.



"linda" wrote:

hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

The output will look like ...

ColumnA mod1 mod2 mod3 (blank) Grand Total
cust1 2 1 3
cust2 1 2 3
(blank)
Grand Total 2 2 2 6


"linda" wrote:

hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi Irfan..
thanks for reply..i already try that before but thats not what i want
because i dont want user to choose which value they want to view.my
suggestion right now is to do it manually in macro but i dont have any idea
in the coding.

your help is very appreciated=)
--
Regards,
Linda


"Irfan Khan" wrote:

The output will look like ...

ColumnA mod1 mod2 mod3 (blank) Grand Total
cust1 2 1 3
cust2 1 2 3
(blank)
Grand Total 2 2 2 6


"linda" wrote:

hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi,

You have to select the list; copy it to the clipbaord and then use the
"Paste Special commad from the Edit menu, in a different location in the
worksheet.

Note: You might have to repeat this process more than one to achive what you
require.

Challa Prabhu

"linda" wrote:

hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi,

Small correction- forgot to use the "Transpose" command in my earlier post.

- Corrected the procedure again-

You have to select the list; copy it to the clipbaord and then use the
"Paste Special commad from the Edit menu, and then select the Transpose
check box and click OK, in a different cell location in the
worksheet.

Note: You might have to repeat this process more than one to achive what you
require.

Challa Prabhu

"linda" wrote:

hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi challa!

thanks for reply...but your reply doesnt solve my problem..an error occur
when i try to use transpose function.The information cannot be pasted because
the copy area and the paste area are not the same size and shape.

you help is very appreciated.
--
Regards,
Linda


"challa prabhu" wrote:

Hi,

Small correction- forgot to use the "Transpose" command in my earlier post.

- Corrected the procedure again-

You have to select the list; copy it to the clipbaord and then use the
"Paste Special commad from the Edit menu, and then select the Transpose
check box and click OK, in a different cell location in the
worksheet.

Note: You might have to repeat this process more than one to achive what you
require.

Challa Prabhu

"linda" wrote:

hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

A pivot table is static, that is, it doesn't update until you tell it to do
so. To prevent even this, you could create the pivot table, copy the table,
and use Paste Special - Values to obtain an unchanging table.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi Jon!

thanks for reply..sorry for the unclear explaination..i know that the pivot
table will not update unless i ask it to update..what i mean by static in my
previous post is user cannot edit the table to show only what they want
to.the table will only update if there's new data added.i've try to use paste
special function but thats not helping at all.

your help is really appreciated.
--
Regards,
Linda


"Jon Peltier" wrote:

A pivot table is static, that is, it doesn't update until you tell it to do
so. To prevent even this, you could create the pivot table, copy the table,
and use Paste Special - Values to obtain an unchanging table.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Linda, you have had some good replies. However, if you still want to do
this with a macro, post back and I'll post the code. James

"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi James!

hope you can send me the code cause i need to do it in macro..using wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda


"Zone" wrote:

Linda, you have had some good replies. However, if you still want to do
this with a macro, post back and I'll post the code. James

"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and has at
least one blank row after it and at least one blank column to the right of
it.
2. The workbook has a second worksheet available, and the new table will be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add a
button to run it.
James

Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub


"linda" wrote in message
...
Hi James!

hope you can send me the code cause i need to do it in macro..using wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda


"Zone" wrote:

Linda, you have had some good replies. However, if you still want to do
this with a macro, post back and I'll post the code. James

"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create
it
using pivot table cause i want it to be static..i hope to do this in
macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution
for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Wow,its really amazing!Great job!Your help is really appreciated,Thanks James=)

can i ask you some more?how to copy data to another worksheet using macro?
i have a worksheet that contains all the raw data[column A-O] that i get
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?

--
Regards,
Linda


"Zone" wrote:

Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and has at
least one blank row after it and at least one blank column to the right of
it.
2. The workbook has a second worksheet available, and the new table will be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add a
button to run it.
James

Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub


"linda" wrote in message
...
Hi James!

hope you can send me the code cause i need to do it in macro..using wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda


"Zone" wrote:

Linda, you have had some good replies. However, if you still want to do
this with a macro, post back and I'll post the code. James

"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create
it
using pivot table cause i want it to be static..i hope to do this in
macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution
for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Glad it works for you, Linda. As a matter of curiosity, how many Custs and
Mods did you have? It should accommodate any reasonable number of these.

On your question about copying columns, if you want the copy to
automatically update everytime the original changes, you'll want to use
references instead of the actual values. Say your original data begins in
cell A1 of Sheet1 and you want your copy to begin in cell A1 of Sheet3. In
A1 of Sheet3, type in
=Sheet1!A1
then drag down the column as far as needed. In B1 of Sheet3, type in
=Sheet1!B1
and drag down the column as far as needed. I don't see why you would need a
macro for this unless the length of the columns is changing when you refresh
the data. If so, post back and we'll tackle that. Cheers! James

"linda" wrote in message
...
Wow,its really amazing!Great job!Your help is really appreciated,Thanks
James=)

can i ask you some more?how to copy data to another worksheet using macro?
i have a worksheet that contains all the raw data[column A-O] that i get
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?

--
Regards,
Linda


"Zone" wrote:

Hi Linda,
This macro assumes that:
1. The original table begins in cell A1 of the first worksheet and has
at
least one blank row after it and at least one blank column to the right
of
it.
2. The workbook has a second worksheet available, and the new table will
be
the only thing on the second worksheet.
If these assumptions are correct,
1. Open the workbook in Excel
2. Show the code editor by pressing Alt-F11
3. If the workbook doesn't have a regular code module, insert one by
clicking Insert on the menubar, then Module.
4. Copy the code below and paste it in the module.
5. Press Alt-F11 to return to the spreadsheet.
6. To run the macro, click Tools on the menubar, then Macro, then
Macros,
and select CustModTable from the list, then Run.
Post back and let me know if the code works for you. If so, we'll add a
button to run it.
James

Sub CustModTable()
Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range
Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As
Variant
Worksheets(2).Activate
Cells.ClearContents
ToRow = 2: ToCol = 1: FromCol = 1
With Worksheets(1)
For FromRow = 1 To .Cells(1, "a").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToRow = ToRow + 1
End If
Next FromRow
[a1] = "Cust"
ToRow = 1: ToCol = 2: FromCol = 2
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, FromCol)
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(ToRow, ToCol) = This
ToCol = ToCol + 1
End If
Next FromRow
For ToRow = 2 To Cells(1, "a").End(xlDown).Row
For ToCol = 2 To Cells(1, 1).End(xlToRight).Column
Cells(ToRow, ToCol) = 0
Next ToCol
Next ToRow
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row
This = .Cells(FromRow, "a")
This2 = .Cells(FromRow, "b")
Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole)
Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1
Next FromRow
End With
End Sub


"linda" wrote in message
...
Hi James!

hope you can send me the code cause i need to do it in macro..using
wizard
doesnt solve my problem.
your help is really appreciated.
--
Regards,
Linda


"Zone" wrote:

Linda, you have had some good replies. However, if you still want to
do
this with a macro, post back and I'll post the code. James

"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like
this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to
create
it
using pivot table cause i want it to be static..i hope to do this in
macro
cause i want to assign it to a button but i'm not really familiar
with
macro..so,i dont have any idea to solve my this.is there any
solution
for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

hi James!

pivot table would give me the table like i want but i dont want to create it
using wizard.is there any macro to auto create a pivot chart & table?cause i
want to assign a button for it.

--
Regards,
Linda


"Zone" wrote:

Linda, you have had some good replies. However, if you still want to do
this with a macro, post back and I'll post the code. James

"linda" wrote in message
...
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to
create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Another thought ..

Assume source data in Sheet1, within say A1:B100

In Sheet2,
you've got the custs listed in A2 down, the mods in B1 across (as posted)

Put in B2:
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1: $B$100=B$1))
Copy B2 across and fill down to populate the table
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"linda" wrote:
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Hi Max!

thank you for reply..but can you explain me more details because after i've
try your suggestion
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1: $B$100=B$1))
but its not working.

your help[ is really appreciated.
--
Regards,
Linda


"Max" wrote:

Another thought ..

Assume source data in Sheet1, within say A1:B100

In Sheet2,
you've got the custs listed in A2 down, the mods in B1 across (as posted)

Put in B2:
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1: $B$100=B$1))
Copy B2 across and fill down to populate the table
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"linda" wrote:
hi!

i've got a table in excel worksheet which part of it looks like this:

columnA|column B|
cust1 | mod1
cust2 | mod2
cust1 | mod1
cust1 | mod2
cust2 | mod3
cust2 | mod3

but,i need to recreate the table in a new worksheet to be analyze,to create
chart from the data..this is what i expect:

cust | mod1 | mod2 | mod3 |
cust1| 2 | 1 |
cust2| 0 | 1 | 2

i've try to use filter,but still can't do this..i dont want to create it
using pivot table cause i want it to be static..i hope to do this in macro
cause i want to assign it to a button but i'm not really familiar with
macro..so,i dont have any idea to solve my this.is there any solution for
this?if yes,how?

thanks in advanced.
--
Regards,
Linda

Here's a link to a working sample:
http://www.flypicture.com/download/MTQ4NDE=
Linda_sumproduct.xls

If the earlier suggestion didn't work as-is, it's probably because your
source data in Sheet1 and/or your row/col headers entered in Sheet2 weren't
consistent, perhaps due to extraneous white spaces here & there. You could
wrap TRIM around both source/headers for increased robustness, viz,

In Sheet2,
Put instead in B2:
=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$100)=TRIM($A2))*( TRIM(Sheet1!$B$1:$B$100)=TRIM(B$1)))
Copy B2 across and fill down to populate the table
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"linda" wrote:
Hi Max!

thank you for reply..but can you explain me more details because after i've
try your suggestion
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1: $B$100=B$1))
but its not working.

your help[ is really appreciated.
--
Regards,
Linda

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


"Max" wrote:

Here's a link to a working sample:
http://www.flypicture.com/download/MTQ4NDE=
Linda_sumproduct.xls

If the earlier suggestion didn't work as-is, it's probably because your
source data in Sheet1 and/or your row/col headers entered in Sheet2 weren't
consistent, perhaps due to extraneous white spaces here & there. You could
wrap TRIM around both source/headers for increased robustness, viz,

In Sheet2,
Put instead in B2:
=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$100)=TRIM($A2))*( TRIM(Sheet1!$B$1:$B$100)=TRIM(B$1)))
Copy B2 across and fill down to populate the table
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"linda" wrote:
Hi Max!

thank you for reply..but can you explain me more details because after i've
try your suggestion
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1: $B$100=B$1))
but its not working.

your help[ is really appreciated.
--
Regards,
Linda

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