Energy Star Data

[MarkRA]

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

 

[baitmaster]

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

 

[Norie]

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?

 

[MarkRA]

I'll be deciding, so arbitrarily deciding PA is Northeast instead of "Mid-Atlantic", etc.

 

[Norie]

Mark

Do you just want to make a list of the states in each region, separated in some way eg by a comma?

 

[MarkRA]

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.

 

[Norie]

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.

 

[MarkRA]

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!

 

[baitmaster]

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!

 

[Norie]

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'.

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.