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

To repeat row values, a specified number of times

Hi,

I have a query in repeating the cell values based on the number specified in a cell. (Using Excel functions only)

I have also attached a sample file for the same.

Please help me out.
 

Attachments

  • Sample file_31.10.2013.xlsx
    8.9 KB · Views: 160
Hi Ramanan,
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 Ctrl + Shift + Enter


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 Ctrl + Shift + Enter


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.
 
Hi again,
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.
 
Ramanan Arumugam


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:)
 
@ Sajan,
Hey buddy.. Its actually your's view, from my point of view.. still thanks for the like.. :)
 
Hi Debraj,
It is always good to look at a problem from different perspectives...!

By the way, you should also consider the N() function.

Cheers,
Sajan.
 
Hi guys,

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?
 
Hi ,

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.

SlNo is a named range , which refers to the text strings that will be repeated ; in the example workbook , these strings are Right , Left , North , East , in the cells A2:A5.

RepeatAmt is a second named range , which refers to the numeric values which will used to define the number of times the above text strings will be repeated ; in the example workbook these values are 2 , 1 , 4 , 2 in the cells B2:B5.

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

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 , 1)
=INDEX(SlNo , 1)
=INDEX(SlNo , 2)
=INDEX(SlNo , 3)
=INDEX(SlNo , 3)
=INDEX(SlNo , 3)
=INDEX(SlNo , 3)
=INDEX(SlNo , 4)
=INDEX(SlNo , 4)

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 SIGN , COUNTIF and SUMIF functions.

The SIGN function takes in a numeric value , and returns one of the following 3 values :

-1 if the numeric value is negative

0 if the number is 0

1 if the number is positive

The SUMIF function generates an array of values corresponding to the repeat amounts for each text string.

You may ask why we use a SUMIF function to generate these values , when we already have the named range RepeatAmt which stores these values ; this will be clear when we consider a different situation later on.

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 COUNTIF function normally returns a single scalar value , which is a count of how many times a particular value occurs in a range ; the range is the first parameter , and the value whose occurrence in the range is to be counted is the second parameter.

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 SlNo ; for the example data in the workbook , it will return an array of 4 elements.

For the first formula , since F1 does not contain a text string from SlNo , the return array will be :

{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 Right in the first cell where the formula has been entered.

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

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 Left in the third cell.

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 North in the fourth cell.

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
 
Back
Top