How to loop in excel without VBA or macros

Question

Is it possible to iterate  loop  a group of rows in Excel without VBA or macros installed  A web search didn t yield anything helpful    IF  testsheet   C 1  lt   99  testsheet   A 1      amp  IF  testsheet   C 2  lt   99   testsheet   A 2      amp  IF  testsheet   C 3  lt   99   testsheet   A 3      amp      and so on through   C 40    A 40       As it is  I ll have to repeat the above code 40 times in each cell and I have over 200 cells which need the code   I m pretty good with PHP SQL  but just learning Excel

User · Answer

You could create a table somewhere on a calculation spreadsheet which performs this operation for each pair of cells, and use auto-fill to fill it up.

Aggregate the results from that table into a results cell.

The 200 so cells which reference the results could then reference the cell that holds the aggregation results. In the newest versions of excel you can name the result cell and reference it that way, for ease of reading.

User · Answer

Going to answer this myself  correct me if I m wrong    It is not possible to iterate over a group of rows  like an array  in Excel without VBA installed   macros enabled

User · Answer

Add more columns when you have variable loops that repeat at different rates  I m not sure explicitly what you re trying to do  but I think I ve done something that could apply   Creating a single loop in Excel is prettty simple  It actually does the work for you  Try this on a new workbook    Enter  1  in A1 Enter   A1 1  in A2   A3 will automatically be   A2 1  as you drag down  The first steps don t have to be that explicit  Excel will automatically recognize the pattern and count if you just put  2  in A2  but if we want B1-B5 to be  100  and B5-B10 to be  200   counting up the same way  you can see why knowing how to do it explicitly matters  In this scenario  You just enter     100  in B1  drag through to B5 and   B1 100  in B6   B7 will automatically be   B2 100  etc  as you drag down  so basically it increases every 5 rows infinitely  To make a loop of numbers 1-5 in column A    Enter   A1  in cell A6  As you drag down  it will automatically be   A2  in cell A7  etc  because of the way that Excel does things    So  now we have column A repeating numbers 1-5 while column B is increasing by 100 every 5 cells You could make column B repeat  for instance  the numbers 100-900 in using the same method as you did with column A as a way to produce  for instance  each possible combination with multiple variables  Drag down the columns and they ll do it infinitely  I m not explicitly addressing the scenario given  but if you follow the steps and understand them  the concept should give you an answer to the problem that involves adding more columns and concactinating or using them as your variables

User · Answer

The way to get the results of your formula would be to start in a new sheet    In cell A1 put the formula    IF  testsheet  C1  lt   99  testsheet  A1        Copy that cell down to row 40 In cell B1 put the formula    A1   In cell B2 put the formula    B1  amp  A2   Copy that cell down to row 40   The value you want is now in that column in row 40   Not really the answer you want  but that is the fastest way to get things done excel wise without creating a custom formula that takes in a range and makes the calculation  which would be more fun to do

User · Answer

I was just searching for something similar   I want to sum every odd row column    SUMIF has TWO possible ranges  the range to sum from  and a range to consider criteria in   SUMIF B1 B1000 1 A1 A1000    This function will consider if a cell in the B range is   1   it will sum the corresponding A cell only if it is    To get   1  to return in the B range I put this in B    MOD ROWNUM B1  2    Then auto fill down to get the modulus to fill  you could put and calculatable criteria here to get the SUMIF or SUMIFS conditions you need to loop through each cell    Easier than ARRAY stuff and hides the back-end of loops

User · Answer

Nat gave a good answer  But since there is no way to shorten a code  why not use contatenate to  generate  the code you need  It works for me when I m lazy  at typing the whole code in the cell    So what we need is just identify the pattern   use excel to built the pattern  structure    add       and paste it in the intended cell    For example  you want to achieve  i mean  enter in the cell      IF  testsheet   C 1  lt   99  testsheet   A 1      amp IF  testsheet   C 2  lt   99  testsheet   A 2      amp IF  testsheet   C 3  lt   99  testsheet   A 3      amp IF  testsheet   C 4  lt   99  testsheet   A 4      amp IF  testsheet   C 5  lt   99  testsheet   A 5      amp IF  testsheet   C 6  lt   99  testsheet   A 6      amp IF  testsheet   C 7  lt   99  testsheet   A 7      amp IF  testsheet   C 8  lt   99  testsheet   A 8      amp IF  testsheet   C 9  lt   99  testsheet   A 9      amp IF  testsheet   C 10  lt   99  testsheet   A 10      amp IF  testsheet   C 11  lt   99  testsheet   A 11      amp IF  testsheet   C 12  lt   99  testsheet   A 12      amp IF  testsheet   C 13  lt   99  testsheet   A 13      amp IF  testsheet   C 14  lt   99  testsheet   A 14      amp IF  testsheet   C 15  lt   99  testsheet   A 15      amp IF  testsheet   C 16  lt   99  testsheet   A 16      amp IF  testsheet   C 17  lt   99  testsheet   A 17      amp IF  testsheet   C 18  lt   99  testsheet   A 18      amp IF  testsheet   C 19  lt   99  testsheet   A 19      amp IF  testsheet   C 20  lt   99  testsheet   A 20      amp IF  testsheet   C 21  lt   99  testsheet   A 21      amp IF  testsheet   C 22  lt   99  testsheet   A 22      amp IF  testsheet   C 23  lt   99  testsheet   A 23      amp IF  testsheet   C 24  lt   99  testsheet   A 24      amp IF  testsheet   C 25  lt   99  testsheet   A 25      amp IF  testsheet   C 26  lt   99  testsheet   A 26      amp IF  testsheet   C 27  lt   99  testsheet   A 27      amp IF  testsheet   C 28  lt   99  testsheet   A 28      amp IF  testsheet   C 29  lt   99  testsheet   A 29      amp IF  testsheet   C 30  lt   99  testsheet   A 30      amp IF  testsheet   C 31  lt   99  testsheet   A 31      amp IF  testsheet   C 32  lt   99  testsheet   A 32      amp IF  testsheet   C 33  lt   99  testsheet   A 33      amp IF  testsheet   C 34  lt   99  testsheet   A 34      amp IF  testsheet   C 35  lt   99  testsheet   A 35      amp IF  testsheet   C 36  lt   99  testsheet   A 36      amp IF  testsheet   C 37  lt   99  testsheet   A 37      amp IF  testsheet   C 38  lt   99  testsheet   A 38      amp IF  testsheet   C 39  lt   99  testsheet   A 39      amp IF  testsheet   C 40  lt   99  testsheet   A 40        I didn t type it  I just use   amp   symbol to combine arranged cell in excel  another file  not the file we are working on    Notice that     part1   IF  testsheet   C   part2   1 to 40  part3    lt   99  testsheet   A   part4   1 to 40  part5         amp    Enter part1 to A1  part3 to C1  part to E1  Enter     A1   in A2      C1   in C2      E1   in E2  Enter     B1 1   in B2      D1 1   in D2  Enter    A2 amp B2 amp C2 amp D2 amp E2   in G2 Enter    I1 amp G2   in I2   Now select A2 I2   and drag it down  Notice that the number did the increament per row added  and the generated text is combined  cell by cell and line by line    Copy I41 content  paste it somewhere  add       in front  remove the extra  amp  and the back    Result   code as you intended    I ve use excel OpenOfficeCalc to help me generate code for my projects  Works for me  hope it helps for others

[excel] How to loop in excel without VBA or macros?

Examples related to excel

Examples related to excel-formula