Forum Discussion
Trying to fill Column using partial match from another Sheet
Since you start off with an acknowledgment that you are, and I quote, "Not super tech nor Excel savvy," I'm hoping you'll be willing to consider a different approach altogether.
In general, it's a mistake from the perspective of design of databases to have redundant tables of data. But that's what you're seeking to create: a master table and two subordinate tables, all containing at least these three data elements in common: a client number (two variants of that), client name, client phone number.
Redundancy of data is a problem, in that maintenance of coherency between the three requires extra effort, the possibilities of discrepancy (i.e., error) are great unless scrupulous attention is paid to ensuring data integrity among the three (or more?) sets of data.
You don't explain the reason behind sheets 2 and 3, so for the sake of discussion, let me assume these are clients of varying degrees of importance, the degree of importance perhaps based on something like contract size. In any event, whatever that distinction is, you could reflect that very difference (and more) by simply adding a column to the master data set (sheet 1 in your instance), a column headed perhaps "Priority" or "Echelon" and then assigning a code of some kind that captures the essence of why you've separated them out in the first place. The "Priority Code" could be a simple A-B-C-_____
Then, whenever needed, you could use the FILTER function to extract from that master list all desired data of all those in Group A. But updates of data would always be done one place only--in the master list.
Since you start off with an acknowledgment that you are, and I quote, "Not super tech nor Excel savvy," I'm hoping you'll be willing to consider a different approach altogether.
In general, it's a mistake from the perspective of design of databases to have redundant tables of data. But that's what you're seeking to create: a master table and two subordinate tables, all containing at least these three data elements in common: a client number (two variants of that), client name, client phone number.
Redundancy of data is a problem, in that maintenance of coherency between the three requires extra effort, the possibilities of discrepancy (i.e., error) are great unless scrupulous attention is paid to ensuring data integrity among the three (or more?) sets of data.
You don't explain the reason behind sheets 2 and 3, so for the sake of discussion, let me assume these are clients of varying degrees of importance, the degree of importance perhaps based on something like contract size. In any event, whatever that distinction is, you could reflect that very difference (and more) by simply adding a column to the master data set (sheet 1 in your instance), a column headed perhaps "Priority" or "Echelon" and then assigning a code of some kind that captures the essence of why you've separated them out in the first place. The "Priority Code" could be a simple A-B-C-_____
Then, whenever needed, you could use the FILTER function to extract from that master list all desired data of all those in Group A. But updates of data would always be done one place only--in the master list.
I do see the benefit of that, would there be a way to automatically assign a grade (A through C, for example) without manually selecting the 400+ clients through our 1000+ entries ? For example, could I add an extra column which would assign letters based on how many times the client's number is mentioned in the whole file (once, twice or three times) ? If that info could be helpful, Sheet #2 containing the 400+ clients are the ones who I represent, and Sheet #3 are my 80 top clients from Sheet #2.
Yes, that would be possible. (Most data manipulation tasks that one can articulate clearly can be accomplished in Excel. One of my rules of thumb is "If you think it should be possible, it most likely is; the challenge is to figure out how the brains behind Excel and its many functions have worked to make it possible.")
So in this case, how would you figure out how many times a client's number is mentioned? There are functions like COUNT, COUNTA, COUNTIF -- which would be worth exploring. Personally I'd go with COUNTIF, but that requires having some criterion for the condition. So how would I find out if a given client number is mentioned once, twice, thrice, or more. It would help to somehow or other get a list of each client number. Hmmm.....as it happens, there's a function called UNIQUE.
So run the UNIQUE function against the column in sheet 1 that contains all 1000+ entries. Or run it against your 400+ in sheet 2.
Then use the resulting list--each client number appears once--as the basis for the COUNTIF.
Now, I've not written those functions out for you, because I don't have your workbook to apply them to, but I would suggest that one of the best ways to learn how to do things like this is to play with Excel yourself. Do it on a copy of your workbook, especially if other people use it.
And this resource, ExcelJet, is a great place to do your research into the various functions and how to use them.