Copy/Paste VBA to realign partially duplicated rows of data

Hi,

I am trying to use a macro to sort through rows of data and where two columns are the same on multiple rows, the macro then copies and pastes unique values from other columns onto the same row, in essence to consolidate and realign the data.

So, in essence, in my example spreadsheet, I am trying to look for where columns H and I are duplicated. If they are duplicated then I want to take the data from columns B, C, D, E, F, G and paste it into the same column in the first of the duplicated rows. Then, if the data in K and L have not yet been repeated within the duplicate rows, I want to copy and paste the contents to M and N (or O and P if M and N are already occupied). Then I want to delete the second of the duplicated rows, since all the unique data from it has been copied to the row above.

My macro sort of works like I want it to, but not completely. Here are the problems I am having:

1. When copying and pasting the values in B:G, the macro doesn't work right if a cell containing a value in the first duplicated row is further right than a cell containing a value in the second duplicated row. For example, for purchaser Z, when the macros copies the "x" in B38 and pastes it in B37, it deletes the 1 in E37, bu that 1 needs to remain where it is.

2. If the data in K and L is the same for multiple duplicated rows, it still copies the values over to M and N. For example, with purchaser P, since K20 and K22 both show "5523" but K21 shows "1491", I only want to copy "1491" to M20 and ignore the "5523" in K22.

3. The last problem I'm having is that when the macro copies and pastes the values in K and L to M and N, it also pastes them in K and L, overwriting the previous data. So for example, when the macro runs, I will end up with "9201" and "Avocado" from K4 and L4 in cells K2, L2, M2, N2, O2, and P2.

I have attached the example spreadsheet also with a tab to show what I want the finished product to be after the macro runs. I greatly appreciate any help you are willing to provide with any of these issues!

Here is my code up to this point:

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.