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 <= 99,'testsheet'!$A$1,"") &
IF('testsheet'!$C$2 <= 99, 'testsheet'!$A$2,"") &
IF('testsheet'!$C$3 <= 99, 'testsheet'!$A$3,"") &
... 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.

Answer 1

=LET(
    currentLevel, C2,
    IF(currentLevel <> -1, "",
        LET(
            inputList, TEXTSPLIT(A2, , CHAR(10)),  // Split input by newlines
            lvl2Data, MAP(inputList, LAMBDA(val, XLOOKUP(val, FILTER(B:B, C:C = -2), D:D & "|" & A:A, ""))),
            lvl2Split, TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, lvl2Data), , "|", , , ""),  // Join with newlines
            lvl2Transforms, INDEX(lvl2Split,,1),
            lvl2Inputs, INDEX(lvl2Split,,2),
            
            lvl3Data, MAP(TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, lvl2Inputs), , CHAR(10)), LAMBDA(val, XLOOKUP(val, FILTER(B:B, C:C = -3), D:D & "|" & A:A, ""))),
            lvl3Split, TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, lvl3Data), , "|", , , ""),
            lvl3Transforms, INDEX(lvl3Split,,1),
            lvl3Inputs, INDEX(lvl3Split,,2),
            
            lvl4Data, MAP(TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, lvl3Inputs), , CHAR(10)), LAMBDA(val, XLOOKUP(val, FILTER(B:B, C:C = -4), D:D & "|" & A:A, ""))),
            lvl4Split, TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, lvl4Data), , "|", , , ""),
            lvl4Transforms, INDEX(lvl4Split,,1),
            lvl4Inputs, INDEX(lvl4Split,,2),
            
            allTransforms, TEXTJOIN(CHAR(10), TRUE, lvl4Transforms, lvl3Transforms, lvl2Transforms, D2),
            allInputs, TEXTJOIN(CHAR(10), TRUE, lvl4Inputs, lvl3Inputs, lvl2Inputs, A2),
            B2 & CHAR(10) & allTransforms & CHAR(10) & allInputs
        )
    )
)

Answer 2

This was a difficult question to answer when it was first asked in the year 2011. However, Excel has grown up since then.

The easy solution to the original problem is to use the following formula:

=CONCAT(IF(C1:C40>=99,A1:A40,""))

However, the OP also asked if we can make a loop within a spreadsheet formula. With Excel 2023, the answer is yes. The REDUCE function will loop through arrays, but it does have some limitations; REDUCE can only pass the value of one variable from one iteration to the next. The following formula will count the number of rows in A1:A40; it's 40 rows. Then it will loop from 1 to 40 over the arrays C1:C40 and A1:A40.

=REDUCE("",
  SEQUENCE(ROWS(A1:A40)),
  LAMBDA(a,idx,
    a&IF(INDEX(C1:C40,idx)>=99,INDEX(A1:A40,idx),"")
  )
)

Neither of these solutions was available back in 2011. Spreadsheet formula loops are also possible with recursive LAMBDA functions.

Answer 3

Check this easy formula out for making slugs. It might help you out. It takes the value in a cell and makes a first-word-second-word slug.

=LOWER(TEXTJOIN("-",TRUE,(TEXTSPLIT(B2,{" ","&","/"},,TRUE,1))))

This formula will use the value in B2 and remove any spaces the & and / characters and replace them with the - character.

Answer 4

I came across this while looking for a way to build a schedule to forecast projects. I wanted to be able to set project durations and have the planned amount of hours populate from the week start to the end. Essentially I wanted to do a for loop. Since I did not want to use VBA, I needed to build an external table that listed each week number and have a corresponding number of columns with 1s following the week number. I then built the attached sheet and used the following formula:

