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.
You've asked a broad question, so I'll give some possible answers for you to further research. Come back if you still need help actually implementing one or more of these thoughts.
- Once you've added that "Live" column to your larger set of rows, you should be able to get a subset by using the FILTER function. This can be done dynamically, and refreshed as needed.
- If you're talking of a once-and-done event, you can just use Data....Filter, and then copy and paste.
- Power Query, if you have that on your system (not all do) could no doubt do it as well. (I'm one of those who doesn't have it, so I'm not positive)
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.
use vlookup
assuming the names that matches sheet 1 and sheet 2 are in column A ( example only)
can add a column on your sheet 1 name this as reference (example only)
type this formula on the cell below reference
=vlookup(a2,'Sheet2'!A:B,2,false)
