You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter Ramanan Arumugam
- Start date

Welcome to the forum!

If your list could repeat, then try the following array formula in cell G2 and copy down

=INDEX(SINo,MATCH(1,SIGN(ISNA(MATCH(SINo,G$1:G1,0))+(COUNTIF($G$1:$G1,SINo)<SUMIF(SINo,SINo,RepeatAmt))),0))

enter with

If your list does not repeat, then try the following array formula in cell H2 and copy down

=INDEX(SINo,MATCH(1,SIGN(ISNA(MATCH(SINo,H$1:H1,0))+(COUNTIF($H$1:$H1,SINo)<RepeatAmt)),0))

enter with

An example of a repeating sequence is:

Right 2

Left 3

North 3

South 2

Left 4

(In this case, "Left" will be repeated for a total of 7 times.)

In the formulas, SINo refers to your text range, and RepeatAmt refers to the number of times each text string needs to be repeated.

Cheers,

Sajan.

I like to complicate things unnecessarily!

So here is a simpler version in H2

=INDEX(SINo,MATCH(1,SIGN((COUNTIF($H$1:$H1,SINo)<RepeatAmt)),0))

enter with Ctrl + Shift + Enter

For repeating sequences, try the following in cell G2:

=INDEX(SINo,MATCH(1,SIGN((COUNTIF($G$1:$G1,SINo)<SUMIF(SINo,SINo,RepeatAmt))),0))

enter with Ctrl + Shift + Enter

Cheers,

Sajan.

You have started two post about this subject both today one at 11:58 and one at 12:07.

Double posting is frowned on as it is an insult to those who have helped in one of the threads.

If you post a question in the wrong forum just start a conversation with a Ninja and ask for it to be moved, we are all here to help

I know its an old thread but I have a question re the formula from Sajan.

I had a similiar problem so looked around found this thread, tried it and it works like a charm! So massive thanks for that!

My question is, as I dont understand the formula, how does it work and how can it be broken down?

I will explain the following formula , assuming that it is the one you have used. If not , hopefully the explanation will help in understanding the other versions.

The formula is :

=INDEX(SlNo,MATCH(1,SIGN((COUNTIF($F$1:$F1,SlNo)<SUMIF(SlNo,SlNo,RepeatAmt))),0))

entered as an array formula , using CTRL SHIFT ENTER.

With the above input data , the final output is supposed to be :

Right

Right

Left

North

North

North

North

East

East

The posted formula is not supposed to start in row 1 i.e. it cannot be entered in any cell in row 1 , since it refers to a cell in that row. It can be entered in any unused cell in row 2 downwards , assuming that the cells above it do not contain the text strings which have been entered in

The outermost INDEX is simple enough to understand ; if we can generate a set of values to give us the following formula in the different cells in column F , then we will get what we want.

=INDEX(SlNo ,

=INDEX(SlNo ,

=INDEX(SlNo ,

=INDEX(SlNo ,

=INDEX(SlNo ,

=INDEX(SlNo ,

=INDEX(SlNo ,

=INDEX(SlNo ,

=INDEX(SlNo ,

These highlighted values are being generated by the following part of the formula :

MATCH(1,SIGN((COUNTIF($F$1:$F1,SlNo)<SUMIF(SlNo,SlNo,RepeatAmt))),0)

The key functions here are the

The

-1 if the numeric value is negative

0 if the number is 0

1 if the number is positive

The

You may ask why we use a SUMIF function to generate these values , when we already have the named range

The point is that the SUMIF function generates an array of values corresponding to the repeat amounts for each text string , and in the example , it will return the array {2;1;4;2}.

The

However , if the second parameter is a range , instead of a single value , then the COUNTIF function returns an array of the same length. In the present case , because we are using :

COUNTIF($F$1:$F1,SlNo)

it will return an array with as many elements as the named range

For the first formula , since F1 does not contain a text string from

{0;0;0;0}

When we compare this array with the array returned by the SUMIF function , we will have {0;0;0;0} < {2;1;4;2} , which will return an array :

{TRUE;TRUE;TRUE;TRUE}

The SIGN function converts this to numeric values , as follows :

{1;1;1;1}

The MATCH function then returns the scalar value 1 , since 1 has been found in the very first position.

The INDEX function now puts the text string

When we come to the second cell , the comparison now becomes :

{1;0;0;0} < {2;1;4;2}

which again returns the same array as before. This results in the second cell also having the text string

In the third cell , the comparison becomes :

{2;0;0;0} < {2;1;4;2}

Now , the first element of the array on the left , 2 , is no longer less than the first element of the array on the right , 2.

Thus , the array returned by the above comparison becomes :

{FALSE;TRUE;TRUE;TRUE}

The SIGN function converts this to numeric values , as follows :

{0;1;1;1}

The MATCH function now returns 2 , and the INDEX function puts the text string

In the fourth cell , the comparison becomes :

{2;1;0;0} < {2;1;4;2}

Now , the first and second elements of the array on the left , 2 and 1 , are no longer less than the first and second elements of the array on the right , 2 and 1.

Thus , the array returned by the above comparison becomes :

{FALSE;FALSE;TRUE;TRUE}

The SIGN function converts this to numeric values , as follows :

{0;0;1;1}

The MATCH function now returns 3 , and the INDEX function puts the text string

And so it goes till all the elements have been listed the given number of times.

And so we come to the question , why do we use the SUMIF function ?

Narayan