How to show largest values?

[Magnus86]

Hi,

I have 100 companies, with financials for 3 years. I wanna sort them by 2011 financials, from the one company with largest turnover from left to right. What is the easiest way of doing it?

Much appreciated!

[dilipandey]

Hi Magnus86,

Left to right ?
Need to see the data, please upload a sample file. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[Magnus86]

Forgot the worksheet, here it is!!!

[Magnus86]

"Left to right" yeah, just in order with the "largest" company from left to right. Thanks!

[oeldere]

This looks to me it is an simular question as this one.

http://www.excelforum.com/excel-gene...html?p=3093305

[Fotis1991]

=LARGE($B$7:$J$7,COLUMN(A1))

Dreg to the right

[dilipandey]

Select the first data set, press Alt + d + s,
you'll get sort window
click on options, Orientation -> Sort left to right.


Regards,
DILIPandey
<click on below * if this helps>

[Magnus86]

This looks to me it is an simular question as this one.

http://www.excelforum.com/excel-gene...html?p=3093305

That is my question! And I donīt think it is the same question. Now I have to sort. In the last one I had already sorted...

EDIT: sorted manually, took me a day

[Magnus86]

Select the first data set, press Alt + d + s,
you'll get sort window
click on options, Orientation -> Sort left to right.


Regards,
DILIPandey
<click on below * if this helps>

Thanks!!!!

[Magnus86]

Select the first data set, press Alt + d + s,
you'll get sort window
click on options, Orientation -> Sort left to right.


Regards,
DILIPandey
<click on below * if this helps>

Hi again,

how do i do this if I have hidden columns and just want the visible columns to be sorted? Now I have 100 columns, but only 33 of them is visible because I have hidden all the columns I am not interested in. I want to sort by one of the values in the columns. Really hard to explain...

I try to show in a worksheet.

[Magnus86]

Please look in the worksheet I posted before. Letīs hid the columns for 2010 and 2009 since I am only interested in turnover by 2011. How do I sort now? Sort by turnover...

[dilipandey]

Hi Magnus86,

If you sort these columns, their place will be changed and it will be difficult because of hidden columns.. please post a sample with your expected results so that I can give it a try. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[Magnus86]

Hi dilipandey,

it seems to be a problem with your answer. I canīt see nothing but "Hi Magnus86,"

[dilipandey]

Okay.. refresh again and you'll find my response. There was some posting error occurred.

I checked which you posted in post#11 and when tried sorting, Excel has given an error message that sort cannot be performed with multiple selections.
If you want you can fetch these data basis some lookup logic

Regards,
DILIPandey
<click on below * if this helps>

[bebo021999]

Hi Magnus86.
Does it convenience if a copy of data will be sorted in another sheet? The new data will be linked and updated automatically from the data source.

[MitchC]

I've attached a file that solves your problem. It isn't capable of knowing which columns you've hidden, but it can sort based on a given date (in your example, I've used the date 1/12/2011, but it's an input and can be changed to whatever you want).

Note that this formula will work if each of your categories has the same number of elements (3 entries for "A", 3 for "B", 3 for "C"). You can copy paste the formula down and add more columns as long as each category has the same number of columns. If you're data is bigger and more complex (i.e. not all the same), then I can create a similar formula that will work but you will need to upload the data.

Hope this helps.

sort by turnover.xlsx

[Magnus86]

Thanks! Ok, so in "Input1" I have my data as it is today. The figures 1,2,3,4 etc. at top of the sheet, starting in B1 are replacing the actual company names for security reasons. In "expected!" I want the companies sorted by "Turnover" which you find in B3 for company 1, H3 for company 3 etc.

NOTE: if it is impossible or very hard to get the columns with 2010 and 2009 figures to come with, I am happy just to get the list which company have the largest turnover in 2011, second largest and so on...

Is that sufficient information? Thank you so much for trying to help me

[Magnus86]

Here is the file...

[Magnus86]

I've attached a file that solves your problem. It isn't capable of knowing which columns you've hidden, but it can sort based on a given date (in your example, I've used the date 1/12/2011, but it's an input and can be changed to whatever you want).

Note that this formula will work if each of your categories has the same number of elements (3 entries for "A", 3 for "B", 3 for "C"). You can copy paste the formula down and add more columns as long as each category has the same number of columns. If you're data is bigger and more complex (i.e. not all the same), then I can create a similar formula that will work but you will need to upload the data.

Hope this helps.

Attachment 210103

Thanks! I am gonna try this ASAP!

[Magnus86]

I've attached a file that solves your problem. It isn't capable of knowing which columns you've hidden, but it can sort based on a given date (in your example, I've used the date 1/12/2011, but it's an input and can be changed to whatever you want).

Note that this formula will work if each of your categories has the same number of elements (3 entries for "A", 3 for "B", 3 for "C"). You can copy paste the formula down and add more columns as long as each category has the same number of columns. If you're data is bigger and more complex (i.e. not all the same), then I can create a similar formula that will work but you will need to upload the data.

Hope this helps.

Attachment 210103

This was a very useful formula! Thanks! Still, it would be better if it could sort it too, since I have hundreds of companies to sort! Letīs see if dilipandey is able to make that work!

[bebo021999]

A solution may be helpful with data sorted automatically in other sheet (sheet "sort"). In yellow cell at first row, you can change the year if you want.

[MitchC]

So you want to actually move the columns around so that they are ordered...not just get a list of the top 10 or whatever? And do you want to show all 3 years once they are ordered or just 2011?

[Magnus86]

So you want to actually move the columns around so that they are ordered YES...not just get a list of the top 10 or whatever? NOT REALLYAnd do you want to show all 3 years once they are ordered or just 2011?YES PLEASE

Please see blue

[MitchC]

The solution by Bebo is what I would try and do...you'll need to do a bit more work to get it to work for multiple years but they idea is in the formula he provides.

[Magnus86]

I've attached a file that solves your problem. It isn't capable of knowing which columns you've hidden, but it can sort based on a given date (in your example, I've used the date 1/12/2011, but it's an input and can be changed to whatever you want).

Note that this formula will work if each of your categories has the same number of elements (3 entries for "A", 3 for "B", 3 for "C"). You can copy paste the formula down and add more columns as long as each category has the same number of columns. If you're data is bigger and more complex (i.e. not all the same), then I can create a similar formula that will work but you will need to upload the data.

Hope this helps.

Attachment 210103

Hi again,

since the real names arenīt 1,2,3 etc but real company names, the formula is no longer good for this purpose since you Put A,B and C in the formula... You get what I mean? :/

[Magnus86]

A solution may be helpful with data sorted automatically in other sheet (sheet "sort"). In yellow cell at first row, you can change the year if you want.

And how do I extend the formula to all my companies? See attached sheet.

[Magnus86]

Or just extend it so it works for all the 125 companies, with the names 1,2,3 etc as in the sheet I posted. I managed to extend it but I canīt change "A";"B";"C" to 1-125 in the same order. Anyone know how to extend the last part in the formula?

[bebo021999]

This is a new version to meets your real data. As the file is too big to attach, I deleted some most right columns. You can copy last column into more columns to match your real data.

[Magnus86]

This is a new version to meets your real data. As the file is too big to attach, I deleted some most right columns. You can copy last column into more columns to match your real data.

Awesome, thank you very much and big ups for your patience!

[bebo021999]

You are wellcome! Nice to help.