Use ranks to order text items

[BelfastHatter]

I have a spreadsheet that ranks the movements in our accounts and pulls in a description if the difference is over a certain limit. We then manually usee a concetenate formulae to put the text for each group of accounts into one cell, putting the highest valued difference first in the text string.

I know the formulae to rank the differences but I do not know how to use the ranking to generate the text for the explanation of the group of accounts. We use Excel 2000 (Don't get me started on that) and I am not allowed to use Macro's for this as it will result in too much paperwork.

An example of the data would be we have 5 rows (rows 2 to 6)for sales info, that we explain the movement on row 7. The movement for each row is in column B, the ranking in column C and the explanation in column D.

Account Movement Rank Explanation
A 10,000
B 20,000 2 £20k Increase in B Reason
C -25,000 1 £25K Decrease in C Reason
D -5,000
E 15,000 3 £15K Increase in E Reason
Sales £25K Decrease in C Reason £20K Increase in B Reason & £15K increase in E Reason

Thanks in Advance

 

[sijpie]

This is very simple: if you cannot use macros, then you cannot automate comments in other cells.

In your reason column you could have complicated =IF() formulas
=IF(B23>A23, "Decrease because of...","Increase because of...)
you can nest IF() statements to give you more options, but it becomes unwieldy very quickly.

From the short example you have given I can't decipher why you have ranked number 1,2 and 3, or what the formula is behind that ranking.

 

[BelfastHatter]

The formula behind the ranking is =Rank(ABS(A2),A2:A6)

The rason for each line is already in done as an if statement.

It is the formula to combine aal of the reasons in to one cell, in the order that they are ranked that is causing an issue.

I know I can use a vlookup in a concatenate formulae, but as the number of reasons may be different each month, the formula would have to be ammended each month, the problem with that is a different person would be doing this task each monthand the some of the users are not very good with Excel beyond the basics.