+ Reply to Thread
Results 1 to 15 of 15

Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

  1. #1
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    Could not figure out how to phase the problem.
    Have tried to come up with a straight formula to do this but it seems like VBA is needed. (happy if someone disproves that and has a forumla)

    For simplicity sake, we will say we have a wks with Column A for "Reciept numbers" and Column B; has text with Bracketed [ITEMS] and ad lib descriptions

    I need to get a comma seperated listing of ONLY what is in the [BRACKETS] in a single cell, Ideally in Proper format (First Letter Of Each Word Capitalized) but I can do that another way seperately if need be.

    This has been parsed or exported from an alternat source so unfortunately it is not consistant in how many [TERMS] are in each [CELL]
    So Row 3 might have [TWO] [BRACKETED PHRASES] and Row 4, 5, and 6 might have one each, say [FOUR] [FIVE] [SIX] with plenty other words around them...

    but all are associated with a single reciept (listed in A3) I want C3 to output: Two, Bracketed Phrases, Four, Five, Six

    I guess the easiest way is if I upload an example wks that shows what I'm trying to have happen.

    Like I mentioned, I have tried coming up with formulas to do this. like:=MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1) and many variations on that theme,.. but they all fail to capture multiple [BRACKETED PHRASES],. and if they do they capture all the text between the begining of the first bracketed phrase and the end of the last bracketed phrase,

    It just looks like this requires VBA, and sadly , I'm feeling out of my depth after a few woeful attempts.

    The key is just getting the Bracketed terms comma seperated into a single cell. Technically if that is done, I can eliminate the Brackets if they are still there, and I can change the capitalization.. But if it all happens in the macro, even better.

    Very late here, Mind scrambled.
    Thank you to any who attempt to help.
    Really,
    Attached Files Attached Files

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

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    If you are on Windows(not Mac), this should do.
    Please Login or Register  to view this content.
    Last edited by jindon; 03-07-2015 at 05:41 AM.

  3. #3
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    WOW!! NICE Jindon!!,.. You really are a guru.
    That works perfectly on my example!!

    Two things :

    1. Sadly, I failed to think clearly enough to include "alpha-numeric" Reciepts (Column A) as my real data has letters mixed in "2011NZ123"

    2. this module requires a blank row before each new Column A "Reciept #" ; What would be required to make it so that there may, or may not be a blank row, or possibly multiple blank rows .. (or would it be easier to ask how to force a blank row before each new Reciept #?)

    Thanks soo much already. This gives me something to work with,.. Now if only the data given to me was even remotely clean and consistant (it is not... much is parsed from PDFs, so the more robust and forgiving this module the better..

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

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    I need to see a sample workbook with possible difficult data situation and the result that you want.

  5. #5
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    sanitizedsample.xlsxIncluded is a sanitized segment of the data. It has been formatted somewhat as best as possible,. removing much of the extranius data and pagination references. (I do not believe the raw data could be handled by VBA)


    there are no blank rows before a new Column A item as I previously set the example.. Again, my mistake. I asume it is much easier to remove any blank rows than it would be to include only a single row before each new item. (correct me if I am incorrect there.)

    Column B is a bit chaotic (but you seem to have already conquered that in your initial code.)

    Column C is the "Desired Output".

    I would be exstatic even if you could just get this much to work. If you see that there is an easy way to conquer the uglier aspects I have included some examples towards the bottom. But again, I am working on the assumption that I will have to manually attack that to get it into the typical format and that's fine. I'm AOK with that.

    But if

    You can see at the bottom of the list some of the data that is ugly (single cell, with pagination, then the key (column A) number followed by the data intended for (Column B) Before that there is one with an open bracket, etc. (My assumption is that I will have to clean up some of the stuff similar to the bottom before applying the macro, unless you can figure an easy way around the pagination number .. By the way, the key (Column A items ; alpha numeric IDs, are close to standard in size with only a potential variation of 1-2 digits in either directions, larger or smaller, if that helps.



    Thank you again for everything.
    Last edited by fau5tu5; 03-07-2015 at 11:20 PM. Reason: added clarity and purpose.

  6. #6
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    By the way, I am in the process of cleaning all the "Ugly Data" now from about 500-1000 items. There are likely only another 800-2500 "Ugly" entries after that.
    Just don't want you spending too much energy helping me on that aspect as it is rather above and beyond.. Would much rather just have the cleanest version of your
    original solution that works with my data (alpha numeric Column A) and no blank rows before new Column A key... (or an easy way to systematically format it all so that there IS only
    a single blank before all new Column A keys)

    Thanks again.. If this works it will litterally save me many hours of manually inputting data from PDFs with potential errors due to typing.

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

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    Try this anyway and if you find any bug post back with the data and the results you want.
    Please Login or Register  to view this content.
    Last edited by jindon; 03-08-2015 at 12:34 AM. Reason: : Rows("1:2").Delete should be : Rows(1).Delete

  8. #8
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    sanitizedsample2.xlsx

    That almost works, except the Formula is based on the word "Source" which is not always present. (I realize after the fact that the chunk I took and sanitized for you to sample DID all have the word "Source" at the begining of each set of data in Column B,.. but that is far from the standard (maybe 50%)

    And if an ID DOESN'T have "source" as it's first word in the column B all its [Bracketed] items are listed in the item befor it.. so if 5 items don't include "source" as the first word, then all [Bracketed] items from all 5 items are listed with the last item that did have "source" .

    So I either need the formula to be based on the Key ID (Column A number) or I need a way to insert something unique at the head of each Column B Data group that could be used instead of "source" without throwing off the corrolation from the Column A IDs and their Column B data.

    I am reuploading a similar version of my last sanitized version, but this one with some of the Column B Terms groups not including "source" just in case I was unable to convey the issue effectively or using the correct terminology.

    Thank you again for everything...

  9. #9
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    I think it could work if: There was a way to say: Look at column A, if there is a number there, then simply insert "cr#zyWo%d" at the very beging of the corrosponding Cell in Column B without affecting the rest of the text in that cell otherwise.

    Then we change "Source" to "cr#zyWo%d" in the VBA, and I assume it would work.

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

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    What about the block of data at the bottom in earlier file?

    Not anymore?

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

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    For the latest file
    1) change
    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    2) change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    Testing your fix now.. (have to proof against multiple data sources) but so far so good

    As for the block of data in earlier file, I manually went through and cleaned those up over a couple hours.. Some was really jumbled and there was no way to get it cleaned up without understanding it. No system would know how to break it up rationally.

    Fingers crossed. What times are you normally available. (It's 2:00 AM here, but I'll stick with it if these are your peak hours)

    Thank you sooooo ooooo ooooo much....

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

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    That's good.

    It's 6 PM here and I normally available in daytime. say 10 - 8 but depends on a day of course...

    Regarding the irregular block of data, it will be much easier if you don't care about the position for the output.

    But I think you do care....

  14. #14
    Registered User
    Join Date
    07-02-2008
    Location
    washington
    Posts
    20

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    Yes the possition must relate to the row where the Column A ID is.
    This way I can hide Column B and create a report that shows CaseID and the system specificTerms associated with it.
    That is the exact output required.


    I'm ELATED! This is working EVERYWHERE!!!! I have to go to sleep now because my typing is causing errors, but I think this nails it...

    Still plenty of work to do yet, but realize how much you have just helped.
    Much Real World Karma is due you.

    Arigato Jindon Sensei !
    *bows*x2

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

    Re: Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

    You are welcome and thanks for the feedback.

+ 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. Vlookup with multiple results comma seperated in cell
    By ledworld in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-18-2013, 03:53 PM
  2. data seperated by comma and copied into multiple rows
    By naflas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2012, 07:49 PM
  3. Summing sequence of numbers seperated by comma within single cell
    By Mazb in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 03-24-2009, 06:51 PM
  4. Replies: 1
    Last Post: 07-04-2006, 07:25 AM
  5. Exctracting comma seperated data from a single cell
    By guyvanzyl in forum Excel General
    Replies: 3
    Last Post: 06-30-2006, 07:20 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