This is very useful function. For example, I have a fixed book inventory table in Excel. And again, I have another list of new inventory from another branch. Of course, some of their books listed in my inventory books, but some are not. I want to add this new book in my list. But how?

Here’s the explanation. Click to see bigger image.

I have table 1, which have a large amount of book inventory. I want to merge this inventory with table 2, but I don’t want book which is already listed in table 1, double listed

There’s a way which I was described in my post Removing same value in Microsoft Excel , which is :

METHOD 1

1. Merging two tables into one
2. Sorting this merged table based on it’s name column
3. Write a function to check the same value, something like : =if(B3=B2,”SAME”,””)
4. Copy and paste result with EDIT/PASTE SPECIAL/VALUE option
5. Sorting again, now based on “SAME” column
6. Delete Rows with the “SAME” sign

That’s easy too, at least compared with manual editing (WAWW, manual editing?).

Now I want to give you a EASIEST WAY. Here’s how.

METHOD 2

1. Err…both table has unique identifier, which is ISBN, right?. Ok, lets make this ISBN key as our keypoint.
2. Name ISBN column in table 1 with some name. To do it, just select ISBN value from table 1, and goto Name address (leftmost, near formula bar, see picture below). Name as you like (but with no space). Name box highligted. Look, I name it isbntable1.
3. 
4. Now put this formula beside ISBN column in table 2. This formula check if ISBN value in table 2 has correspond value in table 1.
5. 
6. The formula is =ISNA(MATCH(E19,ISBNTABLE1,FALSE)).
7. Note : E19 is ISBN in table 2 beside this formula column (with a small blue box)
8. Isbntable1 is name of ISBN range in table 1.
9. Now copy formula to the cell below. The result with TRUE value is your unique value which not listed in table 1
10. 
11. Very very easy. In this example, you can se that TRUE result is in order ( I just paste value from table 1 over and below table 2 value. That’s why). But what if your TRUE and FALSE result oscillating?. Just sort!. Remember, you must copy and paste special / value this result to get the right sorting (See Step 4 in Method 1)

If you like this post, give comments and share to friends.

Popularity: 1% [?]

About Admin

Blogging and, especially Wordpress lover. He can tweak small wordpress code to meet any need. Not a designer, though. Interested in programming world. He loves PHP and Visual Basic. You can argue, but I think, wordpress platform is the best blog platform today. Website | Email