I have the following data, how can I sort them into ordered array?
I have the following data, how can I sort them into ordered array?
Last edited by warakawa; 10-20-2010 at 03:13 AM.
An image is not great - just put the values in a sample file and attach it.
In the sample please also outline the "after" view - you don't stipulate how you wish to sort (horizontal, vertical etc...)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
what do you mean by sample file? Do you mean I save the data onto an Excel file and attach it?
I want to after view to have the same dimension as the original data dimension, which is A to J and 1 to 5.
Microsoft Excel 2010 32-bit and Windows 7 64-bit on Thinkpad X200T
Create a sample workbook with BEFORE and AFTER sheets showing the need and the goal.
Click GO ADVANCED and use the paperclip icon to post up your workbook.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Last edited by warakawa; 10-20-2010 at 03:31 AM.
If you don't know that a workbook is an Excel file, then maybe you don't know enough of even the basics to be attempting this. I'm not trying to be mean, just frank. The simplest of "jargon" must be in your vocabulary if we're even to have a meaningful discussion about Excel.
....it is....If sample workbook is an excel file,
We don't know what you want when you show us pictures of sample data and say "sort this into an array". That could mean anything, especially coming from someone who doesn't know that a workbook is an Excel file.how can I show you after sheets when I don't know how to do it?
So, we want you to SHOW us by creating two worksheets in your workbook, one is BEFORE...basically the table you gave before in your picture, but this time in an Excel sheet so we can see and use it as we try to help you.
The second sheet is AFTER and you manually layout your desired "sorted array" so we can see what you're talking about. We know you don't know how to do it automatically, but certainly you know what your end result needs to be, else how will anyone know if we are answering your question correctly?
Thanks for the tip, I will know what a workbook is in the future.
I have attached the before and after workbook.
Thanks.
You can use the SMALL() function to order these values since you don't mind duplicate values.
On your BEFORE sheet...pick an empty cell lower down in column A (like cell A8) and enter this formula:
=SMALL($A$1:$J$5,COLUMN(A1)+((ROW(A1)-1)*10))
Now copy that cell down and across into a range of cells the same size as your original table.
is there a way to it without using formula? I would like to use buttons on the ribbon. Formula is scary, it is impossible for me to remember the forumla.
I did what you said, it come out like this
Last edited by warakawa; 10-20-2010 at 04:52 AM.
Highlight the values you want to sort, and go to the data tab on the ribbon, select sort A-Z and it will sort them in place, the only problem is your format it will sort each column in order rather than the entire table
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
maybe I have to enter the data vertically and get the result vertically too.
Vertical is the best way!
Excel is designed to work best with data horizontal or vertical, a nice tip to make this process easier if you are going to be adding data :-
Type DATA in cell A1
higlight A1 and A2, press CTRL-L, then check the box my table has headers
you can now type your values starting at A2 down to wherever (always start in the next empty space)
You can now sort using the dropdown box on the header, and filter on items like the top 10 values etc
Or you could do what I said. Put the formula in A8 and copy down and across to A8:J12. You skipped row 8 and 9 for some reason in your pictured example.
how come you guys did not mension "sort & filter" button? That's exactly what I needed.
Well, no, given your sample data it clearly wasn't.Originally Posted by warakawa
Had you provided a completely different sample (ie a vector as opposed to a matrix) then I'm pretty sure JB would have mentioned it.
I assume vector means vertically?
single column or single row
you read your post, but I didn't understand your post.I had to go through a business statistic book.
Can anyone tell me why subscription is not working, I am not getting any email notifications.
I just thought this sub forum might have a bit more traffic and maybe someone have encountered the same problem might be able to help me out. But you are right, I should stick to the other thread, just have to come back often to check the threads I guess.
Whilst you await resolution to your email issue... you can via Quick Links etc get a listing of threads you're subscribed to
(also via your UserCP you would normally have a list appear of threads active in your session that you are subscribed to)
DO is right, always use the UserCP link on the menu bar at the top to start your session. I even have the resulting URL on my computer as the forum link so I always start there.
so the subscription works for you guys right? I just want to know if it's my problem or an issue with this forum.
Thanks.
as discussed this question is not relevant to this thread.
You are right, this is not relevant to this thread, however it is relevant to this thread
http://www.excelforum.com/the-water-...t-working.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks