Grouping entries with same stems

[glasspanic]

First of all, the entries in the following file I refer to are in another language (Japanese, to be exact). I've tried to illustrate my problem using English examples, but they might be a little contrived, so please ignore that :D

I have a list of about 2000 rows/lines of text. Each line/row contains words (which in the grand scheme of things relates to an individual character).

The problem arises that many of these entries share stems and thus take up quite a bit of space. To put this example into English, imagine an entry
larg.e larg.er larg.est gre.at gre.en

If possible, I would like to group these entries such that they come out as
larg.e,er,est gre.at,en

That is, to have a macro or something compare the stems for all the words in a line, and group the ones that have the same stem before the ".".

There are also some instances of prefixes/suffices, which are denoted with a "-". I would like the "-" deleted and then the word treated as a normal entry. Through this some duplicates would arise which would also need to be removed.

So

larg.e larg.er larg.est gre.at gre.en -gre.en super-

Would become

larg.e larg.er larg.est gre.at gre.en gre.en super

Which would end up as

larg.e,er,est gre.at,en super

This is done on a line by line basis - only words in the same line would be compared.

If someone is able to tell me how I might do this, or, even better(! :P), could do it for me themselves, that would be awesome.

Thanks in advance!

[rylo]

Hi

Assuming that you have your data on sheet5!A2:G2, your cursor is somewhere on sheet5 row2, and you have a blank output sheet called sheet6 then try

Please Login or Register  to view this content.

Hope that gets you going.

rylo

[glasspanic]

Just wow, thank you so much!

Find and replace... I can't believe I overlooked that.

Just a quick question though - how can I get this macro to run for each line/row of my spreadsheet? Otherwise it works perfectly (well, I added a column with a space bar between entries so that the resulting groupings have a small seperator).

Again, thanks a lot!

[rylo]

Hi

Here goes.

Please Login or Register  to view this content.

rylo

[glasspanic]

Thank you very much indeed!

Is it possible for the macro to add a space inbetween each group?

ie gre-at,en big-er,est etc?

Thanks in advance

[rylo]

Hi

It does, doesn't it? Does for me. One thing to, can you change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If that doesn't solve things, can you attach an example workbook showing what it does come back with for the example data, and what it should show.

rylo

[glasspanic]

Sorry

I was trying to highligh the space which was giving me problems. There is a space there

Thank you so much for your help!

[rylo]

Hi

I've interepreted this as being solved, so I've marked the post that way.

If not, then can you unmark the post, and give some more detail on the problem. Attach an example spreadsheet to help clarify.

rylo