+ Reply to Thread
Results 1 to 19 of 19

Combine two arrays into a third.

  1. #1
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Combine two arrays into a third.

    Hi

    I have one array that lists a set of curency pairs in the form

    AUDCAD
    CADUSD
    GBPUSD

    A second array that lists some incremented times in the form

    10:00
    10:05
    10:10

    I would like to combine the first rows in each array into one item, the second rows into one item etc. so i can then search the new array for the pair and return the time.

    If it could be a dictionary (which i have only just come across) it would allow easy selection I think.

    The two existing arrays are shown below

    Is there an easy way?

    Thanks

    Neil

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Combine two arrays into a third.

    Sorry but is that not a slow Solution?

    Anything with loops must be slow.

    Why not

    1. Save Array One into Column One of a Worksheet

    2. Save Array Two into Column Two of a Worksheet

    3. Read Columns One to Two into a New Array.

    Three lines of code and no loop
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    I do not have a lot of values so it is in reality a blink of the eye.

    I do see what you mean

    However if i can keep it all in the VBA it would be better for me currently.

    If you could outline a way for me to work on that would be good.

    Thanks

    Neil

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Combine two arrays into a third.

    Something like this

    You will need to test it
    depending on your setup your first column and or row may be 0 or 1


    Please Login or Register  to view this content.


    This Worked for me

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-08-2018 at 08:09 PM.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combine two arrays into a third.

    Hello webwyzard,

    This macro uses a Dictionary to store the file names and times. The path, start time, and minutes to add are hard coded into the macro. However, this can be changed if needed.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  6. #6
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    Hi

    I set a Public ListPairsTimesArray()

    The two receding arrays are filled. Note that both arrays feeding the new array are filled.

    Then on using the code below i get subscript out of range?

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    Thanks for this i will see if i can get it to work!!

    Update i got it to work after add ing a variant called ffile and replacing file in the code

    One question which i have been trying to solve for a while.

    This produces output as follows

    How would i only increment for each pair and not each template 0.01 to 0.0x?

    AUDCAD-0.01 10:00
    AUDCAD-0.02 10:05
    AUDCAD-0.03 10:10
    AUDCAD-0.04 10:15
    AUDJPY-0.01 10:20
    AUDJPY-0.02 10:25
    AUDJPY-0.03 10:30
    AUDJPY-0.04 10:35
    AUDUSD-0.01 10:40
    AUDUSD-0.02 10:45
    AUDUSD-0.03 10:50
    AUDUSD-0.04 10:55
    EURJPY-0.01 11:00
    EURJPY-0.02 11:05
    EURJPY-0.03 11:10
    EURJPY-0.04 11:15
    GBPUSD-0.01 11:20
    GBPUSD-0.02 11:25
    GBPUSD-0.03 11:30
    GBPUSD-0.04 11:35
    NZDJPY-0.01 11:40
    NZDJPY-0.02 11:45
    NZDJPY-0.03 11:50
    NZDJPY-0.04 11:55
    NZDUSD-0.01 12:00
    NZDUSD-0.02 12:05
    NZDUSD-0.03 12:10
    NZDUSD-0.04 12:15
    USDCHF-0.01 12:20
    USDCHF-0.02 12:25
    USDCHF-0.03 12:30
    USDCHF-0.04 12:35

    Thanks

    Neil
    Last edited by webwyzard; 11-10-2018 at 07:03 PM.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combine two arrays into a third.

    Hello webwyzard,

    If you are looking for a result like this ...

    AUDCAD-0.01 10:00
    AUDCAD-0.02 10:00
    AUDCAD-0.03 10:00
    AUDCAD-0.04 10:00
    AUDJPY-0.01 10:05
    AUDJPY-0.02 10:05
    AUDJPY-0.03 10:05
    AUDJPY-0.04 10:05
    etc.

    This should work...

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    That is exactly what i need.

    Absolutely fantastic!!!

    I will give it a go tonight

    Many thanks.

    Neil

  10. #10
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    Quick question.

    How do i make the dictionary with all the values just created available outside the routine? Can i declare a public variant to hold them somehow?

    I read you can use Public Dict As Object?

    Thanks

    Neil
    Last edited by webwyzard; 11-13-2018 at 03:17 PM.

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combine two arrays into a third.

    Hello Neil,

    This version has the Dictionary as a Public variable in the code module. You can access it from any other portion of your code.

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    I have the code you posted working but a weird thing is happening later in the code and i can not figure it out.

    The text file has a total of 400 odd rows and the line to be replaced/updated is on about line 134 (GMT_StartTime). However when my script to read in the file and split into rows to search is used it seems to leave out some ros and gives a false position of row 70.

    Code to do the spliting and replacing is below.

    Attached is the results from the locals window showing it finding the match at line 70 but many lines are missing.

    If you view the attached template file it has all the rows.

    I thought it would be something simple but it skips rows and i can not see why.

    Regards

    Neil

    Please Login or Register  to view this content.
    Attached Images Attached Images

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combine two arrays into a third.

    Hello Neil,

    Can you post a copy of the workbook so I can better troubleshoot the problem? Please redact any personal or private information before you post the workbook.

  14. #14
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    OK i will try tomorrow thanks.

  15. #15
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    I have uploaded workbook with the vba.

    It starts with ModifyParamValue() where the user inputs and selects from message boxes currently.

    I have commented in the code briefly what does what but if you need more explanation holler!

    Try not to look at the code too long I don't wish for a grown man to cry!

    The ultimate goal is to be able to modify times (which i am looking at here) in a sequential manner using your code but also to be able to modify text values and numbers.

    Note I did have the code that now seems not to work, "working" so there must be a gotcha somewhere that i cannot see.

    Hopefully, you can

    Thanks for your help.
    Attached Files Attached Files

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combine two arrays into a third.

    Hello Neil,

    Thanks for posting the workbook. I confess after looking at this for sometime I am sure what a typical file looks like and what it is you want to change in the data. If you could provide a file that I can download on my computer along with before and after examples of the results, it would be a huge help.

  17. #17
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    Hi

    I have uploaded an excel file. In the first worksheet column (a) I have a complete template file.

    Then I have shown the types of values we need to modify and write back to the Templates.

    There is a Notes sheet with a bit more explanation.

    If you need a complete set of templates to look at let me know.

    I struggle with this as my background (if you can call it that) is in MS Access and Excel is a foreign land currently.

    Thanks again for your help.

    Regards

    Neil
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    After hours of work using different methods i fanally figured out what was going wrong here.

    The set file rows are not unique.

    Hidden in them are rows with object (wrapped in <> as the name)

    This is why the replacement value we specified got stuck in the wrong part of the file and it skipped rows.

    So really how to deal with these non unique values?

    Change them on the fly when reading them and then change them back after modifying the value and before writing the file?

    Any ideas appreciated.

    Regards

    Neil

  19. #19
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Combine two arrays into a third.

    Leith

    I have now managed to work my way through the problem which was more complex than first thought.

    I changed the code to only read certain portions of the file and miss out a chunk in the middle where i was never going to change a value.

    Then i just altered the code line where the value was changed to make sure the arrRows was correctly correlated to the arrKeys. I had to add 76 to arrRows to allow for the rows that were in the initial array containing all rows but not loaded into the dictionary array.

    Sorry this was a bit convoluted but initially i could not understand how your code worked. After a good few hours of testing and reading up about dictionarys i finally twigged what was going on.

    Thanks for your help.

    Regards

    Neil

+ 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] Combine two 1D arrays
    By billyjo182 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2017, 11:28 AM
  2. VBA to Combine Spreadsheets using Arrays
    By batchjb69 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2017, 02:29 PM
  3. How to combine 2 arrays of data into 1 array
    By london7871 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2016, 01:18 AM
  4. Combine Arrays
    By kreitzig in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-04-2013, 02:31 PM
  5. Combine arrays
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-01-2011, 05:39 PM
  6. how to combine named arrays into a matrix
    By luv2glyd in forum Excel General
    Replies: 9
    Last Post: 06-19-2010, 02:09 PM
  7. Combine - by summing - two arrays into a third
    By ElmerS in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-25-2010, 04:10 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