Solved

SORT DATA IN EXCEL 2010

Posted on 2011-09-20
19
187 Views
Last Modified: 2012-05-12
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
Comment
Question by:blueyes727
  • 10
  • 8
19 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 36568519
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36568635
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 Comment

by:blueyes727
ID: 36568668
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36570030
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 Comment

by:blueyes727
ID: 36570128
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 Comment

by:blueyes727
ID: 36570189
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 Comment

by:blueyes727
ID: 36570229
Also forgot to mention the rows will not always be the same length, it will depend on the dates I am sorting.

Blueyes727
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36570472
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36570525
Working to display the other cols....
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:blueyes727
ID: 36570534
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 Comment

by:blueyes727
ID: 36570539
ok, thank you.

Blueyes727
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36570669
Please provide an example of what you expect to see in 3 rows when your combination is: "BAD" (combining "B and "AD")


0
 

Author Comment

by:blueyes727
ID: 36570678
Sorry I should of wrote B-AC-AD.  This would be for the 3 row column.

Blueyes727
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36571072
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
 
LVL 10

Accepted Solution

by:
SANTABABY earned 500 total points
ID: 36571657
Please try the attached workbook.
SORT-TEST.xlsm
0
 

Author Comment

by:blueyes727
ID: 36574271
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36575082
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 Comment

by:blueyes727
ID: 36575228
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 Closing Comment

by:blueyes727
ID: 36575235
Thak you very much.

Blueyes727
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now