Insert new rows based on cell value, copy variable cell range (pairs of columns) and transpose data below

  • Hello OzGrid!

    This is my first post on this forum, although i have browsed on here and been directed here before for answers!Example file for OZ Grid.xlsx I have searched as best i could for a solution for what i require, but I can only find a solution in parts, and not how to work them as a whole.

    I will explain here what i would like and also i have attached a sample file - the top section of data is how it comes, and the bottom section of data is how i would like it to manifest after macro:

    Column M denotes how many people have applied - This will also denote how many rows i need for the data (i.e. if 3 people applied i will need 2 rows inserted below)

    Columns N-AQ are the applicants details - in paired columns (i.e. N is candidate 1's number, O is candidates 1's name, P+Q are candidate 2's number and name, R+S are candidate 3's number and name etc). In total there are cells for up to 15 candidates

    What I would like is, for example, if the first row with data says 3 applicants, it inserts 3 new rows, copy Columns A-M and AR-BB and paste that info on the rows below (2 rows below), and then transpose the candidates numbers and names under columns N and O (i.e. underneath where candidate 1's details are)

    I suppose it would also be useful to then delete all the unused columns (which should be P-AQ once all candidate data is transposed?) however I don't necessarily need a macro for that as i could do it manually.

    Hopefully the attached excel file can explain / show what i mean better than the above. I hope you can help!

    Thanks in advance


    Example file for OZ Grid.xlsx

Participate now!

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