A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to analyze data?



 
 
Thread Tools Display Modes
  #1  
Old August 13th, 2007, 10:34 AM posted to microsoft.public.excel.misc
Linda
external usenet poster
 
Posts: 661
Default 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  
Old August 13th, 2007, 10:46 AM posted to microsoft.public.excel.misc
Irfan Khan[_2_]
external usenet poster
 
Posts: 24
Default 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  
Old August 13th, 2007, 10:48 AM posted to microsoft.public.excel.misc
Irfan Khan[_2_]
external usenet poster
 
Posts: 24
Default 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  
Old August 13th, 2007, 12:54 PM posted to microsoft.public.excel.misc
challa prabhu
external usenet poster
 
Posts: 750
Default 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  
Old August 13th, 2007, 01:22 PM posted to microsoft.public.excel.misc
challa prabhu
external usenet poster
 
Posts: 750
Default 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  
Old August 13th, 2007, 01:34 PM posted to microsoft.public.excel.misc
Jon Peltier
external usenet poster
 
Posts: 5,018
Default 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  
Old August 13th, 2007, 02:34 PM posted to microsoft.public.excel.misc
Zone[_3_]
external usenet poster
 
Posts: 127
Default 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  
Old August 13th, 2007, 02:36 PM posted to microsoft.public.excel.misc
Max
external usenet poster
 
Posts: 8,574
Default 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  
Old August 14th, 2007, 01:58 AM posted to microsoft.public.excel.misc
Linda
external usenet poster
 
Posts: 661
Default 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  
Old August 14th, 2007, 02:34 AM posted to microsoft.public.excel.misc
Linda
external usenet poster
 
Posts: 661
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.