+ Reply to Thread
Results 1 to 34 of 34

Automatic Split of cells based on example with expanded definitions as per "key" in a loop

  1. #1
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Automatic Split of cells based on example with expanded definitions as per "key" in a loop

    ​Hi guys,


    I need to transform a data input from the attached example as per follows and struggle to write a script that would cater for such needs. Please see the attached.


    The data shall be copied from "original" and the columns should be split as "transformed" based on the information in the "key" sheet.


    The loop should then run row by row and next line shall be inserted into next available row as illustrated on example in attachment.


    Many thanks for your guidance!


    Regards,

    V
    Attached Files Attached Files

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Sucuri Website Firewall coming up...Cannot post code...
    I am certain someone with Regex proficiency can simplify...See attached
    Last edited by sintek; 04-26-2020 at 04:40 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    See next code
    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Okay, Let me actually introduce you to the full issue that i am having as I am struggling to accommodate the suggested code in a flexible way.



    I am trying to create a script that will add records from original file to newly built template as per attached.



    Please note that some of the columns have strings that need to be split and one of them uses the dictionary that I attached before.

    Also, the next record always have to be pasted in the next empty row :S



    If you could add the comments it would help as well as I am learning this part of VBA...many thanks!



    Your help is immensely appreciated!
    Attached Files Attached Files

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    I just don't get it...Why upload a sample file that does not reflect your actual requirement from the get go...
    We have wasted valuable time on unnecessary coding...

  8. #8
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    My apologies, the only reason for that is that i did not actually want to waste anybody's time as I wanted to implement your coding to the whole template. However, struggled to do so... My apologies, I agree, should have posted the whole file from the beginning and will do so going forward.
    Would still appreciate if you could help though.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    should have posted the whole file
    Not whole original file Velehrad...Just a exact setup example and a accurate step by step detailed description of what needs to happen...The current attachment of post 6 makes no sense...
    Perhaps jindon or PCI can make sense of it...Sorry I couldn't help...

  10. #10
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Sorry, I am new here so was not really sure how it works...If you change your mind, please let me know so I could elaborate on the attached. Otherwise keep well and thank you!

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    so I could elaborate on the attached
    That is always a great idea...

    Original sheet...
    You have blue highlighted cells...I assume only these need to be utilized...Are the column Headers|Numbers|Count always the same...
    newVersion sheet
    Where do these headings come from...Not same as Original sheet
    What is "InsertUpdate" and "ORG"

    Does not make sense...
    I suggest uploading a sample file with actual info...and a step by step explanation to get result...

  12. #12
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Thank you so much Sintek for not giving up on me! Just give me a moment, I will reiterate the file.

    Thanks again.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Please Login or Register  to view this content.
    Edit: Fixed a bug.
    Last edited by jindon; 04-27-2020 at 07:00 AM. Reason: One line fixed.

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Well there you go...
    jindon = The Force is strong with this one!

  15. #15
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Thank you so much, Jindon! You are a legend! could you please just briefly comment on the distinct sections of the code, So I can learn a bit myself/(so I dont bother you guys next time?)

    Thanks again!
    T

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Try to understand it for yourself first and ask specific line(s) that you don't understand.

  17. #17
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Hello again, Jindon and thanks so much for your time.

    So I slightly amended the template (but not the input of the cells where the code yields its results), however, the code breaks in the below line.

    b(n, 10) = Trim$(x(2)): b(n, 11) = Trim$(x(1)): b(n, 12) = Trim$(x(0))

    I have also attached the alternative form and would like to understand how can this code be adjusted to it (as the difference is minimal) and understand what exactly broke - given the line above?
    Also, what happens if I update the "key sheet" with new items/definitions?

    Would that impact the code?

    Many thanks!
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Hi Jindon, thank you for the above...


    The code ran but did not apply to column E and column S ("in the "NEW file - i.e. F and AA in the original file").

    I have attached an example of two items within ("original") and how these should transformed into ("new") - sorry if I missed anything before..(?)

    I hope this clears things up. Please note that column A and C dont change but as the rest are placed in the first available row once the previous record was processed.. The rest should move as per the sample.

    Your previous code was very close, so do you think you could amend it to fit the attached as I am struggling to do that.

    I have also just expanded on the "key" file but dont think this will affect anything.


    THANK YOU SO MUCH!
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    If the newversion sample sheet is your expected result then I don't understand what you are trying to do now.

  21. #21
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    It is one of them but I want to understand how the code changes if applied to the one I sent you at last.
    I want to paste the data as into the "original" and have them come out (using the code and key) as the newVersion?

    Or which part do you not understand. I can explain?

    Sorry to take up your time, I promise the last version is the last sample I need an advice for...

    Much appreciated!

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Don't do thing like this without try to understand the code for yourself.
    We are not your personal coder.

    There is a Commercial forum that you can pay for.

  23. #23
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    No that is not what I am trying to do, Jindon.

    I am trying to understand why does the code not work once I try to apply it to the changed template and why does it break in the line that I copied here, man :S...

  24. #24
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    In other words, I would like to understand the dynamics of the code and how to make it more dynamic myself...hence why I also asked for comments. The attached sample was just to help with the illustration...

    Sorry for misunderstanding. :S

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Quote Originally Posted by Velehrad View Post
    I am trying to understand why does the code not work once I try to apply it to the changed template and why does it break in the line that I copied here, man :S...
    That doesn't mean that "You are trying to understand".
    The reason why it doesn't work is only because you are changing the data set.

  26. #26
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    I would like to understand the dynamics of the code and how to make it more dynamic myself.
    You will need a lot more Excel Experience in order to understand jindon's code and apply it to other datasets...
    This cannot be taught by one thread...Start working through the forum and try to learn...

  27. #27
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Exactly my point though... That's why I am trying to see how could I change the code to make it dynamic - in accordance to how the data changes and understand how to change it myself - which I struggle to.. that is why i asked you to comment on the code as I dont understand why does it break where it breaks..

    I really appreciate the time you spent on this - no need to accuse of taking advantage of this help forum

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Like I already mentioned.

    If you want all the lecture about the code given from someone, you need to understand the code line by line for yourself.
    Otherwise how can you modify the code?

    It seems to me that you are trying to find the difference by the difference data set, it will be endless...

  29. #29
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    I do understand the code partially as I have been working with vba and excel for a while... it is just that some parts of the code I struggle to adjust to differently structured dataset..that is it.

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    I said,.............
    Quote Originally Posted by jindon View Post
    Try to understand it for yourself first and ask specific line(s) that you don't understand.

  31. #31
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Is there really no way you could help me to adjust the code that i attached at last?

    I am struggling here:

    x = Split(Replace(Mid$(a(i, 22), 3), ",", "~"), "~")
    b(n, 10) = Trim$(x(2)): b(n, 11) = Trim$(x(1)): b(n, 12) = Trim$(x(0))

    you declare this to be a three string field where you split the strings that are in a cell V (original sheet) and split them in the column (,10); (,11) and (,12) within the NEW sheet.

    What I dont understand is how come it doesnt work with the new form as nothing really changed - the data seems to be in the same place

    Thanks!

  32. #32
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    No, that is not the line that actually raise the error.
    That line should work as long as the column references are the same.

    The biggest difference between 1st data set and the 2nd is the length of the string in one cell.
    Some cell contains more than 4000 characters, and that made Index function to fail.
    Please Login or Register  to view this content.
    so changed to
    Please Login or Register  to view this content.
    and the column reference for variable "a"has been adjusted accordingly.

  33. #33
    Registered User
    Join Date
    04-21-2020
    Location
    toronto
    MS-Off Ver
    2017
    Posts
    23

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Oh Okay, that makes sense, thank you!

    The next thing I am struggling with is the the (,27) in the sheet "original" as I am trying to place every item in [] on a separate line.

    temp(3) = Split(Replace(mid( a(i, 27), "[", "]", "")

    the above doesnt seem to do the job, any suggestions?

    Thanks

  34. #34
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatic Split of cells based on example with expanded definitions as per "key" in a

    Are you joking?
    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)

Similar Threads

  1. Copying specific cells based on cell value "P" or "O" to sht1 or sht2
    By Ity007 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-01-2016, 07:53 AM
  2. [SOLVED] Copy cells from range "C3" to "F3" with loop and blank field
    By masterm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-16-2015, 01:03 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] Expanded and collapsed the rows by toggle "+" or "-" sign on left of the text
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-24-2013, 02:49 PM
  5. Replies: 3
    Last Post: 04-14-2013, 11:53 PM
  6. Change "Parent" cell based on results of multiple "Children" cells
    By ccowman in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-14-2012, 02:33 AM
  7. Split cell values based on "," and "-" and format as shown below
    By Roop in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-22-2012, 12:28 AM

Tags for this Thread

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