+ Reply to Thread
Results 1 to 15 of 15

Macro for data present in multiple rows and columns following multiple conditions

  1. #1
    Registered User
    Join Date
    08-30-2015
    Location
    chicago
    MS-Off Ver
    2007
    Posts
    32

    Macro for data present in multiple rows and columns following multiple conditions

    Combination is complex
    Attached Files Attached Files
    Last edited by macrolearnerkk; 09-07-2015 at 10:21 AM.

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,526

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Questions:
    For type A, specifically in D4, E5, F6 the data if it exists will only ever be three entries max and will always be staggered like this?

    In K14 and L15 you have extra data and I understand you want that data included, if there is extra data like this, will there always be a blank row between?

    This data will always be staggered also?

    What's going on with columns G,H,I & J?
    Last edited by skywriter; 09-06-2015 at 03:53 AM.
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Hi MacroLearnErkk
    .
    . I am learning myself and was attempting to answer this as skywriter came in and sensibly tried to clarify what you want. I had started so i finished, but with hindsight maybe it would have been better to wait – I made a few assumptions ( never good to assume in any business , I think )

    . For example I……
    . Assume Date column 1 is an indication of last entry in any row.
    . Other assumptions were based on assuming you picked your test data carefully to reflect all scenarios.. so for example the point skywriter asked about... that “ This data will always be staggered „ is true..


    . There are a lot of unnecessary / extra bits in the code because:

    . 1 ) I do that, as I am learning! – Helps me remember what is going on. ( And I hears “This is a teaching forum“ ( And debugging is easier !! )
    . - Yous can easily work through chopping bits out, simplifying steps ( often by substituting in for a variable what that variable is Let or Set to , so doing away actually using a variable for it etc.. etc… ).
    . - And you can think of doing a Function or two, for example to do the adding of an entry in the output Array to save repeating similar code lines.

    . 2 ) I left it a bit “raw” , typos etc.. as it is not too clear exactly of the output or possible input data. So I have not checked extensively. It seems to match your hand filled in results exactly

    .. here just a small bit of your Test data:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    K
    L
    M
    1
    Date
    Record No
    Type
    colA1
    colA3
    colA2
    colB1
    colB3
    colB2
    9
    06.09.2015
    TypeA
    10
    06.09.2015
    gh
    gh
    11
    06.09.2015
    kl
    kl
    12
    06.09.2015
    ij
    13
    06.09.2015
    14
    06.09.2015
    mn
    15
    06.09.2015
    qr
    16
    06.09.2015
    17
    06.09.2015
    1616
    18
    06.09.2015
    TypeA
    19
    06.09.2015
    uv
    ef
    20
    06.09.2015
    yz
    21
    06.09.2015
    gh
    22
    06.09.2015
    23
    06.09.2015
    kl
    24
    06.09.2015
    op
    25
    06.09.2015
    26
    06.09.2015
    6537
    27
    06.09.2015
    TypeB
    28
    06.09.2015
    12
    29
    06.09.2015
    34
    30
    06.09.2015
    31
    06.09.2015
    56
    32
    06.09.2015
    78
    33
    06.09.2015
    2356
    34
    06.09.2015
    TypeB
    Sheet1


    .. and then the output my code gives ( for the full data you gave )

    Using Excel 2007
    Row\Col
    A
    1
    ab cd ef
    2
    mn op qr
    3
    gh kl
    4
    gh ij kl
    5
    mn qr
    6
    uv yz
    7
    ef gh
    8
    kl op
    9
    12
    10
    34
    11
    56
    12
    78
    13
    ab
    14
    cd
    15
    ef
    16
    ab
    17
    cd
    18
    ef
    19
    12
    20
    34
    21
    56
    Output




    Let us know how you get in please and answer skywriter’s questions. Maybe we can give then another / better or improved alternative. I used Arrays just ‘cos I’m in to that recently.. Most would do it with spreadsheet stuff, I think..

    Alan

    Code in next post, post #4
    Last edited by Doc.AElstein; 09-15-2015 at 05:06 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Code for last Post # 3

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 09-15-2015 at 05:05 AM.

  5. #5
    Registered User
    Join Date
    08-30-2015
    Location
    chicago
    MS-Off Ver
    2007
    Posts
    32

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Combination in detail
    Last edited by macrolearnerkk; 09-07-2015 at 10:22 AM.

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,526

    Re: Macro for data present in multiple rows and columns following multiple conditions

    I'm not sure you really answered my concern regarding the data for record 1456. Okay I understand the D, E, F part. My concern is the multiple data in K, L, M. You have three data entries. gh, kl, ij and then a gap of a row in between, I asked you a question regarding this gap will it always be there if there is multiple data. Notice how row 13 has no data.

    Questions
    #1 Will there be a blank row between multiple data sets as in the example I'm citing.

    #2 If there are multiple data sets will it always be set of threes before the last data set. In other words here we have a set of three and then a set of 2, so if it was even more data can we count on it to be let's say in an example of 4 sets of data 3 sets of 3 and then the last set which might only be two?

  7. #7
    Registered User
    Join Date
    08-30-2015
    Location
    chicago
    MS-Off Ver
    2007
    Posts
    32

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Combination not clear
    Last edited by macrolearnerkk; 09-07-2015 at 10:22 AM.

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,526

    Re: Macro for data present in multiple rows and columns following multiple conditions

    I'm going to pass. Too much trouble at least for me the way it's formatted. Staggered data, out of order concatenation. You gave an example with a blank row and now say it wouldn't have a blank row. Sounds like there are probably some other variables that are going to cause issues. Maybe someone else will post something that will make it look easy, but for me I don't exactly know how to approach it. Maybe Doc A. can help you.

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,564

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Seems that macro I proposed in previous thread can be updated to take into account these additional data:

    Please Login or Register  to view this content.
    As you can see main change is different record type recognition mechanism:
    Please Login or Register  to view this content.
    And in
    Please Login or Register  to view this content.
    second loop is repeated in 3-rows chunks (I marked blue external loop - what is inside is the old code (only difference with original - just limited to 3 rows):
    Please Login or Register  to view this content.
    while
    Please Login or Register  to view this content.
    is left untouched

    Enjoy!
    Best Regards,

    Kaper

  10. #10
    Registered User
    Join Date
    08-30-2015
    Location
    chicago
    MS-Off Ver
    2007
    Posts
    32

    Re: Macro for data present in multiple rows and columns following multiple conditions

    @Kaper,

    Thank you.
    Last edited by macrolearnerkk; 09-07-2015 at 10:17 AM.

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,564

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Sorry to say so, but probably your data has different layout than the sample you posted. See code inserted in your sample file. It works!

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Macro for data present in multiple rows and columns following multiple conditions

    @ skywriter
    Quote Originally Posted by skywriter View Post
    I'm going to pass. Too much trouble at least for me the way it's formatted. Staggered data, out of order concatenation......... Maybe Doc A. can help you.
    .. I think ( maybe by luck or design – I cannot remember now ) I got my code to work on what I still think is his requirement, and I think his further replies to you have confirmed that. In any case it certainly gives still the results exactly as he wanted. ( I just got a bit confused now. / recently whether he wants to get any diagonal going right downward in the order K L M or as originally stated K M L. But again I think I have it right in the original code. And I think I understand his further answers to you. I think you may have been looking for ( I just thought a possible consequence might be if there is a an empty cell in L , but not in K and M in a diagonally going down to the left that he wants my code would give an extra space between the two concatenated values, but an extra Replace(___ bit could be added to change any two spaces to one so no problem there )

    Alan
    ………………………………………………………..

    @ Kaper
    Quote Originally Posted by Kaper View Post
    Seems that macro I proposed in previous thread can be updated to take into account these additional data:........
    . Thanks for supplying an alternative. I learn the most when I see how a Pro does a version of a solution I give. I will try to find the time to do justice to that later and work through it thoroughly and learn from it. At first glance I think I may be doing something very similar to you, but just in Arrays. And you do it more professionally and tidily by calling your Action Subs

    ( I missed the OP’s earlier Thread, it is almost a Duplicate, or rather would have been good to reference it when he started this Thread…. Thanks for catching the connection there.. )
    ………………………………………………

    @ Learningerrk
    . I think from all your further replies it appears my original code ( Post # 4) is doing what you want. I have tried Kaper’s code from the File given in post # 9 and it produces the same results as mine, that is to say exactly the hand filled in Output Sheet you gave originally.
    . Kaper’s code is a typical “spreadsheet interaction type” code so makes a mine comparison to mine.
    . Please let us know how you get on with both codes.
    . Let me know if I should simplify my code a bit, if you have difficulty doing that.
    .
    Alan

    ………………..
    P.s. 1. )
    For the next time, when staring such a Thread which clearly follows on from another, it would be helpful to reference it with a link like
    http://www.excelforum.com/excel-prog...rent-rows.html
    .. as that may help someone get started.

    P.s. 2 )
    I just thought a possible consequence might be if there is a an empty cell in L , but not in K and M in a diagonally going down to the left that he wants my code would give an extra space between the two concatenated values, but an extra Replace(___ bit could be added to change any two spaces to one so no problem there . But maybe some of you answers to skywriter suggested that situation may not occur anyway.

  13. #13
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Quote Originally Posted by Kaper View Post
    Sorry to say so, but probably your data has different layout than the sample you posted. See code inserted in your sample file. It works!
    ... Yes it does
    ( gives same results as mine ( from Post # 4 ) and identical to what the OP gave as sample hand fiiled - in wanted Output
    Last edited by Doc.AElstein; 09-07-2015 at 09:25 AM.

  14. #14
    Registered User
    Join Date
    08-30-2015
    Location
    chicago
    MS-Off Ver
    2007
    Posts
    32

    Re: Macro for data present in multiple rows and columns following multiple conditions

    @Kaper, @skywriter, Thank you once again for your valuable contribution. I really appreciate it.

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Macro for data present in multiple rows and columns following multiple conditions

    Quote Originally Posted by macrolearnerkk View Post
    @Kaper, @skywriter, Thank you once again for your valuable contribution. I really appreciate it.
    .. Hi,
    . Any Feedback on my code ?

    Alan

+ 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] Macro to transpose data from two columns into multiple rows
    By Wolopter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-23-2014, 07:18 AM
  2. Replies: 9
    Last Post: 12-15-2013, 10:05 PM
  3. [SOLVED] Combining Multiple Columns to Create Multiple Rows in Macro
    By TacoBrew in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2013, 04:26 PM
  4. Clock In/Out - Macro to Log Time by a Switch on multiple columns (multiple rows)
    By jasw529 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-30-2013, 06:45 AM
  5. [SOLVED] Macro to transpose data from two columns into multiple rows
    By Briansva92 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2012, 07:55 PM
  6. Multiple ccolums/rows to get data from multiple columns/rows (vlookup)
    By Ramzes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2010, 05:35 AM
  7. Replies: 1
    Last Post: 03-18-2009, 04:18 PM
  8. Make a macro run on rows/columns only if data is present?
    By bpreas - ExcelForums.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-09-2005, 03:05 PM

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