how far away is the nearest cell that matches this one?

**ottenm** · 02-12-2014, 12:48 PM

I'm generating some math practice problems (addition, subtraction, etc.) and would like to minimize having the same problem show up next to itself, or even a few cells away. Any suggestions on using Excel to calculate the distance (in cells) from this cell, to the nearest cell (up or down) with the same formula in it? Something like this:
1 + 2 (5)
3 + 4 (3)
5 + 6 (1)
5 + 6 (1)
3 + 4 (3)
1 + 2 (5)

I actually have a column with the problems in it, and an adjacent column with the problems reversed (1+2, 2+1) so ideally I'd like to search up or down in my column, and in the adjacent column, but I can make it work with just up/down in my own column.

Thanks for any help-

**XOR LX** · 02-12-2014, 01:00 PM

Hi,

Assuming your desired matches are exact matches (i.e. no extra spacing, etc.), and that the data as you give it is in the range A1:A6, this array formula** in B1:

=MIN(IF($A$1:$A$6=A1,IF(ROW($A$1:$A$6)<>ROWS($1:1),ABS(ROW($A$1:$A$6)-ROWS($1:1)))))

Copy down as required.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**ottenm** · 02-12-2014, 01:34 PM

Insanely tight! Hats off to you XOR LX, hope it's a great day for you there in Yorkshire, England.

Thanks a million!! Tweaked it so scan column C and D instead of just A1:A6 (entered as an array formula just as you described, by hitting Ctrl-Shift-Enter there after the final closing paren):

=MIN(IF(C:D=C1,IF(ROW(C:D)<>ROWS($1:1),ABS(ROW(C:D)-ROWS($1:1)))))

**ottenm** · 02-12-2014, 01:37 PM

Ignore the attached image, it's a screen shot of it not working before I figured out how to enter an array formula. In the excelforum.com "Attachment Editor", I can't figure out how to delete the attachment.

Peace!

**XOR LX** · 02-12-2014, 01:46 PM

You're welcome, my friend. And I hope you're having a good morning in sunny (?) Austin, Texas too!

By the way, one last piece of advice: I'd strongly recommend that you don't use entire column references (e.g. C:D) in array formulas. Try to put some limits on them, e.g. C1:D1000. You can go as large as you think you'll need to, but if you reference an entire column then that's potentially a million cells that will have to be calculated.

Unlike with 'standard' formulas, in calculating CSE-entered versions, Excel doesn't determine which are the only used cells in your range, and so will calculate the formula over the entire range you specify, potentially reducing your spreadsheet to a crawl.

Cheers

**MarvinP** · 02-12-2014, 01:54 PM

Hi,

How about a Random, non Repeating drill problem worksheet. See the attached where you can specify the maximum number and operation for the drill sheet. See the attached. Change the Max Number or the Operation. They should never repeat problems (in rows adjacent).