Forum Discussion
Moving large subsets of Excel data from tab to another based on criteria
How do I pull select data fields from one Excel tab into another? E.g. I have a complete and very large list of customers on tab 1. On tab 2 I have a subset of these customers that are "live"....but still too large to move manually. I want to insert the designation "Live" into a column on tab 1 for those that match the live select customers listed in tab 2. Hope this makes sense. Thanks.
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.
20 Replies
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.
- Hi,
Excel Community: https://techcommunity.microsoft.com/t5/excel/ct-p/Excel_CatThanks HotCakeX!
JimS-2145 you've asked your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space which is what HotCakeX was referring to- please post Excel questions here in the future.
