+ Reply to Thread
Results 1 to 6 of 6

IF(MOD) for skipping columns

  1. #1
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    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. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #3
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    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.
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: IF(MOD) for skipping columns

    Based on your sample and adapting existing formula

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    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. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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