|
ESL Forum:
Techniques and methods
in Language Teaching
Games, activities
and teaching ideas
Grammar and
Linguistics
Teaching material
Concerning
worksheets
Concerning
powerpoints
Concerning online
exercises
Make suggestions,
report errors
Ask for help
Message board
|
ESL forum >
Ask for help > merging cells with duplicate entries
merging cells with duplicate entries
|
moravc
|
merging cells with duplicate entries
|
Hello dear friends,
I have been searching for a tool which would find in an Excell Table 2 rows with duplicated entries and merge them into one single row. Eg:
western film with cowboys (west(r)n)
western from the west (west(r)n)
would become
western film with cowboys, from the west (west(r)n)
I don´t want to merge the cells manually one after another as there is about 1,000 cells to merge :-(
|
5 Jul 2011
|
|
|
|
yanogator
|
|
It �s a little complicated, but you do it with the IF function. It is easiest if you create a new table to be the merged one, then delete the original table when you are finished with it. New A1 would be old A1 In New B1 you would put the following: =IF(A2=A1,B1","B2,B1) That says that if the contents of A2 are the same as the contents of A1, then in cell B1 put the contents of B1, then a comma, then the contents of B2. Otherwise, put just the contents of B1 in new cell B1. Now, you wouldn �t type any of these cell locations (A1, A2, etc), you would click on each one in the old spreadsheet (where the duplicates are). Excel will then put in the proper notation to take the contents from the old sheet. It �s been a while since I taught Excel, so I might have made a mess of it. Also, this is a rather simplistic version that requires that the table be sorted on column A, and that there be only two of any item in Column A. Otherwise, it requires a LOOKUP function, which becomes more complicated. I hope this helps you some. I �m sure it doesn �t take care of everything (maybe not even anything) Bruce |
5 Jul 2011
|
|
|
moravc
|
Bruce, thank you very much, it sounds WONDERFUL, but I don �t know why the Excel keeps telling me there is a mistake in the equation, namely in the part A1,B1","B2,B1
Would you be so kind and try it in your Excel? (my version in XLS 2007 but I am pretty sure it doesn �t make a difference whether I use older xls than you...)
|
5 Jul 2011
|
|
|
yanogator
|
|
I spent a little time looking into it and haven �t been able to figure it out. I �ll have some more time tomorrow (It �s late Monday night here now). I might not be able to get it right, though. It has been a few years since I was a trainer in Office. Bruce |
5 Jul 2011
|
|
|
moravc
|
That �s very nice of you Bruce. Don �t worry if you don �t find the right solution. I can still use the table with double entries, I will just need twice as much paper to print it, but the table will be useful... Maybe I will find the right macro on the internet...
Thank you for your help!!! You are great!
|
5 Jul 2011
|
|
|
yanogator
|
|
I �ve sent you a PM, and we �ll continue this off the forum, so Ed doesn �t add to his list people who go on and on discussing things that have nothing to do with ESL. Bruce  |
5 Jul 2011
|
|
|
moravc
|
|
Thanks a lot Bruce! I have sent you an email and PM.
|
5 Jul 2011
|
|
|
