Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Old September 13th 07, 10:30 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 205
Default how to analyze data?

ooo,i see..
thanks for that,James
you are really helpful;-)
--
Regards,
Linda


"Zone" wrote:

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
who to analyze data with more than 30,000 rows in excel Jane Excel Discussion (Misc queries) 5 March 5th 07 11:25 PM
Analyze Data tokentrinkit Excel Worksheet Functions 3 August 18th 06 08:01 PM
How do I statistically analyze data across multipe worksheets? Ottomatic New Users to Excel 3 January 19th 06 10:41 PM
Analyze & Filter data in a notepad file Rudodoo Excel Discussion (Misc queries) 1 December 1st 05 10:58 AM
How do I analyze data from a survey in excel LaReina Excel Worksheet Functions 1 June 25th 05 11:43 PM


All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017