Search:

# Ranking Columns by Highest Revenues

Asked By: Thomas    Date: Oct 14    Category: MS Office    Views: 609

I have an income statement [excel spreadsheet] run via our financial
reporting tool. Column A is hidden ; Column B contains line
descriptions. Columns D-F is for Consolidated Summary;
Columns OQ is for "Company A" data, Columns SU is for "Company B"
data, Columns WY is for "Company C" data etc.

The report can be run for 1 company or all companies [ 20 in total].

My question is that I need to re -arrange the columns with company
data as per the above according to the company with the highest
revenue. The revenue total is on line 25.
The final spreadsheet should have the data for the company with the
highest revenue in cols OQ and the next highest in cols SU etc.

Share:

The following solution depends on there being something in cells
O1:Q1, nothing in cell R1, something in S1:U1, nothing in V1, etc.

First insert a temporary blank row at the very top of the sheet.
In cell O1 place this formula:
=IF(N2="",O26,N1)
(Note that the 26 in the formula is the line  containing the revenue
(it has temporarily moved one line down), and the O is the column  I've
assumed contains the value you want to sort on (if its P or Q, change
to suit).)

Copy this formula across (with the fill handle) as far as the last
company plus one more column (to CP1 for 20 companies).
You should now have the revenue for each company  repeated on line one
4 times.

Now to sort. Starting from O1, select the block of cells to CPx, x
being the number of rows you want to include in the sort. Now go to
Data|Sort.., click the Options button and choose 'Sort left to right',
click OK, choose 'Row 1' in the topmost field, and choose
'Descending', finally, click OK.

Now just delete the top row. QED.

Thanks for the solution. I will try it and let you
know. There is nothing in column  R nor V.

I have tried your suggestion.It works to a certain
extent ...

Column "O" equates to "Year to Date" and column  "Q"
equates to "Current Month" . When I do a sort I want
the "Year to Date" and "Current Month" columns  for
each company  to "move" as one.

Also what should happen to the "empty" columns in
between ? I need to maintain a blank space between 2
different companies i.e. column R should always be
"blank".

Please get back to me if you require further details

Thanks ...

it retained the initial column
order for each company  (Year to Date, Revenue, Current Month) along
with a blank column  between companies.

The file you sent me off-list (too confidential I think for me to post
here) is considerably more complex, and does not satisfy the criteria
I stated were needed for my previous solution to work. However the
following worked:
1. Unmerge merged cells on row 10, from O10 and to the right. (If you
have Excel 2003 onwards,you can take each group of three cells,
unmerge them and then use 2003's 'centre across selection' under the
Horizontal alignment section of the alignment tab of the Format Cells
dialogue box.)

2. In cell N8 enter this formula:
=Q25

3. In cell O8 enter this formula:
=N8

4. Unhide column  P and enter this formula into P8:
=N8

5. In cell Q8 enter this formula:
=N8

6. Select the 4 cells N8:Q8 and using the fill handle fill to the
right to cell BE8.

7.Select all cells from N8:BE709

8. Go to Data|Sort, click Options, sort left to right, OK,
choose Row 8, descending, OK.

9. Now you can delete data  in cells N8:BE8, rehide unhidden column.

10. QED

I will have another go. Thanks for not posting the
file.

I was thinking of maybe recording a macro to do the
sort and "tidy up " the columns. So when the FD runs
the report  there won't be any effort required by him.

in revealing cols OQ as in OP means columns  O
to Q.
Being so, we would
copy revenues and its column  nr side by side;
sort this pair by revenues;
copy blocks of data  of each company  by the column nr to next sheet

blocks of data of each company being
if revenue is in column Q (icol=17)
then data to copy are columns(icol -2 : icol)

Didn't find what you were looking for? Find more on Ranking Columns by Highest Revenues Or get search suggestion and latest updates.