Forum Discussion
Trying to fill Column using partial match from another Sheet
Not super tech nor Excel savvy, so I'm having issues trying to put words on what I'm trying to do for a Google search.
Basically, I have an Excel file with 3 sheets; one has a list of all my company's clients with their phone numbers, one has a list of 400+ of the clients from the first sheet and a third one has a list of 80 of those clients.
I'm trying to extract my client's phone numbers from the first sheet to both the others.
My clients have numbers that can range from 1234-0 to 123456-0.
The "-0" part can only be "-0" or "-1", but the first part ranges from 4 to 6 various numbers.
However, both Sheets 2 & 3 have the number as is, while Sheet 1 has the first part in a column and the "-0" or "-1" in another column.
In short, on the tables below, I think I would need a fonction in sheet 2 & 3's "Phone Number" column which could compare Sheet 1's "Client Number" column with Sheet 2 & 3's "Client Number" and if it finds a partial match (because of the "-0" or "-1" difference) extracts the info in the corresponding "Phone Number" column to the empty "Phone Number" column on Sheet 2 & 3.
Using Excel version 2511 if that's useful.
Sheet 1 (Full list) :
| Client Number | -0 / -1 | Client Name | Phone Number |
| 123456 | -1 | Microsoft | 1234567890 |
| 9876 | -0 | Apple | 9876054321 |
Sheet 2 & 3 (Smaller lists) :
| Client Number | Client Name | Phone Number |
| 123456-1 | Microsoft | |
| 9876-0 | Apple |
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.
7 Replies
Hi CRenaud​ ,
See the screenshot above. In cell E2 of Sheet1 I have applied below formula:
=LET( cn, TRIMRANGE(Sheet1!A2:B10), tr, TRIMRANGE(Sheet1!D2:D10), IFERROR(INDEX(tr, SEQUENCE(ROWS(tr)) / BYROW(TRIMRANGE(TAKE(cn,, 1)) & TRIMRANGE(TAKE(cn,, -1)) = TRANSPOSE(VSTACK(TRIMRANGE(Sheet2!A2:A10), TRIMRANGE(Sheet3!A2:A10))), OR)), "No match") )Change the range in formula as per you need.
Try it and let me know is worked or not.
IlirU
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.
Hi CRenaud​
I would recommend you format your data as structured Tables Below Table1 is what you have in Sheet1, Table2 represents your Sheet2 (Tables can be on separate sheets)
in I4
=XLOOKUP( [@[Client Number]], ( Table1[Client Number] & Table1[-0 / -1] ), Table1[Phone Number], "No match" )
