Excel: search and replace data in two column

Lipi - Apr 9, 2010 at 11:01 AM
 Lipi - Apr 10, 2010 at 06:08 AM
I have an excel sheet with 1200+ cells of data (room areas for a huge floorplan, grouped and added by apartments, then floors, then all together).
The floorplans are changing, and I need a smart way of substituting data as it changes and identifying differences (rooms no longer listed, new rooms and such). The data I get out of archicad is in tab separated text format, basically I get the room name and area.

A (room name); B (area, and area sums); C (values of the if query to only get the differences, I need to see them to double check, if the differences are out of the ordinary); E (room name new); F (area new)

Every room has it's unique name, and so far I have managed to use that in a combination with vlookup (columns A and E) to sort the new data into the correct row as the old one, if it still exists, and then if statements to show the differences next to both new and old data columns (column B and F).

It all works well(-ish) up to this point, but I still need to go and copy new values into the old column a few at a time, since the paste values command copies the empty (but not blank, apparently) cells in column C over the area sums (B). It is taking me forever, and is prone to mistakes, accidental deletions and such. Also, I have it all on the same sheet now, and it's a bit of a mess.

Can anyone help me? I want to set up a system to make it simpler in the future, but unfortunately have only a basic knowledge of Excel. I catch on quick, if properly explained, though :)
I am quite willing to sent the file, if it helps understand the problem.

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 9, 2010 at 02:36 PM
First let me complement on great addition to my vocabulary "well(-ish)"

Could you post your book at some share site like https://authentification.site and post back the link. Easier to understand then what you explained and see the approach to solution