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

4173
9
Jump to solution
04-15-2015 05:38 PM
KenricMcCay
Occasional Contributor II

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 1Set 2
1 3 110 Street99 110 Street
2 4 111 Park100 111 Park
3 5 112 Road101 112 Road
4 3 113 Ave102 113 Ave
5 9 114 Lane103 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!

0 Kudos
1 Solution

Accepted Solutions
SepheFox
Frequent Contributor

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

View solution in original post

9 Replies
SepheFox
Frequent Contributor

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.

KenricMcCay
Occasional Contributor II

Sephe,

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

0 Kudos
RichardFairhurst
MVP Honored Contributor

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. 

KenricMcCay
Occasional Contributor II

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 1Column 2
32.432 84.342  110 Street99 110 Street
33.432 85.342 111 Park100 111 Park
36.432 89.342 112 Road101 112 Road
22.432 54.342 113 Ave102 113 Ave
72.432 14.342 114 Lane103 114 Lane
0 Kudos
RichardFairhurst
MVP Honored Contributor

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.

KenricMcCay
Occasional Contributor II

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!

0 Kudos
SepheFox
Frequent Contributor

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

KenricMcCay
Occasional Contributor II

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.

SepheFox
Frequent Contributor

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!

0 Kudos