Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA - Excel (English)

    Hi folks, I am new to VBA and just starting out <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> . I am working on several lists which I would like to amalgamate into one list. Each list has one common field for cross referencing [this common field is either a chinese character, or chinese phrase]. I have two problems. First, against some chinese character/phrases, in ajoining cell, are lists, for example, fred, john, peter, etc, and so what I want to do, is take this one entry with multiple definitions [ie., fred, john, peter, etc - against the one chinese letter and phrase, and change the worksheet to show a separate row for each multiple so that, row a1 might be chinese character = fred, row a2 chinese character = john, row a3 chinese character = peter, etc.

    This leads me to the second issue. I want to actually reverse this process for other lists. One chinese character, for example, has 214 separate rows with different meanings [which are sanskrit variants of chinese]. What I want to be able to do, is identify all 214 rows, read that information, convert all those different meanings [ie sanskrit] into data arrayed in one cell only, separating each field by a [,] and [space] [", "]; so new list would show only one character/phrase in the first cell, and in the adjacent cell, the new list of different meanings [sanskrit - like, fred, john, peter, etc] would appear.

    I have worked out several ways, some manual [because of 214 occurrences], and some automatic when involving less than 30 occurences - a system limitation, or my ignorance of if, and, and nestled function [?]

    I would appreciate any suggestions, and helpful coding.

    Thanks in anticipation.

    Philip

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - Excel (English)

    Welcome to Woody's Lounge!

    It would be helpful if you could post an example of what you're working with.

  3. #3
    New Lounger
    Join Date
    Aug 2004
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Excel (English)

    Edited by HansV to display data in table format - see <post#=164109>post 164109</post#>

    Thanks for your reply Hans - very much appreciated. Perhaps this will make things clearer.

    Example 1.

    Imagine a spreadsheet with 20000-30000 rows, with the following an extract from that spreadsheet:

    <table border=1><td>pravartana</td><td>進</td><td align=right>12</td><td>vṛddhī</td><td>進</td><td align=right>12</td><td>jaṅgama</td><td>進</td><td align=right>12</td><td>vicaṭana</td><td>進</td><td align=right>12</td><td>atikrama</td><td>進</td><td align=right>12</td><td>krama</td><td>進</td><td align=right>12</td><td>gamana</td><td>進</td><td align=right>12</td><td>gāmin</td><td>進</td><td align=right>12</td><td>gati</td><td>進</td><td align=right>12</td><td>praveśa</td><td>進</td><td align=right>12</td><td>pravṛtti</td><td>進</td><td align=right>12</td><td>vīrya</td><td>進</td><td align=right>12</td></table>
    The first column is sanskrit defintion of chinese character in second column, the 3rd column shows the number of times the chinese character appears in this spreadsheet, ie. 12 times. The most number of entries [rows] for anyone chinese character is 214, that is, instead of appearing 12 times as in this example, it appears 214 times. Other chinese characters occur to lesser extent over range of 20000-30000 rows.

    The following line shows how I want this information to appear:

    <table border=1><td>進</td><td>pravartana, vṛddhī, jaṅgama, vicaṭana, atikrama, krama, gamana, gāmin, gati, praveśa, pravṛtti, vīrya</td></table>
    Column one shows now, only one instance of this chinese character, with the 12 sanskrit definitions now appearing in column two, as a list with comma + space separation. It is possible to do this using normal excel functions, but only for a limited number of occurences .

    Ideally, the other 11 rows would be deleted at the end of this conversion, and preferably, the sanskrit entries would first have been sorted to make them appear alphabetically, so that when they appear as one list in column 2, they would follow each other alphabetically.

    The second situation is actually just the reverse of this:

    in this example, you would have this type of entry:

    <table border=1><td>進</td><td>pravartana, vṛddhī, jaṅgama, vicaṭana, atikrama, krama, gamana, gāmin, gati, praveśa, pravṛtti, vīrya</td></table>
    with a chinese character in column 1, and the list of sanskrit terms in column 2.

    The task would then be to delete this row, and replace it with 12 entries as appears below:

    <table border=1><td>pravartana</td><td>進</td><td align=right>12</td><td>vṛddhī</td><td>進</td><td align=right>12</td><td>jaṅgama</td><td>進</td><td align=right>12</td><td>vicaṭana</td><td>進</td><td align=right>12</td><td>atikrama</td><td>進</td><td align=right>12</td><td>krama</td><td>進</td><td align=right>12</td><td>gamana</td><td>進</td><td align=right>12</td><td>gāmin</td><td>進</td><td align=right>12</td><td>gati</td><td>進</td><td align=right>12</td><td>praveśa</td><td>進</td><td align=right>12</td><td>pravṛtti</td><td>進</td><td align=right>12</td><td>vīrya</td><td>進</td><td align=right>12</td></table>
    I hope this clarifes the situation, and apologies for prior garbled explanation.

    Regards

    Philip

  4. #4
    New Lounger
    Join Date
    Aug 2004
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Excel (English)

    Sorry, Hans, I should have enclosed copy of part of spreadsheet.

    Regards
    Philip

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - Excel (English)

    My browser has messed up my attempt to display your previous reply in table format, sorry for that.

    What is the purpose of the 5th column in the spreadsheet you attached? Should this be processed too?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - Excel (English)

    Perhaps you can use or adapt the macros in the attached version.

    To concatenate the data, make sure the worksheet with the unconcatenated words is active. Press Alt+F8 to display the Macros dialog, select Concatenate and click Run.

    To unconcatenate the data, make sure that a worksheet with concatenated words is active. Press Alt+F8 to display the Macros dialog, select Unconcatenate and click Run.

  7. #7
    New Lounger
    Join Date
    Aug 2004
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Excel (English)

    Hans,

    Just a quick thankyou for all your trouble. It will take me some time to work through the macros kindly provided, and I sincerely appreciated being pointed in the right directon. Thanks.

    The actual spreadsheet that this data came from has a number of columns, most of which I excluded to reduce file size. As for column 5, this is how the data originally appeared before I broke that data down into individual entries. Basically I did this by saving Excel file as Encoded text and reopened the appropriate column which then allowed me to separate each character by using [comma] as a separator, and thus producing a number of columns with each column presenting an individual character. It was then a simple matter of adding these columns together into one work sheet. Of course this was all done manually, and so the realisation was that I needed to tackle VBA and come to grips with its processing power.

    Thanks for your help, as it was very much appreciated.

    Philip

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •