Here is an approach that should work for you:
Create a new Excel worksheet.
Open file A and select the 4 columns, copy, and paste into columns A - D of the new sheet.
Repeat this for file B, pasting the data in columns E - H of your new worksheet.
In the first row of column I (cell I1) enter this formula:
=IF(A1<>E1,1,"")
Click on this cell and then scroll to the last row in your new file. Put your pointer at the last row and column L, then hit the shift key and click to highlight 4 columns and however many rows you have. Now use the edit commands to fill right, and again to fill down.
In this box you have created, every place you see a 1 shows you where your data sets are different. Now you can work through your data sets to align the data in your two sets, inserting cells in either set as needed to line things up. Be careful when you insert to move cells down, not right. Each time you insert cells, you need to recreate the formulas in columns I - L so they are comparing the right things. In most cases this is done just by highlighting and redoing the fill right and fill down. If you insert something in row 1, you might have to enter the formula in I1 first.
Eventually, you should see "1"s in columns I - K only where one data set has blank cells. You can then decide what data you want to restore in you A set, and copy from the B set to the A set. I assume B will have some "new data" you don't want in A.
When this is finished, if the original order is not some sorted data set you can easily reproduce, you might need to add an index column for later sorting. Click on column A and right click to select "Insert". Add a column in front of the B data set also if needed. In the first cell of the added column enter 1, and in the cell below that enter the formula:
=A1+1
Click on the cell with this formula, scroll to the bottom to select all cells in column A in the range of the data set, then fill down. Select the whole column and copy, then paste special "Values" to replace the formulas with constants. This will give you a way to preserve the order of this data if you need to do any later sorting (You will still have some blank data cells I assume). Repeat this for the B set if needed.
Now you have all the data you want for your file A. Copy that data and paste it back into file A replacing the original data (or into a new file A)
You can now sort this data by one of the data columns to isolate the blanks. Insert a line to separate the blanks from the real data, then sort the remaining data by the index value column (if you needed one). You should now be able to delete the blank data lines and have your file A the way you want it.
It's harder to write this than to do it <ggg> I do this sort of stuff in Excel all the time. |
