Sorting Collection

[David]

Nope as far as I know there's no refrence to word in my code. Thanks
for your help though.

- David

 

[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

 

[Norman Jones]

Hi Mahesh,

Can you give an example of formula values which do not permit sorting?

 

[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

 

[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)

 

[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.

 

[Guest]

did follow also have tried int still get 1122334455667788 want 12345678 then
repeat till entire column sorted

 

[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)

 

[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).

 

[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.

 

[Tom Ogilvy]

If you want to send a sample workbook, I will set it up for you.

(e-mail address removed)

 

[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

 

[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

 

[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

 

[Guest]

Sorry didn"t get all sort is down a column not across a row
Thanks
Curt

 

[Guest]

incomplete sets just are that incomplete 12345---
Thanks again

 

[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.

 

[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)