topic Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data? in Data Management Questions

Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

KenricMcCay — Thu, 16 Apr 2015 00:38:29 GMT

Hello,

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!

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

SepheFox — Thu, 16 Apr 2015 00:44:01 GMT

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.

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

KenricMcCay — Thu, 16 Apr 2015 00:47:07 GMT

Sephe,

Unfortunately, I do need it in another database. I wish I could just add a new unique ID. Thank you for replying.

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

RichardFairhurst — Thu, 16 Apr 2015 00:57:31 GMT

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.

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

KenricMcCay — Thu, 16 Apr 2015 01:10:56 GMT

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

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

SepheFox — Thu, 16 Apr 2015 01:15:14 GMT

Oh ok, that's not so bad then. Calculate them out to separate columns then join them all back together into one column!

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

KenricMcCay — Thu, 16 Apr 2015 01:33:44 GMT

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!

Thank you.

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

RichardFairhurst — Thu, 16 Apr 2015 01:33:56 GMT

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.

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

KenricMcCay — Thu, 16 Apr 2015 01:40:04 GMT

Richard,

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!

Re: Excel/ArcMap - After Geoding Unique Values Gone How to Use Excel to Match and Combine Data?

SepheFox — Thu, 16 Apr 2015 01:52:23 GMT

Thanks for asking the right questions, Richard. I thought I knew what I was looking at, but apparently I didn't. Well, you know what they say about assumptions!