=IFERROR(IF($G14<=L$4, IF($H14<53,VLOOKUP($H14,Table2[#All],(COUNTIF($K14:K14, ">0")+2),), 0)*$E14, 0), 0)

IFERROR removes any errors from blank cells and gives a nicer look.

1st IF checks to see if project start date is equal to or greater than the week date in calendar row.

2nd IF checks to see if the project duration is less than a year, then finds the correct line in Table 2, then the column index is based on COUNTIF, which counts the number of non zeros from the beginning of the project row, and then adds 2 (if there are no non zeros, then the column indexed will be column 2, which is the first column with 1s from Table2).

When both IF statements evaluate to TRUE, then 1 is the result, which is multiplied by the Time Estimate per Week.

If either IF statement is FALSE then 0 is the result.

I also formatted the numbers in the area where I use this formula to Custom 0;-0;;@ to remove any plain zeros from view.

Main Sheet Table 2

Answer 5

@Nat gave a good answer. But since there is no way to shorten a code, why not use concatenate 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 <= 99,'testsheet'!$A$1,"") &IF('testsheet'!$C$2 <= 99,'testsheet'!$A$2,"") &IF('testsheet'!$C$3 <= 99,'testsheet'!$A$3,"") &IF('testsheet'!$C$4 <= 99,'testsheet'!$A$4,"") &IF('testsheet'!$C$5 <= 99,'testsheet'!$A$5,"") &IF('testsheet'!$C$6 <= 99,'testsheet'!$A$6,"") &IF('testsheet'!$C$7 <= 99,'testsheet'!$A$7,"") &IF('testsheet'!$C$8 <= 99,'testsheet'!$A$8,"") &IF('testsheet'!$C$9 <= 99,'testsheet'!$A$9,"") &IF('testsheet'!$C$10 <= 99,'testsheet'!$A$10,"") &IF('testsheet'!$C$11 <= 99,'testsheet'!$A$11,"") &IF('testsheet'!$C$12 <= 99,'testsheet'!$A$12,"") &IF('testsheet'!$C$13 <= 99,'testsheet'!$A$13,"") &IF('testsheet'!$C$14 <= 99,'testsheet'!$A$14,"") &IF('testsheet'!$C$15 <= 99,'testsheet'!$A$15,"") &IF('testsheet'!$C$16 <= 99,'testsheet'!$A$16,"") &IF('testsheet'!$C$17 <= 99,'testsheet'!$A$17,"") &IF('testsheet'!$C$18 <= 99,'testsheet'!$A$18,"") &IF('testsheet'!$C$19 <= 99,'testsheet'!$A$19,"") &IF('testsheet'!$C$20 <= 99,'testsheet'!$A$20,"") &IF('testsheet'!$C$21 <= 99,'testsheet'!$A$21,"") &IF('testsheet'!$C$22 <= 99,'testsheet'!$A$22,"") &IF('testsheet'!$C$23 <= 99,'testsheet'!$A$23,"") &IF('testsheet'!$C$24 <= 99,'testsheet'!$A$24,"") &IF('testsheet'!$C$25 <= 99,'testsheet'!$A$25,"") &IF('testsheet'!$C$26 <= 99,'testsheet'!$A$26,"") &IF('testsheet'!$C$27 <= 99,'testsheet'!$A$27,"") &IF('testsheet'!$C$28 <= 99,'testsheet'!$A$28,"") &IF('testsheet'!$C$29 <= 99,'testsheet'!$A$29,"") &IF('testsheet'!$C$30 <= 99,'testsheet'!$A$30,"") &IF('testsheet'!$C$31 <= 99,'testsheet'!$A$31,"") &IF('testsheet'!$C$32 <= 99,'testsheet'!$A$32,"") &IF('testsheet'!$C$33 <= 99,'testsheet'!$A$33,"") &IF('testsheet'!$C$34 <= 99,'testsheet'!$A$34,"") &IF('testsheet'!$C$35 <= 99,'testsheet'!$A$35,"") &IF('testsheet'!$C$36 <= 99,'testsheet'!$A$36,"") &IF('testsheet'!$C$37 <= 99,'testsheet'!$A$37,"") &IF('testsheet'!$C$38 <= 99,'testsheet'!$A$38,"") &IF('testsheet'!$C$39 <= 99,'testsheet'!$A$39,"") &IF('testsheet'!$C$40 <= 99,'testsheet'!$A$40,"") 

I didn't type it, I just use "&" 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 > <= 99,'testsheet'!$A$

part4 > 1 to 40

part5 > ,"") &

• 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&B2&C2&D2&E2 " in G2
• Enter " =I1&G2 " in I2

Now select A2:I2 , and drag it down. Notice that the number did the increment 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 & and the back.

Result = code as you intended.

I've used Excel/OpenOfficeCalc to help me generate code for my projects. Works for me, hope it helps for others. (:

Answer 6

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!

Answer 7

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

1. Enter "1" in A1
2. 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:

3. "100" in B1, drag through to B5 and
4. "=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:

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

Answer 8

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 <= 99,'testsheet'!A1,"") 

Copy that cell down to row 40 In cell B1 put the formula

=A1

In cell B2 put the formula

=B1 & 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).

Answer 9

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.

Answer 10

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.