D
David
Nope as far as I know there's no refrence to word in my code. Thanks
for your help though.
- David
for your help though.
- David
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
mahesh
Hi,
I need to draw a pareto chart based on a list of values, which are
formula-driven.
First I need to sort the values, take the cumulative, calculate the
percentage and then plot a pareto chart.
As I know, excel doesn't allow sorting of formula-driven values.
So, effectively, I need some option, function or code which takes these
formula-driven values and sort them, so that I can plot a pareto chart
Please clarify.
Thanks and regards
Mahesh
I need to draw a pareto chart based on a list of values, which are
formula-driven.
First I need to sort the values, take the cumulative, calculate the
percentage and then plot a pareto chart.
As I know, excel doesn't allow sorting of formula-driven values.
So, effectively, I need some option, function or code which takes these
formula-driven values and sort them, so that I can plot a pareto chart
Please clarify.
Thanks and regards
Mahesh
N
Norman Jones
Hi Mahesh,
Can you give an example of formula values which do not permit sorting?
Can you give an example of formula values which do not permit sorting?
M
mahesh
Hi Norman,
It's not exactly "formula values do not permit sorting", but "sorting
doesn't take formula fields". For example, if you have a list of 5
values in column A. Take the cumulative, for each cell, in the adjacent
column B. And take the percentage of each value corresponding to the
total value and place them in column C
Now select all the columns and sort by column A.
The result will be that only Col A will be sorted and not the other
two.
It is understood that, when you are selecting all the columns, all the
values should align accoridngly with the sorted col.
Could I answer you?
Thanks
Mahesh
It's not exactly "formula values do not permit sorting", but "sorting
doesn't take formula fields". For example, if you have a list of 5
values in column A. Take the cumulative, for each cell, in the adjacent
column B. And take the percentage of each value corresponding to the
total value and place them in column C
Now select all the columns and sort by column A.
The result will be that only Col A will be sorted and not the other
two.
It is understood that, when you are selecting all the columns, all the
values should align accoridngly with the sorted col.
Could I answer you?
Thanks
Mahesh
G
Guest
Is it possible to adapt your code to sort a column in numerical sequence that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get sequence all
ways got 1122334455667788 needed 12345678
any way
Thanks
(e-mail address removed)
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get sequence all
ways got 1122334455667788 needed 12345678
any way
Thanks
(e-mail address removed)
T
Tom Ogilvy
in Column C put in this formula in the first row
=countif($B$1:B1,B1)
then drag fill down the column.
Select column C, do Edit=>Copy, then Edit=>Paste Special and select Values.
Now sort the data with column C as the first key, then column B as the
second key.
This is fairly trivial - make an effort. - pay attention to the
instructions.
=countif($B$1:B1,B1)
then drag fill down the column.
Select column C, do Edit=>Copy, then Edit=>Paste Special and select Values.
Now sort the data with column C as the first key, then column B as the
second key.
This is fairly trivial - make an effort. - pay attention to the
instructions.
G
Guest
did follow also have tried int still get 1122334455667788 want 12345678 then
repeat till entire column sorted
repeat till entire column sorted
T
Tom Ogilvy
I see in another thread, you posted sample code that is sorting on column C,
but your original post said your data was in column B. You also appear to
want to sort the rows out to column t, so the information I gave you would
be put in column U and you would sort on U as your primary key.
=countif($C$1:C1,C1)
but your original post said your data was in column B. You also appear to
want to sort the rows out to column t, so the information I gave you would
be put in column U and you would sort on U as your primary key.
=countif($C$1:C1,C1)
T
Tom Ogilvy
Either you haven't correctly described what you want to do, or you didn't
apply the solution properly.
for example, in your sample code you posted you do
for t = 2 to cells(100,3).End(xlup).Column step 8
cells(100,3).End(xlup).Column will always return 3, so your loop would
only do one pass.
you also select Range(Cells(t, 3), Cells(t + 3, 3)).Select
and then sort only the selection. This would sort C2:C5 on the one pass
you make, but you said you want to sort 8 cells. Also, it appears you want
to sort all the columns from A to T (you explanations are not that clear),
so you would not just sort column C alone.
I posted some corrections in that thread, but hopefully you can understand
that I don't particularly accept you answer that you triet it and it failed.
That may very well be, but it would work for the problem as I understood you
to described it (which may not be the problem).
apply the solution properly.
for example, in your sample code you posted you do
for t = 2 to cells(100,3).End(xlup).Column step 8
cells(100,3).End(xlup).Column will always return 3, so your loop would
only do one pass.
you also select Range(Cells(t, 3), Cells(t + 3, 3)).Select
and then sort only the selection. This would sort C2:C5 on the one pass
you make, but you said you want to sort 8 cells. Also, it appears you want
to sort all the columns from A to T (you explanations are not that clear),
so you would not just sort column C alone.
I posted some corrections in that thread, but hopefully you can understand
that I don't particularly accept you answer that you triet it and it failed.
That may very well be, but it would work for the problem as I understood you
to described it (which may not be the problem).
G
Guest
i caught onto the relationship of columns so it did sort. Only i am after a
sort of 12345678 not 111222333444555 this is my problem. need to sort into
groups of 8 repeated till column is sorted. the other post has had diferent
ways to try maybe i left some trial data in.
sort of 12345678 not 111222333444555 this is my problem. need to sort into
groups of 8 repeated till column is sorted. the other post has had diferent
ways to try maybe i left some trial data in.
T
Tom Ogilvy
If you want to send a sample workbook, I will set it up for you.
(e-mail address removed)
(e-mail address removed)
G
Guest
entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8
Here are a copy paste from a worksheet need the columns to sort 12345678. as
you can see I have been trying. So far no luch
Thanks
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8
Here are a copy paste from a worksheet need the columns to sort 12345678. as
you can see I have been trying. So far no luch
Thanks
T
Tom Ogilvy
Data value Qty
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8
so you don't have an equal number of each value (1 to 8)
How do you want them sorted (assume the first 1 is in C2 for each case)
1 2 3 4 5
6 7 8 1 2
3 4 5 6 7
or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
what to do about incomplete sets?
and, this cetainly doesn't match any of your descriptions or your code.
If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i > 8 then i = 1
cell = i
Next
or
i = 0
for each cell in range("C2:C17")
i = i + 1
if i > 8 then i = 1
cell.Resize(1,5).Value = i
Next
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8
so you don't have an equal number of each value (1 to 8)
How do you want them sorted (assume the first 1 is in C2 for each case)
1 2 3 4 5
6 7 8 1 2
3 4 5 6 7
or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
what to do about incomplete sets?
and, this cetainly doesn't match any of your descriptions or your code.
If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i > 8 then i = 1
cell = i
Next
or
i = 0
for each cell in range("C2:C17")
i = i + 1
if i > 8 then i = 1
cell.Resize(1,5).Value = i
Next
G
Guest
in the column to be sorted needs to finish out at 12345678 / 12345678 until
it does the entire column. I am sure I can adjust for the columns if I have a
macro that will sort.
I've pulled my hair out what I have left !!!
Thanks Much
it does the entire column. I am sure I can adjust for the columns if I have a
macro that will sort.
I've pulled my hair out what I have left !!!
Thanks Much
G
Guest
Sorry didn"t get all sort is down a column not across a row
Thanks
Curt
Thanks
Curt
G
Guest
incomplete sets just are that incomplete 12345---
Thanks again
Thanks again
T
Tom Ogilvy
Sub AAAA()
Dim cell As Range
Dim rng As Range
Dim rng1 As Range
For Each cell In Range("C2:G2")
Set rng = Range(cell, cell.End(xlDown))
cell.Offset(0, 1).EntireColumn.Insert
Set rng1 = cell.Offset(0, 1).Resize(rng.Count, 1)
rng1.Formula = "=Countif(" & rng(1).Address(1, 1) & _
":" & rng(1).Address(0, 0) & "," & _
rng(1).Address(0, 0) & ")"
rng1.Formula = rng1.Value
Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _
key1:=cell.Offset(0, 1), Order1:=xlAscending, _
key2:=cell, Order2:=xlAscending
cell.Offset(0, 1).EntireColumn.Delete
Next
End Sub
produced:
1 1 1 1 1
2 2 2 2 2
3 8 3 3 3
4 1 4 4 4
5 2 5 5 5
6 1 6 6 6
7 2 7 7 7
8 1 8 8 8
1 2 1 1 1
2 1 2 2 2
3 2 3 3 3
4 1 4 4 4
6 2 5 5 5
7 1 6 6 6
8 2 8 7 7
7 1 8
with your test data.
Use the method I originally advised, applied to the multicolumn layout of
your data and the statement (as I understood it) that you wanted each column
sorted separately. As I understand it, that is what you want.
Dim cell As Range
Dim rng As Range
Dim rng1 As Range
For Each cell In Range("C2:G2")
Set rng = Range(cell, cell.End(xlDown))
cell.Offset(0, 1).EntireColumn.Insert
Set rng1 = cell.Offset(0, 1).Resize(rng.Count, 1)
rng1.Formula = "=Countif(" & rng(1).Address(1, 1) & _
":" & rng(1).Address(0, 0) & "," & _
rng(1).Address(0, 0) & ")"
rng1.Formula = rng1.Value
Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _
key1:=cell.Offset(0, 1), Order1:=xlAscending, _
key2:=cell, Order2:=xlAscending
cell.Offset(0, 1).EntireColumn.Delete
Next
End Sub
produced:
1 1 1 1 1
2 2 2 2 2
3 8 3 3 3
4 1 4 4 4
5 2 5 5 5
6 1 6 6 6
7 2 7 7 7
8 1 8 8 8
1 2 1 1 1
2 1 2 2 2
3 2 3 3 3
4 1 4 4 4
6 2 5 5 5
7 1 6 6 6
8 2 8 7 7
7 1 8
with your test data.
Use the method I originally advised, applied to the multicolumn layout of
your data and the statement (as I understood it) that you wanted each column
sorted separately. As I understand it, that is what you want.
G
Guest
is there something I can add into "For Each cell In Range("C2")" to have it
disregard text in the cells. Would like to use a pull down menu for data
entry into the cell. Thanks it does the job extremely well. Once I relized it
stops where there blanks. With this new sheet there won't be any blanks.
Thanks Again
(e-mail address removed)
disregard text in the cells. Would like to use a pull down menu for data
entry into the cell. Thanks it does the job extremely well. Once I relized it
stops where there blanks. With this new sheet there won't be any blanks.
Thanks Again
(e-mail address removed)
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.