How to tell the order in which an event occurred?

[Camanarac]

Good day!
please look at the attached excel.
Each row describes a document and the times when a revision for the document occured.
Column H specifies the number of revissions.

Columns C,D,E,F,G specifies the time when a revission occurred. If the column is 1, this is the first revission occurred. Then, the maximum value in each row describes the time when the last revission occurred. When there is no value in any column, it means that no such event occurred for that particular row.

What I am trying to do is to order in columns K, L, M, N, O, in which order did each event occur.

For example, Row 4 is correct with its current formula, but row 18 should be 1,2,3,4,5 (or 1,2,4,3,5, since revissions C & D occurred the same day)

Getting the first & last is easy (I can use MAX & MIN formulas) but getting the middles is what I am finding hard to do.

How would you do it?

[Camanarac]

Actually, Row 18 would be more correct as 1,2,3,3,4

[MarvinP]

Hi Camanarac and welcome to the forum,

I have read your words and looked at your sample and don't understand it.

In Columns E to I you call them TIME. Is that Time of Day? I just don't see like in row 18 you have 1331 occurences with 5 different Revs but the sum of the columns E to I don't add to 1331.

Perhaps you could give a better or real example of what is going on here. I'm thinking you are in charge of a programming development company and each document is a peice of code. Each time someone checks it out to work on it that would be called an occurence. When they check it back in you see how many lines of code have changed so that might be ????

I'm so lost on this question. Please try again.

[Camanarac]

Thanks Marvinp for the welcome.

About the work, I am reviewing the historic data of communications between a designer, a contractor, and their client. Each document may be either an original study for engineering, design criteria or design sheets.

This is a portion of a larger spreadsheet, which is bigger that 6mb so I just posted some relevant columns. Now I have added the formulas with which "CASE" and "OCCURRENCE" were calculated to provide a better understanding. In ROW 18 this says that CASE 11111 (The case when a document was reviewd by A,B,C,D,E) occurred 1331 times. Anyway they are wrong because the "CASE" evaluated who reviewed each document, but not the sequence in which the document was reviewed. I am trying to define in which sequence was the document reviewed because that would give me more detailed cases and I can see what was the most common way to review a document.

TIME occurred specifies the day when a revision occurred. For example, the document in row 7 was revised by "A" on the first day, by "C" on the ninth day and by "B" on the 56th day. Then from that data, I can conclude that the order of the revisions was A - First,C - Second,B - Third, giving me a SEQ_CASE 13200. The sequence analysis shows this, but my actual formula works sometimes where the document received only 3 revisions, sometimes not and never if the document had 4 or 5 revisions.

The SEQ_CASE should be a 5-digits code which answers the question: Who reviewed the document first? Who last? In what order where the in-between revissions?

Best regards,
Carlos.

[MarvinP]

Hi camanarac,

Find the attached, now that makes more sense to me.
I used the Rank formula with an IfError conditon to get the answer (I hope) you want.

Also find a quick pivot table to count the number of each sequence.

See the attached.

[Camanarac]

Thanks for teaching me the RANK formula

You are great!

[MarvinP]

The hard part of many of these questions is to simply understand what the question is.
The fun part is to see how many ways people have of coming up with answers.

I've learned as much as I've helped others.