Forum Discussion
Data Sorting Issue!
Hey Folks! I'm in a bit of a quandry at the moment - I've got two arrays of data containing names. One is larger than the other, and I want to find the common names between them, and then isolate the corresponding rows corresponding to those names. For example:
Supposing I had these two arrays, I want to find the common names between each of them, and output an array of only the common names, with the additional company data linked to each name. (i.e. each person still has the right company associated with them). So for the above example, I'd ideally want the following as the eventual output:
To be honest, I'm not really sure where to look for this - every function that would appear to work doesn't allow me to group two columns together as an input value and compare to another 2 columns, so if I try to use MATCH to see if the first name registers somewhere, if it's a very common name, chances are that it will come up in a different entry, and similarly with last names. Any suggestions would be super appreciated! :)
Peter-
First off I want to say that I appreciate your sense of humor. Nice touch! Not sure you can do what you want all in one formula but I've used a two and a few helper columns in order to accomplish the task. You'll need to adjust the formulas based on your data structure.
1. An Array Formula to get unique values in each list
2. A simple Vlookup
I'm going to attach an example file as I think it may be easier to comprehend. When entering the array formula use key combination CTRL + SHIFT + ENTER then drag down.... When using the vlookup you can simple put this formula in cell B13 and then drag to the right and then down....
