IF(MOD) for skipping columns

**rhudgins** · 07-29-2010, 09:39 PM

I have the following formula that skips 4 columns when I drag it across a row to paste data. Can someone help me revise it to skip on two columns?

=IF(MOD((COLUMN()-1)/4,1),"",INDEX($Z:$Z,INT((COLUMN()-1)/4)+41))

**DonkeyOte** · 07-30-2010, 12:44 AM

Change /4 to /2

FWIW I prefer to use COLUMNS($A1:A1) * to COLUMN()-1 as it is more robust.

The use of COLUMN()-1 in this instance assumes first formula result is always in an odd numbered column which may not prove to be the case over a long period of time and would then generate incorrect results.

*where A1 is the first cell containing the formula

**rhudgins** · 07-30-2010, 03:12 PM

I tried making the changes but it is not working. Here is my spreadsheet....The range is slightly different then before.

**DonkeyOte** · 07-30-2010, 03:18 PM

Based on your sample and adapting existing formula

Please Login or Register  to view this content.

**rhudgins** · 07-30-2010, 03:24 PM

This is perfect thanks!

In row 5 is there a formula that can remove the column spaces inbetween each ticker in row 4 and leave all of the tickers in one continuos string?

**DonkeyOte** · 07-31-2010, 02:16 AM

Depends on the volume of "tickers" ... native formulae aren't great when it comes to doing mass concatenation in a single cell calc whereas doing similar via VBA (UDF) is trivial.

If you're happy to use "helper cells" then you can use pretty basic formula to do this - ie use helpers to build an ever extending string and in your result cell simply return the last cell in the helper range.