Can I use autofill horizontally and than vertically?

[projectmoneytree]

Basically I have data in columns I and J.
When i drag the following formula (in column K) down, it checks if any two consecutive values in column J are equal to the top two (J3 and J4), and if so, it returns an answer.

IF(AND($J$3=$J5,$J$4=$J6,),($I$3-$I5)+($I$4-$I6),0)

My problem is that I also need to compare any two consecutive values to J4 and J5, J6 and J7.... all the way to J10000 and J10001. Each comparison in a new column.

If i remove the $ and drag the formula horizontally first, than i will still have to add the $ manually to each formula after so that i can autofill it downwards. Any thoughts how this can be accomplished without the manual labour

Thanks to anybody with an idea, I really appreciate it!!!

 

[Teeroy]

Hi and welcome to the forum.

Why do you need the comparisons to run across columns? It would be easier to compare them in a "helper" column immediately adjacent the values to be compared.

 

[projectmoneytree]

Hi and thanks for the quick response!

I'm not sure if I understand or perhaps I didnt explain well enough. The current formula I have dragged down in column K essentially tells me if the value to the left (in column J, as well as the value directly below it, perfectly match the two values at the top of column J. This fills up all of column K. So if my next step is to do the exact same thing only using the second and third values of column J as the constant values (instead of the top 2), would this not take up another entire column, and so on?

Hi and welcome to the forum.

Why do you need the comparisons to run across columns? It would be easier to compare them in a "helper" column immediately adjacent the values to be compared.

 

[Teeroy]

That clears things up. Try replacing your Formula in K with:

=IF(AND(INDIRECT("$J$" & COLUMN()-8)=$J5,INDIRECT("$J$" & COLUMN()-7)=$J6),(INDIRECT("$J$" & COLUMN()-8)-$I5)+(INDIRECT("$J$" & COLUMN()-7)-$I6),0)

and copy / drag down then across. Don't forget for each column across you should start one row further down, otherwise you'll end up matching the row with itself.

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you give us, say, 20 rows of sample data (with a few matches) and the expected results so we can be sure of both the requirement and the layout?

My signature block below has some suggestions for showing small screen shots.

If you have 10,000 rows and you want to drag this across as well as down, I would be wary of using the volatile function INDIRECT as it may cause your sheet to be very slow to respond.

 

[Teeroy]

Peter,

I agree that using a Volatile function is less than ideal but I couldn't think of another formulaic method. The only alternative that came to mind to achieve the requested output was writing the Formula directly using VBA.

 

[Peter_SSs]

I agree that using a Volatile function is less than ideal but I couldn't think of another formulaic method.

That's why I have asked for sample data and layout. No guarantee, but it may trigger an alternative formula approach.