Advanced Sorting

[Bigg Al]

OK I've spent ages racking my brains on how to do this:

I get a lot of data in raw text form, where all values are inline such as this

Please Login or Register  to view this content.

What I want to be able to do, is either find an option, write a function to sort it on a new page, or even make a macro to sort it into a table in a more useful format of:

Please Login or Register  to view this content.

So that I can then use the data for further work, without having to move it around by hand. This is not too inconvenient for my example, but when you have a few hundred entries and several values for each, it can become rather time-consuming and unproductive, the type of task computers were made to do for us. I know how I would go about doing this as a *nix shell script or in C++, but I would prefer a solution where I can just click a button from within excel.

[StephenR]

This assumes your data starts in A1. You can use formulae to do this but I can't remember exactly how.

Please Login or Register  to view this content.

[Mark@Work]

An alternative would be to use the INDEX() function
so if your original data is in column A sheet1 and your re-ordered data is in A-D of sheet 2 then:

Sheet2!A1 : =INDEX(Sheet1!$A:$A,COLUMN()+(ROW()-1)/4)

replicate across 4 columns and down as required.

Mark.

[allstarphg]

guys, ineed help how to separate male and female result with high to low sorting?


NAME SCORE
max 5
william 3
susan 7
john 7
cindy 9
lucy 2
thomas 4
jacob 11
lily 9

male score (high to low) score female score (high to low) score

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Pete_UK]

@allstarphg,

Further to Glenn's post, if you read the forum rules at the top of the screen, you will see that you should not attempt to hijack someone else's thread by asking your own question (Rule 04) - instead you should start your own thread, with a link to this one if you think it is relevant. Please note as well, that the thread you are subscribed to is over 11 years old.

Also, you will need another column of data to distinguish between male and female names.

Pete

[FDibbins]

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.