Excel Up/Down Arrows to Show Position Change

[RajJol]

Hi All,

I currently have a XLSM spreadsheet that displays a number of records (approx 50). These records are updated on an hourly basis and sometimes change position based on the latest version of data once refreshed.

I want to be able to add arrows in an extra column to display the movement of records once data has been refreshed (e.g. if Mike was 1st but has moved 2nd after the data was refreshed, I want an arrow pointing down so we are able to see Mike has moved down from his last position. Similarly, if another record has moved up or stayed in its same position, then a symbols indicating this).

The spreadsheet currently has a ranking column which simply puts the position number of each record in descending order (e.g. 1st, 2nd, 3rd etc), I am assuming we would need to hold this in a variable and compare against its new ranking to define if the record has moved up/down or still the same.

Any help would be much appreciated.

Thanks in advanced.

 

[Gavona]

Wingdings font, =char(225) or =char(226) for the arrows.

Simply use a macro to copy the current records to a new sheet and to name that range "OldData"**

In this example the keyfield (name?) is in columnA. The current rank is in ColumnB.

I bring the old rank into columnC using
=VLOOKUP(A9,OldData,2,0)

And columnD (formatted to use wingdings font) shows the direction of travel arrows using the formula:
=IF(B9=C9,"",IF(B9<C9,CHAR(225),CHAR(226)))

You can combine the formulae in columns C&D if you want. Or adapt the approach to use Index and Match instead of vlookup.



**For macro advice use forum707. But try recording

clicking in the first cell of your range.

Ctrl-Shft-End or Edit.Goto,Special CurrentRegion
Hopefully one of these will select your entire data table but it depends on the construction of your workbook

Ctrl-C to copy
Click into the second worksheet and paste (or PasteSpecial, Values if necessary)
The pasted values should at this stage be highlighted.
Insert,Name,Create to name this range OldData

Post the recorded code for further help (in Forum707)


Gavin

 

[AnotherHiggins]

Based on your saying you have an "XLSM" file, I assume you're using Excel 2007 or 2010.

If that's the case, then you can use conditional formatting to display very nice looking green/yellow/red arrow Icons.

This is a sample of what you could produce:

The trick is that you can't use the Icons for custom-defined formulas in the Conditional Formatting (at least not in 2007 - maybe they've updated that in 2010?).

To get around that I'd do something similar to what Gavin suggested as far as saving the previous positions somewhere.

But then, in an adjacent column, use a formula like =If(NewPosition < OldPosition, 1, If( NewPosition > OldPosition, -1, 0))

That will return:
[tt]+1 [/tt]for people who improved
[tt]-1 [/tt]for people who got worse
[tt] 0 [/tt]for people who remained in the same position

Now select that new column and in the Home ribbon, go to Styles > Conditional Formatting > Icon Sets and select the set you want to use - probably the first one. Either go into More Rules at the beginning of the process (at the bottom of the list of Icon sets) or go back into Manage Rules after you've created the Icons and tick the box for Show Icon Only.

Menu Structure:

Show Icon Only dialog:

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.