Combine multiple records onto one row based on unique name/surname in the first column

[Anita7]

I am trying to convert duplicate data in columns into single rows BUT extracting the relevant data at the same time.
Here I am uploading a sample in excel of what i really want to do.

Thank you

[arlu1201]

Try this code

Please Login or Register  to view this content.

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 View | Macros
Select a macro in the list, and click the Run button

[Anita7]

Hi Arlette,
Thank you but it seems that it's not working for me yet...
I ran it with a file with 100 rows and I ended up with 5 ones :SS
I'm new at VBA and I can't explain what is wrong...

[Anita7]

And when I try to run it with the sample which I have uploaded above the result is this:

Ann White 100
Julia Brown 100
Johny Blue 100
Julia Brown 200
Johny Blue 300

PS: Should I copy the code in a worksheet containing data or first copy/paste the code and pasting the data afterwards?

[arlu1201]

Did you put the code in a standard module?

You need to put the code first, then run the macro on the data.

[Anita7]

Yes, I got that but I don't understand if the worksheet on which I'm going to run the macro should be blank before I copy the code or it doesn't matter?
This is how it looks like the VIsual BAsic Editor. Visual Basic Editor.jpg
Thank you )

[arlu1201]

The code can be copied either before or after the data.

[Anita7]

But what is wrong? I'm uploading again the sample but with the code in it this time.
I don't get what I want..:S

[arlu1201]

The reason why its not working is because your earlier sample file had data in columns B & C. Hence, the code was created based on that.

Here is the updated code as per your updated sample file -

Please Login or Register  to view this content.

[Anita7]

Thank you so much Arlette,
Can I use this code on a sheet with 4thousand rows? May it cause any problem?

PS:In my case I need more than two records associated with the name in the first column.
Is there any need to make a change in the code?
for example: Ann White 100 200 300 500 and so on

[arlu1201]

You can use it on any number of rows. It wont cause any problem.

[Anita7]

Ok
What about the other question?
It looks like it is causing me some problems...
Thanks again

[arlu1201]

I have to re-work the code a bit.

[Anita7]

Please can you do that for me?
Thank you so MUCH )

[bheanloh]

I modified the code. See if it works.

Please Login or Register  to view this content.

Boon

[mike7952]

See if this will work for you

Please Login or Register  to view this content.

[Anita7]

Yes, it really works )))
Thank you sooooooo much @Mike and Boon
How can I mark it as solved?

[arlu1201]

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.