Trendering Ranks

[JordanR]

Kind of lost here and hoping for some help:
I get a list, every day, of items that I need to track their placement (or rank) on the list. I've tried looking into the Rank function and I've even used a forumla to just give me a numbered list and they work relatively well. Problem is that I have no way of turning that into a trend over time.
Example:
11/1: A, C, D, B, E
11/2: C, A, E, D, B
11/3: E, A, C, B, D

I'm trying to get to a point where I can create a sheet that has that data listed vertically, and can graph the ranking of each letter over time (so I can see A went to the second position and stayed there for two weeks in a row)

Any ideas?

 

[shades]

Howdy. This can be done. Are those columns? And you are using rank to get results in those cells?
________
Buy air one vaporizer

 

[shades]

Assuming "yes" to those questions, here is a possible approach.

Put the dates in column B, beginning at B2, down. Then put numbers 1 through 5 in cells C1 to G1. Fill in the table as you have.

I created a dynamic named range, which automatically resizes when you add new dates and data.

MyValu is the named range and is defined this way:

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),5)

Then in cells J1 through N1, put the letters A through E.

In cell J2 put this formula:

=INDEX(MyValu,1,MATCH(J$1,$C2:$G2,))

Copy it across to to column N

Then copy all of those columns down as far as your data.

Now base you graph on this formula table.

(see attachment: first worksheet is Data, containing two tables above; second worksheet is Graph and includes the graph from the second table on Data.)

Is this what you were thinking?
________
Og Kush

 

[JordanR]

Wow, sorry I didn't come back to check on this answer. This is outstanding and helps a great deal!
Thanks.