Hi folks,
I have bunch of sporadic data ranging from assigned code numbers 1 to 58, each representing a specific county, such as 1 for Marin County, 2 for Monterey, etc.



In addition, I have numbers, ranging from 1 to 980, assigned to specific (roadway) routes, such as 1 for Hwy 1, 80 for Interstate 80, etc.

Currently, I have a head list of routes, under which are the corresponding county number(s).

Now, I want to:
1) Name counties by their actual name, rather than their assigned code;
2) Completely reverse the data arrangement; that is, have the routes fall into each corresponding county.

How do I do it? Can it be done at all? Any help will greatly be appreciated.

Dave

Hi Dave,

I suppose it could be done, but it would help if you presented a mock-up of your data with a before and after.

With #1, first thought that comes to mind is a Lookup table, but again, without seeing your data I'm just guessing.

Here is a portion of my data base:

RouteCOUNTIESCOUNTY CODES11020273435364449511Del Norte 253542Siskiyou2s533Modoc3254Humboldt428293031385Trinity04u296Shasta52681115222429387Lassen

A B C D E F G H
Route Counties Code County
1 10 20 27 2 Napa
2 53 54 10 Marin
3 53 2 20 Yolo

Sorry, I think my previous reply in an Excel format was shrunk to a perfect gibberish statement.

Dave,

Can you upload a workbook? With the data you posted, I see four column headers, but it looks like possible 6 date element on row 1.

Hi Jeff,
Absolutely! I gave you only a portion of my data so you would have an idea what I am dealing with.
The 1st column is just the routes. The rest represent different counties (58 of them in the original database.)
I really appreciate you looking into this for me.
Dave

Please upload a workbook. Retyping data is not amusing :-(

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Sorry, here we go!

Attached Files

Copy of Route_list.xlsx (14.8 KB, 12 views)

Hi Dave,

Just to make this even clearer...

In row 2 you have route 1. You want to rename 10, 20, 27, 34 etc to their actual name?

Like 10 might be Austin and 20 might be New York?

So for row 17, would the 20 there also be New York and on row 27, would the 10 there be Austin?

Is this a one off or will you be constantly doing this for a larger set of data?

Can you simply not do a find and replace or would that be overly time consuming?

If F/R is not an option, would a macro work for you?

Please explain #2 from post #1 in clearer detail!

Do you basically want to transpose your data?

Do you need to upload a sheet with your expected results?

In row 2 you have route 1. You want to rename 10, 20, 27, 34 etc to their actual name? Correct.

Like 10 might be Austin and 20 might be New York? Correct.

So for row 17, would the 20 there also be New York and on row 27, would the 10 there be Austin? Correct.

Is this a one off or will you be constantly doing this for a larger set of data? There is another data similar to this that I'd be working on soon.

Can you simply not do a find and replace or would that be overly time consuming?I can definitely replace county codes with the actual names by using 'Replace All'. However, I still need to regroup by counties. That is, I will need to show which routes are in each county.

If F/R is not an option, would a macro work for you? A macro would work too.

Please explain #2 from post #1 in clearer detail! As you have seen, the original data is grouped based on Route numbers. Instead, I want to regroup it based on counties, so that I'd be able to see, for an example, that in Austin county (code 10 in your example above) routes 1 (column 1, row 2) and 20 (column 1, row 27) run through it.
I hope I have made myself clear now.

Do you basically want to transpose your data? Please see the above explanation.

Do you need to upload a sheet with your expected results? Yes.

Your feedback is greatly appreciated.
Dave

Sorry Dave, I can't seem to find the right solution.

I can get the first part done, but as it is, the second half is really what you are after.

Maybe with the "bump", somebody else with more macro experience than I will jump in to save the day.