Moving every 4th cell over to columns

  • I have a spreadsheet that lookups something like this:



    DBM Power AMP
    45.6
    32.2
    3


    47.7
    48.0
    2.5


    49
    35
    15


    What I want to do is move the first row in each set over to the Power Column (45.6,47.7 and 49).


    I also want to move every second row to the AMP column (32.2, 48, 35).


    I know I need a for Each loop but don't know how to create it.


    I have 250 sets of these.


    thanks for your help

  • instead of a macro why not use formula.
    Have a look at the attached sheet. SHeet 1 has the formula.


    Steps
    Copy the whole sheet across (once all the formual are there.
    Copy + Paste special Values for the whole sheet
    Then sort the whole area by Power or AMP
    Delete the rows below the data you want to keep

    There are three types of people in this world.
    Those who can count and those who can't.

  • Assuming you want a somewhat automated solution due to the length of your list, the attached should do it for you.


    The "Rearranged" sheet shows how to add a few columns that allow formulas that can be dragged down to cover all of your data. The trick is to create the Entry1 - Entry"n" list in the column adjacent to and preceding your data (dragging the Entry1 cell down should auto fill for you). Then select the cells in columns B and C through the end of your data and INSERT > NAME > CREATE. This will will enable you to get the intersection of Data and Entry"n". Using the INDIRECT and CONCATENATE functions then allow you to write formulas (I've assumed that your data sets repeat every four lines) in columns D-F that can be dragged down until all of your data sets are converted.


    You may want to do a copy and past special (to another sheet?) on columns D-F at some point.

  • I like your solution thomach


    You can also do the same sort of thing by taking the data (named range) into a vba array ...manipulate with code into another array then dump it out to a new named range.


    look carefully at the named ranges (some are dynamic ...and "data" had to be extended...

    Robert Hind
    Perth - Western Australia

  • A more "friendly" version of my earlier posting.


    Comments now shown in spreadsheet and vba coding ....should be a lot easier to follow concepts.

    Robert Hind
    Perth - Western Australia

  • Please note my vba solution was written using Excel 2002 (office XP) and I understand some of the code won't work with Excel 97.

    Robert Hind
    Perth - Western Australia

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!