+ Reply to Thread
Results 1 to 6 of 6

Array Help

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    rockville,maryland
    MS-Off Ver
    Excel 2007
    Posts
    4

    Array Help

    I have 2 ranges:

    6 10.00
    12 9.00
    12 8.00
    12 7.00
    319 6.00

    I'd like to create an array based on these 2 ranges so the result is
    10
    10
    10
    10
    10
    10
    9
    9
    ...etc

    essentially 10 would be the first 6 items, followed by 9 for the next 12, so on and so forth

    Any help?

    Thanks

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Array Help

    Where is the data located?

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If posting code please use code tags, see here.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Array Help

    Here is a solution (courtesy of Daddy Longlegs)
    =IF(SUM(A:A)<ROWS($A$1:$A1),"",LOOKUP(ROWS(A$1:A1)-1,SUBTOTAL(9,OFFSET(A$1,,,ROW(A$2:A$6)-ROW(A$2)+1)),B$2:B$6)&"")
    See attached
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Array Help

    http://www.excelforum.com/excel-prog...-an-array.html

    @ wonger1381

    As your dublicate thread in which you asked me something is blocked and Admin(even that i asked for this) don't open it again, i have an answer for you.

    .
    ..Thanks for your help. Are these the kind of responses that earned you your title of "valued forum contributor"
    I didn't even mean to post it twice. It looked like it got cut off the first time.
    Yes. As my excel skills are limited, i have 5775 posts here doing this.I send warnings for duplicate and cross post threads. So the forum gave me the title of "Valued Forum Contributor " Isn't nice?

    By the way. Did the solution that offered to you in post#3, works for you?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    08-02-2012
    Location
    rockville,maryland
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Array Help

    The solution in 3 worked as a clever solution but I was hoping to do it in VBA which after playing around with I was able to figure out.
    In case anyone is interested: month_vector and cpr_vector are ranges of cells.

    Dim cf(1000, 4)
    Dim vcpr As Range
    Dim cpr(1000, 0)
    Dim mth As Range
    Dim month(1000, 0)
    Dim finalcpr(1000, 0)

    'first creates an array of months
    m = 0
    For Each mth In month_vector
    month(m, 0) = mth
    m = m + 1
    Next mth

    'creates an array of CPRs
    c = 0
    For Each vcpr In CPR_Vector
    cpr(c, 0) = vcpr
    c = c + 1
    Next vcpr

    arraycount = Application.WorksheetFunction.Count(cpr)
    '
    vcounter = 1
    m = 0
    f = 0
    g = 0
    finalcpr(0, 0) = cpr(0, 0)
    Do Until vcounter = arraycount + 1

    ''Uses the 2 arrays to merge into single array
    Do Until f = month(m, 0) + g
    finalcpr(f, 0) = cpr(m, 0)
    f = f + 1
    Loop
    m = m + 1
    vcounter = vcounter + 1
    g = f
    Loop

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Array Help

    If you want code try this, which assumes the data starts at A1.
    Please Login or Register  to view this content.

+ 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