x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 196

SORT DATA IN EXCEL 2010

Hello,

I have data from "A" to "AD" which I sometimes have to sort.  Is it possible to sort these 30 letter sequences in both blocks of 3, 4 and 5 rows high so that I get every combination possible in either columns of 3, 4 or 5 rows high depending on what I need..  For example, blocks of 3 rows high would give abc, abd, abe...all the way to aad then bcd, bce, bcf
...all the way to bad.  The amount of lines would be astronomical I guess that's why I thought I would ask first.

I have attached a file with a small example.

Thank you,

Blueyes727 SORT.xlsx
0
blueyes727
• 10
• 8
1 Solution

Chief EngineerCommented:
Hello Blueyes727,
I'm not sure I totally understand exactly what you want but I can tell you this much.

For a combination of 3 characters with each character having 30 possible values (from A - AD) you would have 27,000 possible combinations (30^3).  For 4 characters you have 810,000 possible combinations (30^4) and for 5 characters you have 24,300,000 possible combinations (30^5).

So, you are correct, the number of possible combinations gets to be "astronomical".

Kyle
0

Commented:
I think the combinations will be huge but somewhat less then exponential. Because it is a combination problem. The results will be similra (but not identical) to 30C3, 30C4, 30C5.
I'm trying to provide you a solution (let's see how close am I able to get to your expectation).
I'll post the code once it works.
0

Author Commented:
Well instead of every combination, if taking only abc, abd, abe, all the way to ab-ac-ad, it would give roughly 140,000 combinations so that would be better.  And if it's only around 140,000 for the 5 number set then it would  be much less for the 4 and 3 number sets.

Or even just take the first 20 letters and forget about the last 10.  Maybe that would be better.

Thank you,

Blueyes727
0

Commented:
The attached file has the code and button to execute it. Please go to the "test" worksheet. You need to enter a few paramaeters to control the functionality. Please let me know if it works for you.

SORT.xlsm
0

Author Commented:
It worked fine up until row 5 to 19, on row 5 to 20 I got an error msg.  I've attached the file.

This is awesome.

Blueyes727

SORT-TEST.xlsm
0

Author Commented:
Another thing I notice is the data I am pasting in this file does not sort except for the 1st column.  I will need the entire rows to be sorted also.  I have attached a sample of what I mean.

Thank you,

Blueyes727
SORT-TEST-2.xlsm
0

Author Commented:
Also forgot to mention the rows will not always be the same length, it will depend on the dates I am sorting.

Blueyes727
0

Commented:
The file was getting long and the variable used for storing row index was integer type, which encountered an overflow. Fixed it.
SORT-TEST.xlsm
0

Commented:
Working to display the other cols....
0

Author Commented:
It's working great but it's still sorting only the first column and not the entire rows.  As mentioned the rows will not always be the same lenght depending on the dates I must sort.

When sorting a, b, c, etc, I need the row sorted also, not just the 1st column.

That is why I have a row with dates at the top.

Thank you,

Blueyes727
0

Author Commented:
ok, thank you.

Blueyes727
0

Commented:

0

Author Commented:
Sorry I should of wrote B-AC-AD.  This would be for the 3 row column.

Blueyes727
0

Commented:
No problem. I had a wrong assumption about the combination (thought it is always three letter). I'll make some changes to address your need.
0

Commented:
SORT-TEST.xlsm
0

Author Commented:
It works perfectly thank you very much.

The only problem now is my Excel sort file which dlmille made for me is slowing me down because of all the data I\m generating.

I'm not sure what to do with this.

Thank you,

Blueyes727.
0

Commented:
I do not have any background about the slowness issue that you mention. If you wish to address that issue, I recommend that you post a new question with the details about what you have and what's causing you pain.

Good Luck.
SB
0

Author Commented:
Ok, I'll see how it goes.

Thank you very much for your help, it's really appreciated.  The file works perfectly.

Blueyes727
0

Author Commented:
Thak you very much.

Blueyes727
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.