I have been trying for some time now to do this, and it is very frustrating.
After I geocoded, I now have the X and Y values and the addresses, but I do not have the unique number associated with them. There are thousands of them, so I cannot add them manually because of the time frame.
Here is an example of what my current data looks like...
In "Set 1" the first and second numbers represent the x/y coordinates. Then, it is the street address (110 Street, 111 Park, 112 Road, 113 Ave, and 114 Lane).
In "Set 2" the first number represents the unique ID. Then it is the street address (110 Street, 111 Park, 112 Road, 113 Ave, and 114 Lane).
|Set 1||Set 2|
|1 3 110 Street||99 110 Street|
|2 4 111 Park||100 111 Park|
|3 5 112 Road||101 112 Road|
|4 3 113 Ave||102 113 Ave|
|5 9 114 Lane||103 114 Lane|
I would like to match and combine these somehow in Excel. The final output should look similar to "1 3 110 Street 99 110 Street". Since there are thousands of entries, I need to partially match the data and then combine the two cells together. My idea is to combine the "110 Street" together since both columns share it. If there is another way I should do this, please let me know!
Solved! Go to Solution.
Why do you need the unique ID? Is it to match to another dataset that contains it? If not, you can just add a new unique ID field.
The problem with using the street address is that sometimes you can get the same street address in a different zip code, for example.
I am not clear what I am looking at. Are these two separate spreadsheets, two separate columns, two separate pages? Are the values in each set in a single field each (set1 and set2) or are the sets made up of separate fields (like x, y, address, unique ID, address)?
The VLookup can do this if the address portion of each set is separated out.
Hello again Richard!
You helped me a lot out last time with a similar question I had. Let me try to explain it better.
The "Set 1" and "Set 2" are 2 different columns. What it actually looks like is something similar to this...
The numbers with the decimal are the Lat/Long coordinates. All of this data is within a column so there are a total of two columns.
I can separate each of these values into their own column. For example, the lat would be in a column. The long would be in a column. The address would be in a column etc.
|Column 1||Column 2|
|32.432 84.342 110 Street||99 110 Street|
|33.432 85.342 111 Park||100 111 Park|
|36.432 89.342 112 Road||101 112 Road|
|22.432 54.342 113 Ave||102 113 Ave|
|72.432 14.342 114 Lane||103 114 Lane|
Are they two columns on the same page aligned in rows already? If so, then I do not understand the problem, since you could just concatenate the two columns together on the same row.
If they are on separate pages or in separate spreadsheets then you would first need to break apart the address from each component first and do a match on the address that is common in both using ZLookup.
I have attached a spreadsheet that shows how to parse the two columns if they were on separate spreadsheet pages and then do a match using VLookup. After matching, just concatenate as needed.
Thank you for taking the time to show me this spreadsheet. It is very helpful! Sometimes I just really over think something and make it more difficult. In this case, I really did that. I will consider this a lesson well learned!
Thank you both!
I don't know about you all, but sometimes I just need to talk it through with someone. After doing just that, I figured it out. It took quite awhile, but it works!