If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to analyze data?
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 |
#2
|
|||
|
|||
how to analyze data?
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 |
#3
|
|||
|
|||
how to analyze data?
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 |
#4
|
|||
|
|||
how to analyze data?
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 |
#5
|
|||
|
|||
how to analyze data?
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 |
#6
|
|||
|
|||
how to analyze data?
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 |
#7
|
|||
|
|||
how to analyze data?
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 |
#8
|
|||
|
|||
how to analyze data?
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 |
#9
|
|||
|
|||
how to analyze data?
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 |
#10
|
|||
|
|||
how to analyze data?
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 |
Thread Tools | |
Display Modes | |
|
|