Help requested! How to re-rank with saving value of one row

[uarshad83]

Hi,

I am not an expert of Excel but learning as much as I can. Currently, I created a Ranking Matrix with linking formulas on 3 sheets:

1. Criteria - have some factors
2. Input - input fields
3. Ranking - calculation & final result


On ranking sheet I have results from COL - D to O with below formula:

=IF(AND(Input!$B3="L",Input!$C3="L",Input!D3="Y"),"1",IF(AND(Input!$B3="L",Input!$C3="M",Input!D3="Y"),"2",IF(AND(Input!$B3="L",Input!$C3="H",Input!D3="Y"),"3",IF(AND(Input!$B3="M",Input!$C3="L",Input!D3="Y"),"2",IF(AND(Input!$B3="M",Input!$C3="M",Input!D3="Y"),"4",IF(AND(Input!$B3="M",Input!$C3="H",Input!D3="Y"),"5",IF(AND(Input!$B3="H",Input!$C3="L",Input!D3="Y"),"3",IF(AND(Input!$B3="H",Input!$C3="M",Input!D3="Y"),"5",IF(AND(Input!$B3="H",Input!$C3="H",Input!D3="Y"),"6","0")))))))))

COL - P = Total Score (simply adding result from COL - D to O) in P
COL - Q = Total Weighted Score (multiplying values in COL D to Q with their respective factors % which is mentioned in ROW - 3) below is the formula

=IF(P4="","",D4*$D$3+E4*$E$3+F4*$F$3+G4*$G$3+H4*$H$3+I4*$I$3+J4*$J$3+K4*$K$3+L4*$L$3+M4*$M$3+N4*$N$3+O4*$O$3)

COL - R = Ranking = Based on COL - Q results I am ranking all values with formula:

=IF(Q4="","",RANK(Q4,S$4:S$100,0))+COUNTIF(Q$4:Q4,Q4)-1

It is giving me my required result in the form of 1,3,5,6,2,4... not in sequence

What I need to do next - I want to know and guidance:

1. If I can sort values automated by ranking - mean in Ranking column it will rank 1,2,3,4...in sequence and also shuffle all ranking if any ranking changed based on input sheet. Please let me know if I can do it by any formula.

2. In input sheet there are some issues on which I am working (and categorized with In Progress or Fixed) in input sheet (COL-P). So if issue is currently In Progress on Ranking sheet I am getting result as required but once issue is Fixed I want to:
- Save the Ranking of that Issue on same Row with current result (in COL D-R) and Re-Prioritize all other Issues excluding the Fixed one. Can I do it with putting some logical formulas in Excel or not.


Any help and guidance will be really appreciated.

Thanks for your time for reading and response.

Profound Regards,

 

[SirJB7]

Hi, uarshad83!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, give a look at the uploaded file.
Col Y: =FILA()-3-----> in English: =ROW()-3
Col Z: =INDICE(B$4:B$100;S4;0)-----> in English: =INDEX(B$4:B$100,S4,0)

Regards!

 

[uarshad83]

Hi SirJB7,

Thanks for your warm welcome, my apology I didn't check that link yet but for sure going to check it.

Really appreciate your response and adding formulas in excel. Probably, I remained unable to convey my point and problem. I just added my question in excel sheet with red text, if you can please review and share your feedback that would be really helpful.

Regards,

 

[SirJB7]

Hi, uarshad83!

Data at columns B:R is the actual data, nor sorted, ordered, pulled out from, that's to say, whatever columns you add after them will not rearrange its position.

In order to do that you have 2 choices:
a) you keep column S as it's required by formulas of columns Y:Z, and with the same criteria you can add more columns building an ordered new set of data (B:R, static original data, Y:Z & more, dynamic ranked data)
b) sort data of rows 4:99 by columns S and then A or B as you require (you have a problem of combined cells that you have to solve first to proceed sorting).

Regards!