Energy Star Data

MarkRA

New Member
Joined
Jun 27, 2012
Messages
6
Hi folks, I'm a university student interning in the real estate industry for the summer and am working with some Energy Star (ES) data as one of my assigned projects.

I'm looking to create a macro to prepare data downloaded from ES into a usable format and produce several charts, including year-over-year comparisons of energy and water consumption and greenhouse gas emissions.

The first problem I'm having is generating a region column based on a state name column. I'd like to take the ES-reported state names - which are fully spelled out as such "California" "District of Columbia (D.C.)" etc - and assign them to a particular region of my choosing (New England states + NY, NJ, PA, DE = Northeast) etc.

I was thinking about using a very long nested string function for this, but I'm sure there must be an easier way!

Thanks in advance
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
welcome to the board

Vlookup is probably your best bet for this. Create a lookup table, then use VLookup to pull the right data in to your data set
 
Upvote 0
What criteria/logic would you use for this?

Is there data that indicates which state is in which region or is it decided by you?
 
Upvote 0
I'll be deciding, so arbitrarily deciding PA is Northeast instead of "Mid-Atlantic", etc.
 
Upvote 0
Mark

Do you just want to make a list of the states in each region, separated in some way eg by a comma?
 
Upvote 0
What I'm ultimately looking to do by this is to create relevant comparisons for energy expenditure - for example the western states have significantly different weather profiles than the northeastern states and, as such, energy and water consumption measurements between the two aren't relevant.

I'm looking to use this region distinction to ultimately create charts detailing year over year comparisons, by region.
 
Upvote 0
I think you need to simply create a list of state/region, which to be honest would probably be easiest to do manually.

Mind you, I suppose you could copy such a list from a website.

Then in the data insert a new column and enter the formula, ie VLOOKUP, that baitmaster suggested for each row.

That could be done with code.
 
Last edited:
Upvote 0
Great - thanks guys, I managed to get the code for implementing the table anyway by having macro record on while manually inputting and arranging the state/region combinations. I will start work on the vlookup references shortly.

I'm trying to make this tool as user-friendly as possible, as I'll be leaving after the summer is over and want there to be some continuity - so future users can just download the data, run a macro, and be done with it.

I'm starting to wonder why excel isn't taught as a full semester class in universities!
 
Upvote 0
I'm starting to wonder why excel isn't taught as a full semester class in universities!

Because then I wouldn't be able to show off in the office so easily, and people would realise just how little I really do. Don't EVER bring anyone's attention to this again!
 
Upvote 0
What part of it do you want to be handled by code?

Inserting a column with formulas is pretty straightforward.

For example, let's say your data is in A1:G345 on worksheet 'Data' with the states in column E. Also, the list is in A1:B51 on worksheet 'States'.
Code:
Dim LastRow As Long

With Worksheets("Data")   LastRow = .Range("A" & Rows.Count).End(xlUp).Row
   .Range("F1:F" & LastRow).Insert xlShiftToRight
   .Range("F1").Value = "Region"
   .Range("F2:F" & LastRow).Formula = "=VLOOKUP(E1, 'States'!$A$1:$B$51, 2,0)"
End With

You could add a button to the worksheet that calls a sub with this code.
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,290
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top