|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
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 |
how to analyze data?
Welcome, Linda.
Suggest you put in a fresh, new posting for your new query, perhaps in excel.programming newsgroup -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "linda" wrote in message ... ok,got it!thanks Max=) .... |
how to analyze data?
Ok Max!Thanks anyway;-)
-- Regards, Linda "Max" wrote: Welcome, Linda. Suggest you put in a fresh, new posting for your new query, perhaps in excel.programming newsgroup -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "linda" wrote in message ... ok,got it!thanks Max=) .... |
how to analyze data?
In , linda
spake thusly: 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? A good source of excellent working examples for this -- one of which I myself was able to use successfully for a similar purpose last month -- is he http://www.rondebruin.nl/tips.htm In any case, I agree that Max gives great formula help. =dman= |
how to analyze data?
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 |
how to analyze data?
Suggest you put in as a fresh, new posting for this, Linda. In your new
posting, you could paste the actual formulas that you're using. It'll be easier for responders to diagnose what's wrong. Generally, ensure that ranges used are identically sized and sufficiently cover the max expected source ranges. And if you're receiving zero returns where it obviously shouldn't be, look out for possible data consistency issues, eg: extra "invisible" white spaces -- as mentioned earlier in this thread -- where the liberal use of TRIM around the source ranges and the lookup values in the sumproduct would then help improve robustness of matching and correct returns. Example In B2: =IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1:$ A$100=$A2)*(Sheet1!$B$1:$B$100=B$1))) You could try instead in B2, with TRIM: =IF(OR(TRIM(B$1)="",TRIM($A2)=""),"",SUMPRODUCT((T RIM(Sheet1!$A$1:$A$100)=TRIM($A2))*(TRIM(Sheet1!$B $1:$B$100)=TRIM(B$1)))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
how to analyze data?
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 |
how to analyze data?
Ok Max,thanks anyway!;-)
-- Regards, Linda "Max" wrote: Suggest you put in as a fresh, new posting for this, Linda. In your new posting, you could paste the actual formulas that you're using. It'll be easier for responders to diagnose what's wrong. Generally, ensure that ranges used are identically sized and sufficiently cover the max expected source ranges. And if you're receiving zero returns where it obviously shouldn't be, look out for possible data consistency issues, eg: extra "invisible" white spaces -- as mentioned earlier in this thread -- where the liberal use of TRIM around the source ranges and the lookup values in the sumproduct would then help improve robustness of matching and correct returns. Example In B2: =IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1:$ A$100=$A2)*(Sheet1!$B$1:$B$100=B$1))) You could try instead in B2, with TRIM: =IF(OR(TRIM(B$1)="",TRIM($A2)=""),"",SUMPRODUCT((T RIM(Sheet1!$A$1:$A$100)=TRIM($A2))*(TRIM(Sheet1!$B $1:$B$100)=TRIM(B$1)))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
how to analyze data?
Thanks James!it works=)
your help is really appreciated! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
Thanks Dallman!
-- Regards, Linda "Dallman Ross" wrote: In , linda spake thusly: 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? A good source of excellent working examples for this -- one of which I myself was able to use successfully for a similar purpose last month -- is he http://www.rondebruin.nl/tips.htm In any case, I agree that Max gives great formula help. =dman= |
how to analyze data?
Hi James!
i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
Hi Linda,
If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
its true i need to use the if statement:
=IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
Linda, yes, I see the problem. Change the last part of the code like this:
For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
million of thanks,James!its work!
thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
Glad it worked for you, Linda. Cheers, James
"linda" wrote in message ... million of thanks,James!its work! thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
Hi James!
its me again,Linda=) i dont know what happen but i cannot reuse your code anymore.the 1 that already in use run perfectly but if i want to do it again,the code doesn't run till end. the error highlighted on this line: Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) can u help me if i'am wrong somewhere? thanks in advanced~ -- Regards, Linda "Zone" wrote: Glad it worked for you, Linda. Cheers, James "linda" wrote in message ... million of thanks,James!its work! thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
Hi Linda,
Just happened to run across your post. What is the error message? When the code chokes, rest the mouse over the word This and see what it is. James "linda" wrote in message ... Hi James! its me again,Linda=) i dont know what happen but i cannot reuse your code anymore.the 1 that already in use run perfectly but if i want to do it again,the code doesn't run till end. the error highlighted on this line: Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) can u help me if i'am wrong somewhere? thanks in advanced~ -- Regards, Linda "Zone" wrote: Glad it worked for you, Linda. Cheers, James "linda" wrote in message ... million of thanks,James!its work! thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 |
how to analyze data?
Thanks for your help,James!
the error message: This = Error 2023 -- Regards, Linda "Zone" wrote: Hi Linda, Just happened to run across your post. What is the error message? When the code chokes, rest the mouse over the word This and see what it is. James "linda" wrote in message ... Hi James! its me again,Linda=) i dont know what happen but i cannot reuse your code anymore.the 1 that already in use run perfectly but if i want to do it again,the code doesn't run till end. the error highlighted on this line: Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) can u help me if i'am wrong somewhere? thanks in advanced~ -- Regards, Linda "Zone" wrote: Glad it worked for you, Linda. Cheers, James "linda" wrote in message ... million of thanks,James!its work! thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 ... |
how to analyze data?
James,
where i need to change my code if i want to place the raw data and the analyze table in the same worksheet?assuming that i have 1 worksheet.raw data in column O and P,while the analyzed data will be place start from cell A1.i change your code as below and it run ok,but then the raw data was also deleted.how to avoid that?cause i want the raw data to be there. 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(1).Activate ' Range("A1:G30").Select Cells.ClearContents ToRow = 2: ToCol = 1: FromCol = 15 With Worksheets(1) For FromRow = 1 To .Cells(1, "O").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 = 16 For FromRow = 1 To .Cells(1, "P").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, "P").End(xlDown).Row This = .Cells(FromRow, "O") This2 = .Cells(FromRow, "P") 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 thanks=) -- Regards, Linda "Zone" wrote: Hi Linda, Just happened to run across your post. What is the error message? When the code chokes, rest the mouse over the word This and see what it is. James "linda" wrote in message ... Hi James! its me again,Linda=) i dont know what happen but i cannot reuse your code anymore.the 1 that already in use run perfectly but if i want to do it again,the code doesn't run till end. the error highlighted on this line: Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) can u help me if i'am wrong somewhere? thanks in advanced~ -- Regards, Linda "Zone" wrote: Glad it worked for you, Linda. Cheers, James "linda" wrote in message ... million of thanks,James!its work! thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 ... |
how to analyze data?
Hi Linda,
This code is intended to run on 2 sheets. I wouldn't try to run it one sheet or you'll surely run into problems. James "linda" wrote in message ... James, where i need to change my code if i want to place the raw data and the analyze table in the same worksheet?assuming that i have 1 worksheet.raw data in column O and P,while the analyzed data will be place start from cell A1.i change your code as below and it run ok,but then the raw data was also deleted.how to avoid that?cause i want the raw data to be there. 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(1).Activate ' Range("A1:G30").Select Cells.ClearContents ToRow = 2: ToCol = 1: FromCol = 15 With Worksheets(1) For FromRow = 1 To .Cells(1, "O").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 = 16 For FromRow = 1 To .Cells(1, "P").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, "P").End(xlDown).Row This = .Cells(FromRow, "O") This2 = .Cells(FromRow, "P") 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 thanks=) -- Regards, Linda "Zone" wrote: Hi Linda, Just happened to run across your post. What is the error message? When the code chokes, rest the mouse over the word This and see what it is. James "linda" wrote in message ... Hi James! its me again,Linda=) i dont know what happen but i cannot reuse your code anymore.the 1 that already in use run perfectly but if i want to do it again,the code doesn't run till end. the error highlighted on this line: Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) can u help me if i'am wrong somewhere? thanks in advanced~ -- Regards, Linda "Zone" wrote: Glad it worked for you, Linda. Cheers, James "linda" wrote in message ... million of thanks,James!its work! thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then 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 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: 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 ... |
All times are GMT +1. The time now is 11:08 AM. |
|
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com