# IF(MOD) for skipping columns

1. ## IF(MOD) for skipping columns

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))

2. ## Re: IF(MOD) for skipping columns

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

3. ## Re: IF(MOD) for skipping columns

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

4. ## Re: IF(MOD) for skipping columns

``Please Login or Register  to view this content.``

5. ## Re: IF(MOD) for skipping columns

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?

6. ## Re: IF(MOD) for skipping columns

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1