Forum Discussion
Partially Merging Rows From Two Excel files Into One
Hello,
I have two excel files on my Mac. Both contain information regarding real estate properties. One file is new and the other is from 2018. The problem I have is that the older file contains data in columns that the new file doesn't have data for (Columns K through R) and I would like to add to the new file. The new file contains certain rows of data that the old file doesn't have. The old file contains some columns the new file doesn't contain. I've attached a sample excel file to explain the situation, as well as screenshots of the excel file. The real files contain around 7,000 rows of text data. I can't share the actual file since it contains confidential information. The sample file contains identical rows and columns of the real files, with sample data inserted.
How can I add the rows of new data from the new file with the columns of text from the old file that are blank in the new file? Is there a way to merge both files together without losing data or mismatching rows and columns? Rows of data on the old file that aren't found on the new file need to be moved to the new file while still updating the rows of data still found in both files.
Columns A through E contain updated data in the new file, along with new rows not found on the old file. Columns F through I are the same in the new file, with the exception of rows that were removed in the new file or didn't exist in the old file. Columns J through R need to have the same data in rows that existed in the old file, while they will be blank for rows that didn't exist in the old file. Columns S through W need to have updated data regardless of whether it was on the old file or not. My biggest concern is ensuring the individual rows of data stay with the correct property number (seen in Column A) when merging the data together.
I'd appreciate any advice.
Thank you!
In K2 on "New Data" you may enter:
=IFERROR(IF(LEN(VLOOKUP($A2,'Old Data'!$A:$W,COLUMN(),0))<>0,VLOOKUP($A2,'Old Data'!$A:$W,COLUMN(),0),""),"")...... and drag it down and across column K:R.
The basic operation is done by this part of the formula:
=VLOOKUP($A2,'Old Data'!$A:$W,COLUMN(),0)Everything else sees to it that you get no NA#! errors (when the property DOES NOT exist in Old Data) or "0" (when the property DOES exist in Old Data but with blank cells in Col K:R).
2 Replies
In K2 on "New Data" you may enter:
=IFERROR(IF(LEN(VLOOKUP($A2,'Old Data'!$A:$W,COLUMN(),0))<>0,VLOOKUP($A2,'Old Data'!$A:$W,COLUMN(),0),""),"")...... and drag it down and across column K:R.
The basic operation is done by this part of the formula:
=VLOOKUP($A2,'Old Data'!$A:$W,COLUMN(),0)Everything else sees to it that you get no NA#! errors (when the property DOES NOT exist in Old Data) or "0" (when the property DOES exist in Old Data but with blank cells in Col K:R).
- That worked! Thank you so much!
