+ Reply to Thread
Results 1 to 4 of 4

Method to multiply AND copy array in single column?

  1. #1
    Registered User
    Join Date
    02-08-2018
    Location
    Scotland
    MS-Off Ver
    15.28
    Posts
    3

    Method to multiply AND copy array in single column?

    Hi all, first question on here so will perhaps have overcomplicated this so I shall try explain.

    I have attached a screenshot below. I have 20,760 rows (120 rows for each of the mutual funds I am investigating; 173 funds total). The reason I have 120 rows for each fund is due to having a row for each month of the period between January 2006-December 2015 (120 months). I have data on various characteristics of these funds (age, size, manager tenure, etc). What I want to do is ensure each fund has the related characteristic value on the same row as it over the whole time period. For example, if fund A has a management fee of 1.5, I want the value 1.5 to be copied into all 120 rows for that one fund, and then move onto the second fund and it's own management fee, and so on for all 173 funds.

    As an example, in the screenshot column K has 173 datapoints on the management fees for each of my funds. I want to not only copy this data all the way down to row 20,760, but also repeat each value a set amount of times before moving onto the next. So for example, I would like to repeat cell K2 exactly 120 times (because that is the value for Fund A), and then immediately move onto the next value (K3) and repeat that exactly 120 times, and so on until all values are repeated 120 times and I reach cell 20,760.

    Apologies for the long winded explanation, this is for my dissertation so if anyone could help me out that would be great!

    Screen Shot 2018-02-08 at 10.29.03.png
    Last edited by Conzo1000; 02-09-2018 at 06:01 AM. Reason: Wrong terminology

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,600

    Re: Method to multiply AND copy array in single column?

    Put 120 in an unused cell, copy.
    Select K2..Klastrow Paste Special, Multiply
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-08-2018
    Location
    Scotland
    MS-Off Ver
    15.28
    Posts
    3

    Re: Method to multiply AND copy array in single column?

    Sorry I explained poorly, I want the values to REPEAT 120 times before moving on to the next value, and so on until I reach row 20,760. So for example I want K2 to repeat 120 times, then move onto K3 which will repeat 120 times, and so on until all values have repeated 120 times.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Method to multiply AND copy array in single column?

    Take a look at the attached file. The formula in the yellow cells:

    =INDEX($K$2:$K$11,1+INT((ROWS($1:1)-1)/4))

    copies the values in K2 to K11 4 times before moving on to the next one. If that is what you want, adjust the range (blue) to suit your sheet, and change the 4 to 120.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 03-07-2016, 09:54 PM
  2. [SOLVED] Transpose Array to Single Column
    By jeversf in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-07-2014, 05:36 AM
  3. Replies: 8
    Last Post: 04-11-2014, 10:18 AM
  4. ◄ Copy Column, but multiply by -1 ►
    By xsoldoutx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2012, 02:22 PM
  5. copy a single column from mutiple workbooks into a single workbook
    By Savan87 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2011, 11:27 AM
  6. Single Conditional Array x two Multi-Column Array - Approach needed
    By David Brown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2010, 11:41 AM
  7. Can't copy single array formula
    By tpblazo in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-08-2009, 10:09 AM

Tags for this Thread

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