Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-03-2016, 03:42 PM
DaveCh DaveCh is offline Grouping Data Windows 7 64bit Grouping Data Office 2013
Novice
Grouping Data
 
Join Date: May 2016
Posts: 7
DaveCh is on a distinguished road
Default Grouping Data

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
Reply With Quote
  #2  
Old 06-03-2016, 04:48 PM
jeffreybrown jeffreybrown is offline Grouping Data Windows Vista Grouping Data Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 06-06-2016, 07:49 AM
DaveCh DaveCh is offline Grouping Data Windows 7 64bit Grouping Data Office 2013
Novice
Grouping Data
 
Join Date: May 2016
Posts: 7
DaveCh is on a distinguished road
Default

Here is a portion of my data base:

RouteCOUNTIESCOUNTY CODES11020273435364449511Del Norte 253542Siskiyou2s533Modoc3254Humboldt428293031385Trinity04u296Shasta52681115222429387Lassen
Reply With Quote
  #4  
Old 06-06-2016, 08:02 AM
DaveCh DaveCh is offline Grouping Data Windows 7 64bit Grouping Data Office 2013
Novice
Grouping Data
 
Join Date: May 2016
Posts: 7
DaveCh is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 06-06-2016, 04:36 PM
jeffreybrown jeffreybrown is offline Grouping Data Windows Vista Grouping Data Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Reply With Quote
  #6  
Old 06-07-2016, 08:11 AM
DaveCh DaveCh is offline Grouping Data Windows 7 64bit Grouping Data Office 2013
Novice
Grouping Data
 
Join Date: May 2016
Posts: 7
DaveCh is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 06-07-2016, 08:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Grouping Data Windows 7 64bit Grouping Data Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please upload a workbook. Retyping data is not amusing :-(
__________________
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
Reply With Quote
  #8  
Old 06-07-2016, 09:41 AM
DaveCh DaveCh is offline Grouping Data Windows 7 64bit Grouping Data Office 2013
Novice
Grouping Data
 
Join Date: May 2016
Posts: 7
DaveCh is on a distinguished road
Default Grouping Data

Sorry, here we go!
Attached Files
File Type: xlsx Copy of Route_list.xlsx (14.8 KB, 10 views)
Reply With Quote
  #9  
Old 06-07-2016, 03:57 PM
jeffreybrown jeffreybrown is offline Grouping Data Windows Vista Grouping Data Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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?
Reply With Quote
  #10  
Old 06-08-2016, 07:30 AM
DaveCh DaveCh is offline Grouping Data Windows 7 64bit Grouping Data Office 2013
Novice
Grouping Data
 
Join Date: May 2016
Posts: 7
DaveCh is on a distinguished road
Default Regrouping Data

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
Reply With Quote
  #11  
Old 06-09-2016, 03:57 PM
jeffreybrown jeffreybrown is offline Grouping Data Windows Vista Grouping Data Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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

Quote:
2) Completely reverse the data arrangement; that is, have the routes fall into each corresponding county.
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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping Data Vba macro code for grouping the data based on 2 hours time dharani suresh Excel Programming 5 04-29-2014 03:25 AM
Grouping Data Pivot table grouping problem 2 tables need different grouping differentdrummer Excel 3 12-10-2013 01:19 AM
Grouping paragraphs h.ridinger Word 2 11-06-2013 10:42 AM
Grouping alamorchgirl PowerPoint 0 07-08-2011 02:14 AM
Grouping - expand all ejohns Excel 0 07-17-2009 07:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft