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.
mathetes I keep getting errors when I try the 2 proposed solutions in this thread. Therefore, it's likely I am just not understanding. I tried to simplify things by placing the 2 columns next to each other. (One column is the master list and the second column is the list of customers that now have a "live" status.) The thought was to use conditional formatting function to identify the duplicates. The duplicates would represent the customers that are "live". I would simply sort these highlighted duplicated names and then place "live" into the implementation status column manually, or a quick copy across all of them. So, now I have a problem with false duplicates or duplicates that are not exact matches, which is what I need. I'm guessing this conditional formatting approach is the not the answer. Any additional thoughts? Appreciate your thoughts on this.
Is it possible for you to upload a sample of the file you're working with. It's awfully hard to prescribe a treatment that really is suitable without examining the patient in question. Not an image of the file; the actual file, or a subset of it. Just don't include any confidential information.
mathetes Attached is a very simple example. The first sheet/tab shows the lists I have placed side by side with the hopes of identifying exact matches. I am looking to compare column C to column A. Once the exact matches have been identified and highlighted (green in this example).............see sheet/tab 2, I will just sort column A by color and choose only the green highlighted companies (aka the EXACT matches). "Company 3" and "Company 3 Asia" would not be exact matches. Once I have them sorted, I will manually enter the word Live into the B column and now have all of my live accounts identified, and then just delete columns C and D because they will no longer needed. I'm sure there are more elegant ways of doing this, but I could not get the other equations to work. So this seemed like the simplest way. Although when I tried it, I was picking up matches that were not exact. Hope this is more clear now and I greatly appreciate your patience and assistance.