#### 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

• 14.9 KB Views: 5

#### Eloise T

##### Active Member
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?

#### CMLET

##### Member
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

• 16.8 KB Views: 9

#### XOR LX

##### Active Member
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.

Regards

Last edited:

#### GraH - Guido

##### Well-Known Member
@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.

#### CMLET

##### Member
Hi GraH, Great Advice, and I agree. ..and...(( your help is Exemplary!!))+10