How to convert duplicated rows into single rows extracting some data from deleted rows

**darvin** · 08-01-2012, 07:49 PM

Hello,

I have a long list of data. Some of the rows are repeated (e.g. the same person) but each row has a unique piece of information (in a specified column).

I would like to have a single row for each repeated person but that puts the unique pieces of information in different columns. The number of repeated rows (e.g. persons) varies.

So I need a way to "automate" this as doing it one by one would not be feasible.

I have attached a spreadsheet that clearly explains by problem.

Thank you!

Darvin

**arlu1201** · 08-02-2012, 03:28 PM

Try this code

Please Login or Register  to view this content.

I have put in the trim function in the code because i found extra trailing spaces in some cells. This will not match the rows properly while identifying the duplicates.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

**darvin** · 08-03-2012, 06:37 PM

Thank you very much Arlette! I don't understand what you have done just yet but this is quite helpful as it is getting me closer to a solution.

I have a problem however. It seems that the merge works well but if row with a unique column is repeated 3 or more times the macro only extracts 1 data from the column.

E.g. if I have:

Revi Panidha 1 EC101
Revi Panidha 1 EC102
Revi Panidha 1 EC103

I would like to get

Revi Panidha 1 EC101 EC102 EC103 (spaces indicating columns)

But I only get

Revi Panidha 1 EC101 EC103.

Any ideas?

Thanks again.
Darvin

**arlu1201** · 08-04-2012, 05:13 AM

If i remember clearly, i had faced a problem with this same name Revi. Can you check if there are spaces before or after the full name? The length of the name should be the same as the duplicate. There should not be extra spaces. Thats why i had put the trim in place but i guess it didnt take care of it.

**Anita7** · 08-17-2012, 08:49 PM

Hi Arlete,

When I try to run the code above I get this error: Subscript out of range. What should I do?

Thank you in advance,
Anita

**jindon** · 08-18-2012, 03:17 AM

Try

Please Login or Register  to view this content.

**arlu1201** · 08-18-2012, 05:00 AM

Anita7,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

How to convert duplicated rows into single rows extracting some data from deleted rows

LinkBack

Thread Tools

Rate This Thread

Display

How to convert duplicated rows into single rows extracting some data from deleted rows

Re: How to convert duplicated rows into single rows extracting some data from deleted rows

Re: How to convert duplicated rows into single rows extracting some data from deleted rows

Re: How to convert duplicated rows into single rows extracting some data from deleted rows

Re: How to convert duplicated rows into single rows extracting some data from deleted rows

Re: How to convert duplicated rows into single rows extracting some data from deleted rows

Re: How to convert duplicated rows into single rows extracting some data from deleted rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions