• 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.

=IF(COUNTIF($A$1??

CMLET

Member
Hi Everyone. Need help with a formula. In the Sample01, Column A:A, contains 13 values in 1000 Rows. Column B "expected result" is a single Ranked occurrence value.
Column E are the 13 values sorted. Refer to Columns (H1:H13), (I1:I13). (A:1, 121) occurred 26 rows down, "Its the largest number of the 13 values, according to its occurrence, and it is Ranked (1st) and will be listed in column B:1,= 1. (A:2, 112) occurred 19 Rows down. Counting down from A:2, 112 was the 2nd largest number of the 13 values, according to its occurrence, and Column B:2,= 2. Thanks for any help or advice in furthering this request.
 

Attachments

GraH - Guido

Well-Known Member
Like Ali, I'm confused... 121 occurs 70 times, 112 appears 83 and 211 counts 87 times.
So where are your numbers coming from, CMLET?
I made a simple pivot, using "1" as "Att" in column B.
Then using rank.
59756
 

CMLET

Member
Hi, In Sample02, I've inserted a new cells A:1, B:1 highlighted. 122 is Ranked 1 in (B:1) because it occurred 19 rows down. Example; (H:2, I:2).
If I had written 121 in (A:1), (B:1) would then be Ranked 13. I suppose this calculating process is independently from its previous cell?? Thank you all again for your help and advise.
 

Attachments

GraH - Guido

Well-Known Member
Would it be like this then?
You actually want to rank based on next occurrence?

I made 2 helper tables:
1st one finds the next position for 100 positions of all values [G1]:=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$1001)/($A$1:$A$1001=G$2),$F4)-N(G3),"")
2nd one ranks them [V1]:=IFERROR(RANK.EQ(G5,$G5:$S5,1),"")

In B1 down: =INDEX($V$4:$AH$103,COUNTIF($A$1:$A1,A1),MATCH(A1,$V$2:$AH$2,0))
In the upload I had this one wrapped in an iferror(), but that seems redundant.
 

Attachments

Last edited:

CMLET

Member
Thank You for your assistance. In Sample03 I put the "expected" values in (B:1 ~ B:62) manually. GraH, your helper taables are awesome, but I was still getting a different result??
 

Attachments

GraH - Guido

Well-Known Member
Care to explain how you get to those different results. So far I've just been guessing how you would do it manually.
I've added your sample3 in my file so we can compare. Can you add in that file a step by step guidance how you do it manually please.
 

Attachments

CMLET

Member
Hi GraH. In Sample04, (A:1) 211. Please refer to Column; (E:12). Occurrence in Column (F:12) is "3" and in Column (G:12), the rank is "11". Column (A:2), 121 highlighted in blue... occurrence is "26" and the rank is "2". =MATCH(A1,A2:$A$1000,0) only lists the last occurrence per the individual cell. I'm grouping the 13 values, from each of the current cell down containing one of the 13 values, and ranking from Highest to lowest 1 to 13. Thank you for being so patient as I am getting confused myself..lol
 

Attachments

GraH - Guido

Well-Known Member
Thank you for putting my patience to the test. The more you try to explain, the less I seem to understand.
If you would manage to make the pattern clear, some-one is able to help. However I'm failing to do so to be blunt.

I do believe I group per 13 as there are 13 values in play.
I do believe I find the next occurrence and express the result in number of rows down.
I do believe I manage to rank those 13 results.

Can you explain why in your first table of 13 (yellow), you have ranked value 123 with rank 1? According to you the next time 123 appears is 17 rows down? I see 123 appearing the first time in row 18 and the second time in row 21, thus 3 rows down.

Or you are saying that value 123 is 17 rows down from the start position the first time round? How would that pattern evolve then? Every 13th row?

Now I do have a headache...
 

CMLET

Member
Hi GraH. the last Sample04...(A:18 cell) 123, the rank in (B:18)= 11 because at that starting point the 123 occurred 3 rows down. (A:21 cell) 123 occurred 26 rows from that starting point and the (B:21 cell) rank is 1. All 13 values are counted down from each starting point and ranked accordingly. PS. I really like your helper tables and have been considering your method over mine.
 

CMLET

Member
=RANK(B1,OFFSET($B$1,MATCH(A1,$A$1:$A$100,0)-1,,COUNTIF($A$1:$A$100,A1),)) this partially works. Needs some adjustments.
 

CMLET

Member
=SUMPRODUCT((A2:INDEX(A2:A$1002,MATCH(A1,A2:A$1002,0))<>"")/COUNTIF(A2:INDEX(A2:A$1002,MATCH(A1,A2:A$1002,0)),A2:INDEX(A2:A$1002,MATCH(A1,A2:A$1002,0))&"")) Hi GraH. You are terrific! However, this needs to be used as the "next offset" column which I am not sure how to incorporate. Many thanks for the last ditch effort but, there is no there..there yet!!!
 

CMLET

Member
H GraH. All or nothing...take a quick look at Sample05. Row1 columns (d~p) are 13 rank'd i.d.'s for the 13 values. Column B are expected Ranks.
The values in cell Columns (d~p) are chronological order (no duplicate) across. Yellow highlights show in Row 1 (d~p) the corresponding Ranks.
Getting a formula to manipulate the data into that format and then decipher for the Ranks into column "B" is way over my pay grade. Thanks for all and your many efforts. CMLET
 

Attachments

GraH - Guido

Well-Known Member
Sorry I give up. I fail to see the full pattern/logic you apply.
You rank 211 "first occurrence" as 11 which is the position of the second "sorting" and so on. I do not get how that sorting is done. "Decipher" is the appropriate word here.
 

p45cal

Well-Known Member
In column C of the attached I've managed to duplicate your manual results in column B, using helper columns D : P (an array entered formula across those columns) and the unique list in U2:U14 (which doesn't need to be sorted btw).
I can't get the formula into one cell yet. (TRANSPOSE shouldn't be necessary in a one-celled formula)
 

Attachments

CMLET

Member
Hi p45cal! You're amazing! I've been at it an gave up like GraH. Now I back in business! How did you come up with Transpose?? Many Thanks and appreciations.
 

GraH - Guido

Well-Known Member
I can't get the formula into one cell yet.
Even with your fine solution, I still can't fully understand the initial query. Except I know now I was heading in the wrong direction. Good luck with that one cell formula... #OutOfMyLeague for sure.
 

p45cal

Well-Known Member
I still can't fully understand the initial query.
I can't understand why the OP would want such data…
I've given up trying to get into one cell - even with INDEX(MATCH($U$2:$U$14,$A4:$A$1000,0),,) (to try and allow non-array-entered formulae).
 
Top