Need help with formula Excel 2007

[Reimer]

I could use some help, if anyone has time.
I have a workbook that is used to track meter readings.
I have attached one sheet of the workbook I an requesting help on.

There is a sheet for each city in the full workbook.
There is a summary sheet where I use VBA to populate C3:CZ236 -with:
0 if there is no activity for a meter (may be inactive)
1 if meter flow is added
-1 if meter flow is subtracted

The Cities are listed in C2:CZ2.
The Meters are listed in A3 thru A236.
Column B has formulas that show the net effect for each Meter.
There are 4 possible entries in column B:
0's -means the meter was not used (Col C thru CZ contained 0's)
0 -means the flow is added to 1 city and subtracted from another city (+1 and -1 or -1 and +1) -net 0
1 -means there is one city where this flow is added +1 net 1
2 -means there are two cities where the flow is added (+1 and +1) net 2

In Column DD I have a VLOOKUP that returns how each meter should be used and by what city.

I would like to have formulas in column DL that would concatenate the information on a row and combine it with the City found in Row 2.
I have examples in a few of the first cells in column DL.

I need a formula (in DL) that will let me compare with the formula in DD to see if they return the same thing.

Currently I use a macro that hides all the city columns that contain 0 for one meter at a time. This was fine in 2003, but in 2007 it takes much longer to run the macro.

Just heading out for the day, so I will not be able to respond until tomorrow.

If after reading this it is decided that it is not worth investing time, I certainly understand!

Thanks!

[HansV]

I'd use a VBA function. Copy the following into a standard module:

Code: Select all

Function GetUse(rng As Range) As String
  Dim oCell As Range
  Dim f As Boolean
  For Each oCell In rng
    Select Case oCell.Value
      Case 1
        GetUse = GetUse & ", +1 " & Cells(2, oCell.Column)
        f = True
      Case -1
        GetUse = GetUse & ", -1 " & Cells(2, oCell.Column)
        f = True
    End Select
  Next oCell
  If f = False Then
    GetUse = "0's"
  ElseIf GetUse <> "" Then
    GetUse = Mid(GetUse, 3)
  End If
End Function

In cell DL3, enter this formula:

=GetUse(C3:CZ3)

Fill down as far as needed.

Notes:

1) If you'd like the function to be recalculated whenever a value in columns C:CZ changes, add the following line at the beginning of the function:

Application.Volatile

This may have a negative effect on performance.

2) Don't forget to save the workbook as a macro-enabled workbook (.xlsm).

[Reimer]

Hans,

Thanks! I will stick the function in with the rest of the functions in that workbook, and give it a shot.
I am curiuos to see how it grabs the name of the city.

Thank you very much.
I will post back the results.

[HansV]

The code lets oCell loop though the cells in a row. If it encounters -1 or 1, it looks at Cells(2, oCell.Column); this is the cell in row 2 in the same column as oCell, i.e. the cell containing the name of the city.

[Reimer]

Hans,

You are amazing! Thank you very much. It works Great.
Thank you also for the explaination. I read the email last night on my cell phone and could not see where it would get the city.
I was very eager to get to word and try it.
You make many peoples jobs easier. THANK YOU

Chuck