+ Reply to Thread
Results 1 to 17 of 17

Merge and sort two columns in a dynamic defined sequence

  1. #1
    Registered User
    Join Date
    01-25-2017
    Location
    Beaumont, Alberta
    MS-Off Ver
    2013
    Posts
    15

    Merge and sort two columns in a dynamic defined sequence

    ***Cross posted in another forum, no replies***

    I'm unsure whether this is better suited for VBA, or just a good formula in excel(?). I'm leaning towards the former as the research I've done thus far is all leaning that way (of which have yielded no results that pertain directly to my dilemma).

    My query is this:
    I have two columns of numbers, A, and B. I need to merge both columns of numbers, but in a defined order based on the value in a cell, in Column C.
    Eg. If the cell value reads 0, I need 1 line from Column A, and 1 from Column B, stacked and repeated in Column C;
    If the cell value reads 1, I need 2 lines from column A, and 1 line from Column B, stacked, and repeated with the next available, unused numbers in Column C;
    If the cell value reads 2, I need 3 lines from Column A, and 1 line from Column B, stacked and repeated with the next available, unused lines in Column C; etc...
    This needs to repeat in the 3rd Column until all the lines are exhausted from Column B. In each scenario, from Column A, I need the first available set of lines, then the second available set of lines, etc... (I cannot skip any lines from either Column A or B). There will never be a time that I ask for more than 1 line at a time from Column B. I may need up to 10 lines at a time from Column A.
    I could have up to 1000 lines of numbers in Column A, and up to 250 lines of numbers in Column B.

    Thus far, I've tried an offset formula that is going the right direction however, it only utilized the number on the line it was on in Column C. I'm thinking a Select Case statement in VBA may be able to automate this whole process however, I'm extremely novice at best and am unsure how to put it all together.

    Any suggestions on where to start or how to accomplish this task would be greatly appreciated.

    Thanks and regards,

    DHT_Tech

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Merge and sort two columns in a dynamic defined sequence

    Sounds doable, but I have two Requests:
    1) Thank you for mentioning the cross-post! I believe that this forum's standards require that you also provide a link to your post in the other forum(s) so that potential helpers can see any developments there. Please edit your post to include that link.

    2) Would it be possible to post a small sample workbook so we can clearly see what you're working with and experiment with solutions? Attachments can be added by using Go Advanced --> Manage Attachments to open the attachment upload window.
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Merge and sort two columns in a dynamic defined sequence

    What does " stacked and repeated " mean?
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    01-25-2017
    Location
    Beaumont, Alberta
    MS-Off Ver
    2013
    Posts
    15

    Re: Merge and sort two columns in a dynamic defined sequence

    Thank you for the response. My apologies on the cross-post, I'm still learning the etiquette. Link to my other post is as follows:
    https://www.ozgrid.com/forum/forum/h...fined-sequence

    I hope my picture below will provide a better idea, the workbook it's in is owned by the company I work for and there is quite a bit of proprietary information in there.
    *Note: Column B will always contain the same value

    Eg.1: Cell Value = 0
    Column A Column B Column C/Result
    14.30 3.63 14.30
    14.38 3.63 3.63
    14.39 3.63 14.38
    14.39 3.63 3.63
    14.33 3.63 14.39
    12.89 3.63 3.63

    Eg. 2: Cell Value = 1
    Column A Column B Column C/Result
    14.30 3.63 14.30
    14.38 3.63 14.38
    14.39 3.63 3.63
    14.39 3.63 14.39
    14.33 3.63 14.39
    12.89 3.63 3.63

    Eg. 3:Cell Value = 2
    Column A Column B Column C/Result
    14.30 3.63 14.30
    14.38 3.63 14.38
    14.39 3.63 14.39
    14.39 3.63 3.63
    14.33 3.63 14.39
    12.89 3.63 14.33
    12.89
    3.63

    I hope this helps. I look forward to anything you can provide.

    Regards,

    DHT_Tech

  5. #5
    Registered User
    Join Date
    01-25-2017
    Location
    Beaumont, Alberta
    MS-Off Ver
    2013
    Posts
    15

    Re: Merge and sort two columns in a dynamic defined sequence

    "Stacked and Repeated" refers to all of the data that end up in Column C is stacked rather than merged, one value on top of the other. Repeated refers to once the first round whether it be 4 from column A, and 1 from column B, it must repeat with the next unused available set from the respective columns to continue to build Column C.

    Thanks!

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Merge and sort two columns in a dynamic defined sequence

    Like this?

    A
    B
    C
    D
    E
    F
    G
    H
    1
    0
    1
    2
    3
    2
    A1 B1 A1 A1 A1 A1 C2:C24 and copied across: {=DHT($A$2:$A$21, $B$2:$B$6, C1)}
    3
    A2 B2 B1 A2 A2 A2
    4
    A3 B3 A2 B1 A3 A3
    5
    A4 B4 B2 A3 B1 A4
    6
    A5 B5 A3 A4 A4 B1
    7
    A6 B3 B2 A5 A5
    8
    A7 A4 A5 A6 A6
    9
    A8 B4 A6 B2 A7
    10
    A9 A5 B3 A7 A8
    11
    A10 B5 A7 A8 B2
    12
    A11
    #N/A
    A8 A9 A9
    13
    A12
    #N/A
    B4 B3 A10
    14
    A13
    #N/A
    A9 A10 A11
    15
    A14
    #N/A
    A10 A11 A12
    16
    A15
    #N/A
    B5 A12 B3
    17
    A16
    #N/A
    #N/A
    B4 A13
    18
    A17
    #N/A
    #N/A
    A13 A14
    19
    A18
    #N/A
    #N/A
    A14 A15
    20
    A19
    #N/A
    #N/A
    A15 A16
    21
    A20
    #N/A
    #N/A
    B5 B4
    22
    #N/A
    #N/A
    #N/A
    A17
    23
    #N/A
    #N/A
    #N/A
    A18
    24
    #N/A
    #N/A
    #N/A
    A19
    25
    #N/A
    #N/A
    #N/A
    A20
    26
    #N/A
    #N/A
    #N/A
    B5
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    01-25-2017
    Location
    Beaumont, Alberta
    MS-Off Ver
    2013
    Posts
    15

    Re: Merge and sort two columns in a dynamic defined sequence

    Yessir, exactly like that. Those are the results I'm after, I just need to automate the process.

    Thank you.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Merge and sort two columns in a dynamic defined sequence

    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Merge and sort two columns in a dynamic defined sequence

    I quite like shg's approach, but if you want a slightly uglier non-UDF solution, I think the following works - If the "Cell Value" variable is in F1, try the following in C2 and fill down:

    =IF(MOD(ROW(1:1),$F$1+2)=0,INDEX($B$2:$B$7,ROW(1:1)-(($F$1+1)*(ROW(1:1)/($F$1+2)))),INDEX($A$2:$A$7,INT(ROW(2:2)-ROW(1:1)/($F$1+2))))

    Take a look at the attachment for a clearer picture of what I did. For sample purposes, I changed your 3.63s to unique values to ensure the correct cells were being used
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-25-2017
    Location
    Beaumont, Alberta
    MS-Off Ver
    2013
    Posts
    15

    Re: Merge and sort two columns in a dynamic defined sequence

    Much appreciated. This will save a LOT of hair pulling.
    How would we set this up to trigger off of a single catalyst cell? What I have set up is a list that, when the user chooses what spacing sequence they are looking for, each text value with return a numeric value in the catalyst cell (0,1,2,3,4,5,6,etc..).?

    Thank you again!

    DHT_Tech

  11. #11
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Merge and sort two columns in a dynamic defined sequence

    If I'm understanding correctly, I think the catalyst cell is F1 in my approach and the third term in shg's UDF (i.e. =DHT(range1,range2,catalyst cell))

  12. #12
    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,900

    Re: Merge and sort two columns in a dynamic defined sequence

    CAntosh... if you think that was ugly... this is what I came up with:

    =OFFSET($A$2,IF(MOD((ROWS($1:1)),(2+$C$2))-1<0,INT((ROWS($1:1)-1)/(2+$C$2)),(1+$C$2)*INT((ROWS($1:1))/(2+$C$2))+(MOD((ROWS($1:1)),(2+$C$2))-1)),IF(MOD((ROWS($1:1)),(2+$C$2))-1<0,1,0),,)
    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

  13. #13
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Merge and sort two columns in a dynamic defined sequence

    Ha! No worries; as long as the car runs, nobody looks under the hood (bonnet?).

  14. #14
    Registered User
    Join Date
    01-25-2017
    Location
    Beaumont, Alberta
    MS-Off Ver
    2013
    Posts
    15

    Re: Merge and sort two columns in a dynamic defined sequence

    Thanks Glenn! I'm not above ugly, if it works.

  15. #15
    Registered User
    Join Date
    01-25-2017
    Location
    Beaumont, Alberta
    MS-Off Ver
    2013
    Posts
    15

    Re: Merge and sort two columns in a dynamic defined sequence

    My next dilemma now, is non-uniform spacing. I've just learned that there may be cases where the scenario could look something like:
    3 from Column A, then 1 from Column B, then two from Column A, then 1 from Column B, then maybe 5 from Column A, and 1 from Column B, etc... That being said, I'm not even sure how I
    would capture the input for something like that, other than a node by node input, which would pretty much put me back to pure manual input...

    Thanks again to everyone for all the help. I've yet again, become completely humbled by the contributions made.

    DHT-Tech

  16. #16
    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,900

    Re: Merge and sort two columns in a dynamic defined sequence

    CAntosh... Bonnet, over here anyway.

    DHT_Tech... It does work, I hope. It did take a while to create this monster.

  17. #17
    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,900

    Re: Merge and sort two columns in a dynamic defined sequence

    Two suggestions.

    1. Evaluate the solutions to your original problem (I didn't look at the UDF as, in general, VBA goes way over my head). That said, the 2 non-VBA solutions both seem to work. Report back, and then...

    2. Close this thread, starting a new one for the new problem. You will (almost certainly) have to mock up a spreadsheet to show what you want. IMHO, it's easier to validate solutions with nice simple numbers (1,2,3, etc) as inputs; rather than 14.63, etc - which are not memorable.

    No need to attach anything with confidential information. just numbers, scenarios and desired outcomes.

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

+ 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] Sort 3 Columns in a defined range
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-26-2016, 07:11 AM
  2. [SOLVED] Consecutive cells match a defined sequence then vlookup
    By IndigoSK in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-15-2015, 03:19 AM
  3. How to build a dynamic matrix table with user defined no.of rows and no.of columns
    By santhosh51 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-20-2013, 03:58 AM
  4. Serial number creation / pre-defined alphanumeric sequence / formula
    By beano129 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2012, 02:27 AM
  5. Dynamic Sort for defined, yet growing, data set
    By marishipu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2010, 07:11 PM
  6. Merge two columns with non-duplicates and sort
    By Zaeguzah in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-15-2010, 09:23 AM
  7. Merge & Sort Dynamic Lists with Data Validation
    By packetdog in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-05-2008, 09:26 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