Macro to merge rows but keep column data

jonnumberone Posts 1 Registration date Wednesday October 17, 2012 Status Member Last seen October 17, 2012 - Oct 17, 2012 at 11:41 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 17, 2012 at 06:08 PM

I am new to this and I have seen there a number of questins regarding merging rows in Excel. I hope someone can help with this one:

I have three lists (lets call them B,P,S) that I am trying to combine in a single spreadsheet that will allow me to see particular figures against individual items (a list of stock items).
These lists are not the same, but do overlap a great deal.
I have put the three lists into one spreadsheet, which is now 1,900 rows and has three (relevant) working columns, with the three lists of items, P below B, S below P, in column A., the first list stock figure is in column B, second in column BP and the third in column BS.
I have used conditional formatting to identify which rows are duplicates in column A, but I now need to merge these duplicates so that the stock figure for each item from the original list (B,P,S) is in columns B,BP,BS, but against only a single reference to the stock item.

The aim is identify a total stock figure for each stock item. There are actually 35 spreadsheets, but as 32 of them only have about 15 common stock items in them, I have done that manually, it's the remaining 3 that are the headache.

Sort of:

Go to A3 (ignoring headers)
Copy A3
Find next
Is there a next/duplicate? No - Go to A4.
Yes - Go to Row(*) (the duplicate)
Is BP(*) blank? No - Copy BP*
Paste to BP3
Delete Row (*)
Yes - Is there another Duplicate?
(Is there another Duplicate?) No - Go to A4
Yes - Go to Row (**) (second duplicate)
Is BS(**) blank? Yes - Go to A4
No - Copy BS**
Paste to BS3
Delete Row **
Go to A4
Repeat x 1,900!

Any ideas would be very gratefully received.


1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 17, 2012 at 06:08 PM
Could you post a sample workbook with some sample data at a public file sharing site and post back the link to the file back here. It would help if you can explain your issue using that workbook as basis