• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDIRECT(ADDRESS--vs--OFFSET?

CMLET

Member
Hello everyone, I'm working on a formula, need help. Attached file Sample001, Column (A) are 13 values in 500 Rows. Desired results are in cells C2:C31~O2:O31.
The current row cell in Column (A2) represents; 13th of the 13 values, exclude duplicates, then 12, 11, 10 so on. Example in Column (R2)(S2). Note: In Column (A3) the value 132 is 13th of the 13 values as indicated in Column (H3). Sincerely, CMLET.
 

Attachments

  • Sample001.xlsx
    14.9 KB · Views: 5
I got lost at your 2nd paragraph. Please give an example of what you are trying to accomplish. e.g. are the rows supposed to add up or what?
 
Hi Eloise T. I included an update in Sample001. 13 Values in Column (A) are numbered according to Occurrence. (Most recent) would be 13th.
The previous cell 12th. This process repeats from the beginning in each cell going downward. Thank You, CMLET
 

Attachments

  • Sample001.xlsx
    16.8 KB · Views: 11
Hi,

What determines the range in column A to be considered for each row?

In your example (for row 2), in column Q you have taken the values from A2:A33 only. Why? What range should be considered when calculating the results for row 3, row 4, etc.?

My first thought was that it should be A2:A501 for row 2, A3:A501 for row 3, A4:A501 for row 4, etc., so I used the following in C2:

=14-MATCH(C$1,MODE.MULT(IF(FREQUENCY($A2:$A$501,$A2:$A$501),$A2:$A$501,{"",""})),0)

and copied across and down.

(Note that, if you are not using an English-language version of Excel, the separator in the part {"",""} may required amending.)

This gives results identical to yours in rows 2 to 6, but from row 7 onwards does not always agree for all cells in a given row.

Please clarify.

Regards
 
Last edited:
@CMLET, To which extend is this different from your previous post? Which was solved by P45cal and John JV, offering 3 possibilities by the way.

Why aren't there any formulae in your original post, though you give it a title "INDIRECT(ADDRESS--vs--OFFSET?" What is the link?

We, who try to help you, are confused by the way you present your cases. Even your answers to additional questions sometimes fail to clarify, because you change your samples. I kindly invite you to please spend a bit more time in making your cases more clear, providing samples with formulae (attempts), step by step requirements, manual solutions with explanations, etc. Otherwise it may get frustrating on both ends, which no-one wants. Thank you for considering.
 
Back
Top