Combine rows that have the same value in Column A

[FerdyHar]

Hello everyone,

I'm starting out in Excel VBA but don't really know where to start on this problem. At work we get a report out of the system that is currently not to our liking.

Excel VBA problem.PNG

This is an short example of how the report comes out of the system. It is about employees moving to a different housing unit.

What we want is the following:
- All rows in the range that are empty should be removed (delete row) (yellow)
- All the rows that have a value in column A that doesn't match any other row should be removed (it is about people moving from one place to another, if an employee number is only present once, they are only moving out or in, not both) (Orange)
- The information from columns H, I and J ( The IN columns) should be added to the row that has the OUT information. The row with the IN information should afterwards be removed.

The result you would get in this case is:

Excel VBA problem 2.PNG

Note that in the actual file it can be several hundred lines.

Attached is the file shown in the images. Thank you for the help in Advance and feel free to ask if anything is unclear.

Ferdy

[MickG]

Try this :-
This code assumes the "Out" data always comes before the "IN" data

Please Login or Register  to view this content.

Regards Mick

[MickG]

Revised Code , forgot about "Orange" cells.

Please Login or Register  to view this content.

Regards Mick

[FerdyHar]

Thank you for the response Mick, and sorry for my late reply, been real busy at work.

The code works perfectly for what we want and luckily more accurate than the manual version they are using currently.

Thanks a lot!

Ferdy