Forum Discussion
Moving large subsets of Excel data from tab to another based on criteria
Here's your sample, with the formula I had suggested in Column B. I edited it to conform to your new arrangment of the two data sets, but that was a minimal change. The formula in cell B2 reads as follows:
=IFNA(VLOOKUP(A2,C$2:D$11,2,0),"")
I copied it down the length of your data set and you can see the result. I didn't bother with the color formatting, because you can just sort based on the presence of the word "Live" in column B.
mathetes I'm afraid I didn't explain it very well. In tab 1 I have a list of all account names and other data. In tab 2 I have a column with account names and a second column (implementation status) that has the word "Live" written it in, since I sorted out only the ones that are live. Whatever I do will need to take the account names from tab 2 and look for a match in tab 1. Once it finds the account name match, it will need to insert the word "live" into the column/cell next to it. By doing this, I update the accounts that have gone live. Too many to do manually. Hope this helps. Thanks.
OK, I've created what I think is a very simple example of how you can correctly add the word "LIVE" to your entries in Tab1--the larger list--with entries from Tab2, the sifted and sorted one that contains those that actually are "Live."
That simple sample is attached. One caveat you need to be aware of: the names in Tab1 and Tab2 will only be matched if they're exactly the same. Even a trailing space (invisible to the naked eye, but very present to the exacting computer) will make it "not found."
But let's start with this. Let me know if it works.
The formula to enter into your Tab1 is =IFNA(VLOOKUP(A3,'Tab2'!A$3:B$5,2,0),"")
In English the VLOOKUP is finding matches that exist in Tab2 for the name in the Tab1 table, and then it's retrieving the word "Live" from column 2 of the table in Tab2.
If that formula were to return the "#NA" error messagedd--i.e., if it doesn't find a match--then the enclosing IFNA function just enters a blank space.
Now, I'm sure there are other ways to do this--Excel always offers multiple solutions--but this strikes me as the most straightforward assuming your data are basically clean. If it doesn't work, come back and let us know.