+ Reply to Thread
Results 1 to 18 of 18

Create four arrays from one array

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Create four arrays from one array

    Hello everybody
    I have some values in C3:V3 (20 values). I need to create an array (five arrays) of the main array C3:V3 in this way

    Temp1 equals to values in C3-G3-K3-O3-S3
    Temp2 equals to values in D3-H3-L3-P3-T3
    Temp3 equals to values in E3-I3-M3-Q3-U3
    Temp4 equals to values in F3-J3-N3-R3-V3

    Thanks advanced for help
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,465

    Re: Create four arrays from one array

    I have seen you use arrays in other projects, so I wonder exactly what you are stuck on here. I also wonder if your intention is to store the result in 4 different 1D arrays, or do you want 1 2D array, or do you want an array of arrays.

    I would probably use a 2D array, with a loop with three counters, one counter counts the first dimension of the array, one counter for the second dimension, and a third counter to keep track of where I am at in the row:
    Please Login or Register  to view this content.
    or something like that.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Create four arrays from one array

    As MrShorty said, an array of arrays might be best. More code is needed should the total of elements not work out even.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    Thank you very much MrShorty
    I tested that and it is ok
    Please Login or Register  to view this content.
    But I need to store the results in four different separated arrays

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    @Mr. Kenneth
    Thanks a lot for this awesome code. That's exactly what I was searching for
    Best Regards

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    I need more little help Mr. Kenneth
    I need to merge the new arrays (the output of your code)
    Create new array to hold the new output as following

    1- temp1 to be merged with temp2
    2- temp1 to be merged with temp3
    3- temp1 to be merged with temp4

    4- temp2 to be merged with temp3
    5-temp2 to be merged with temp4

    6-temp3 to be merged with temp4

    As for these six results to be in one array
    If possible I need to duplicate each item from the six .. I mean for example: after merging "temp1 to be merged with temp2" to duplicate this again
    so I would imagine to have 12 items >> each item and the next are the same

    Hope it is clear

  7. #7
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Create four arrays from one array

    While I could show you how to do the first part, you lost me on the last part. Even so, there is probably no reason to reinvent the wheel. You have probably seen the array routines that Chip Pearson lists. You might want to see if those meet your needs. http://www.cpearson.com/excel/vbaarrays.htm

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    I know it is may be confusing so I put my imagination in this picture
    11.png

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    And this is the final output I desired
    22.png
    Last edited by YasserKhalil; 11-24-2016 at 01:50 AM.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    I found this udf that merges two arrays
    Please Login or Register  to view this content.
    But it takes value from one array and value from the other array and so on
    And my need to have all the values of the first array the all the values in the second array

  11. #11
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    I have tried and edited the udf to suit my case
    Please Login or Register  to view this content.
    Can you help me to do the merge for arrays and the whole results to be put in one final array?

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    This is a sample workbook with the final code (It is Kenneth's code)
    We can start from this post
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    After merging the arrays in this way
    Please Login or Register  to view this content.
    How can I populate it to range("N3")?

  14. #14
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    This is my try. Please correct me if I am in wrong way
    Please Login or Register  to view this content.
    But of course I missed to duplicate each array as I illustrated earlier
    How can it be easily done?

  15. #15
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    Is there an easier way to do looping for these lines
    Please Login or Register  to view this content.

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,690

    Lightbulb


    Hi !

    Quote Originally Posted by YasserKhalil View Post
    I have some values in C3:V3 (20 values). I need to create an array (five arrays) of the main array C3:V3 in this way

    Temp1 equals to values in C3-G3-K3-O3-S3
    Use Index worksheet function, needs just one codeline to create a Temp array …

    (Samples on another forum you well know …)


  17. #17
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,805

    Re: Create four arrays from one array

    Do you mean dealing with reply in post#15? My issue now is different
    I need to simplify the code in post # 15 ...

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,690

    Re: Create four arrays from one array


    Just means only a single codeline is necessary to create an array using INDEX … (Excel basics even in VBA !)

+ 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. [SOLVED] Looping thru arrays with dynamic names. Array1/2/3/5, how to array(Array & i) syntax?
    By graym463 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-24-2015, 10:15 AM
  2. Replies: 6
    Last Post: 09-25-2013, 10:08 PM
  3. [SOLVED] New to arrays-how do I build new array while filtering first array?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-03-2013, 05:59 AM
  4. [SOLVED] New to arrays-Create two column array with different column order to sheet?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2013, 01:37 AM
  5. [SOLVED] How would one create a single list of #'s (array) from multiple lists (arrays)?
    By Rstrand in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-08-2013, 07:32 PM
  6. Replies: 4
    Last Post: 01-15-2011, 02:10 PM
  7. An array of Arrays
    By cybercab in forum Excel General
    Replies: 0
    Last Post: 05-04-2005, 09:18 AM

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