[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Combining 3 columns to produce 1. Creating buckets...adding data values

Hello Experts,

I have 10 columns and I need to create buckets.  Column headings, 0 year, 1 year, 2 year, 3 year, 4 year, 5 year, 7 year, 10 year, 20 year, and 30 year.  I need to group the columns like: 0-3, 4-6, 7-10, & 10+ <-new column  headings.  Therefore, I need to sum up, the first 4 columns, (0, 1, 2, 3) and display them in the new column, "0-3".

Here is what I  have for the first 4 columns, although it's not working correctly:

   Dim c As Range
   Dim d As Double
   Dim b As Integer
   d = 0
   b = 3
   
   With Worksheets("report")
        .Range("C2").value = "0 - 3"
        For Each c In .Range("C" + b + ":F" + b).Cells
             d = d + CDbl(c.value)
             .Range("C" & b).value = d
             b = b + 1
        Next c
   End With

in my excel sheet, the columns begin at column C and the data begins at row 3.  

Any help would be greatly appreciated.
0
efarhat
Asked:
efarhat
  • 12
  • 8
1 Solution
 
mvidasCommented:
Hi efarhat,

Keeping the general theory in which your code works (so you understand how it works), I've modified it to loop through what you have. It should work if you change your With portion to:

   With Worksheets("report")
      .Range("C2").FormulaR1C1 = "0 - 3"
      Do Until b > .Range("C65536").End(xlUp).Row
        d = 0
        For Each c In .Range("C" + b + ":F" + b).Cells
             d = d + CDbl(c.Value)
        Next c
        .Range("C" & b).Value = d
        b = b + 1
      Loop
   End With

Also, I would recommend Dim'ming b as Long rather than Integer (if your worksheets are big)

Another way to do it would be using:

Sub AnotherWayToDoIt()
   Dim c As Range
   Dim c2 As Range

   With Worksheets("report")
    .Range("C2").FormulaR1C1 = "0 - 3"
    For Each c In Range(.Range("C3"), .Range("C65536").End(xlUp))
     Set c2 = Range(c, c.Offset(0, 3))
     c = Application.WorksheetFunction.Sum(c2)
    Next c
   End With
End Sub

Just a thought
Matt
0
 
mvidasCommented:
I always like shorter code, so it could also be done using:

Sub AnotherWayToDoIt()
 Dim c As Range
 Sheets("report").Range("C2").FormulaR1C1 = "0 - 3"
 For Each c In Range(Sheets("report").Range("C3"), Sheets("report").Range("C65536").End(xlUp))
  c = Application.WorksheetFunction.Sum(Range(c, c.Offset(0, 3)))
 Next c
End Sub
0
 
efarhatAuthor Commented:
What if I want to do it from select columns?  Also, clear the contents from those columns, so it will only display the new columns?

let me explain in more detail exactly what's going on.  I am currently display all these columns (20).
10 YTM columns and 10 IR columns, sequentially, starting with column C.
like this:
                       0    |    C     l     D    |    E       l     F     |    ...
merged cells-> 1    |        0 - 3        |          4 - 6         |    ...
                       2    | YTM    l    IR    |  YTM    l     IR    |    ...
                       3    | data    | data   | data     |  data   |
                       4    | data    | data   | data     |  data   |
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
efarhatAuthor Commented:
is this at all possible?
0
 
mvidasCommented:
Kind of, although you may still have to do a little bit of manual work.  

The following sub will sum up however many columns you have selected (starting at row 3), and place the sum in the leftmost column.  As an example, this will work if you have columns C,E,G,I,K selected, or if you have X:Z selected, whatever columns you have it will sum them up and put them in the left most column selected.  It will then delete all selected columns except for the leftmost column selected. You could do this for each of your ranges, both in YTM and IR.  The manual work you will have to do is put the IR columns next to the YTM, and merge the heading cells.  
NOTE: the macro below will not put the "0 - 3" heading in, as I'm under the impression you're going to be doing this to more than just 0-3.
If I have not understood fully what you're asking, please let me know, and explain what I'm missing.  Here's the sub:

Sub SumNonAdjacentSelectColumns()
 Dim r As Long, fC As Integer, cols As Range
 If Selection.Columns.Count = 1 Then Exit Sub
 fC = Selection.Column
 For r = 3 To Cells(65536, fC).End(xlUp).Row '3 is the first row that contains data to sum
  Cells(r, fC) = Application.WorksheetFunction.Sum(Intersect(Rows(r), Selection.Cells))
 Next r
 For Each cols In Selection.Areas
  If Intersect(Columns(fC), cols) Is Nothing Then
   cols.EntireColumn.Delete
  Else
   If cols.Columns.Count > 1 Then
    For r = cols.Column To cols.Column + cols.Columns.Count - 1
     If r <> fC Then Columns(r).EntireColumn.Delete
    Next r
   End If
  End If
 Next cols
 Intersect(Columns(fC), Selection.Cells).Select
End Sub
0
 
efarhatAuthor Commented:
mvidas,

i'm not sure what the code you have posted is doing.    I'm trying to understand it so I can modify it to accompany worksheet layout.  

may i email a simplified version of what it's doing and what i am trying to accomplish? i will double the points if you like.  I really appreciate the help.  As you can see, i'm not looking strictly looking for code but more of an explanation on how to go about doing this.  Realizing though, having code does explain a lot.  Many thanks.

email me at <email removed - Bingie EE PE> to prevent from posting your email addy.
0
 
mvidasCommented:
Unfortunately EE frowns upon emailing files like this, as it takes away changes other people might have of helping.
It sounds like you have 10 columns of YTM then 10 columns of IR, right? And you want to take the 20 columns you have and make them into 8, 2 for each of the 4 time spans?    I could write code that would do that, but if you want to understand how it works (which I always need to), I could show you how to modify the first piece of code I wrote (that used the theory in your code) to suit your needs.  But it sounds like I'm still missing the point a bit, let me think about this and see what will work best (I need to step away from the computer for a few minutes anyways)
0
 
efarhatAuthor Commented:
exactly.  
0
 
efarhatAuthor Commented:
i'll mess around with your code a little bit and see if i can get anywhere.
0
 
efarhatAuthor Commented:
mvidas,

maybe this may help some.  i tried explaining this before but not sure i did a good job of that.  pictures say 1000 words:

YTM  YTM  YTM  YTM  YTM  .... | |  IR   IR   IR   IR ...
0Y     1Y      2Y     3Y    4Y    .... | |  0Y  1Y   2Y  3Y ..

these are displayed in order and I need to group them. One YTM and one IR for the bucket 0-3 and so on.   Does that help?      

1000 thank yous!
 
0
 
efarhatAuthor Commented:
i doubled the pts btw.
0
 
mvidasCommented:
Sorry for the delay.  Doubling the points wasn't necessary, but I do appreciate the gesture.  Here is a new subroutine, following a lot of the same ideas you had for your original, and should be very easy to modify to suit your needs, in case the way I saw it wasn't the way it is.  As far as specifics, here's how I saw it:

source data:
YTM columns are C-L, with years 0,1,2,3,4,5,7,10,20,30
IR columns are M-V, same year breakdown

output:
0-3 goes in columns C:D, 4-6 to E:F, 7-10 to G:H, and 10+ in I:J


Sub AnotherWayToDoIt()
 Dim Rw As Long, Lr As Long, Sh As Worksheet
 
 'Create a worksheet object for easier referencing
 Set Sh = Sheets("report") 'We can now use Sh instead of Sheets("report")
 
 'Last row of data
 Lr = Sh.Range("C65536").End(xlUp).Row
 
 'Column Headings
 Sh.Range("C1:J2").NumberFormat = "@"
 Sh.Range("C1").FormulaR1C1 = "0 - 3"
 Sh.Range("E1").FormulaR1C1 = "4 - 6"
 Sh.Range("G1").FormulaR1C1 = "7 - 10"
 Sh.Range("I1").FormulaR1C1 = "10 +"
 Sh.Range("C2,E2,G2,I2").FormulaR1C1 = "YTM"
 Sh.Range("D2,F2,H2,J2").FormulaR1C1 = "IR"
 Sh.Range("C1:D1,E1:F1,G1:H1,I1:J1").MergeCells = True
 Sh.Range("C1:J2").HorizontalAlignment = xlCenter
 
 'Loop through each row of data
 For Rw = 3 To Lr
  Sh.Cells(Rw, 3) = Application.WorksheetFunction.Sum(Sh.Range("C" & Rw & ":F" & Rw)) '0-3 YTM
  Sh.Cells(Rw, 4) = Application.WorksheetFunction.Sum(Sh.Range("M" & Rw & ":P" & Rw)) '0-3 IR
  Sh.Cells(Rw, 5) = Application.WorksheetFunction.Sum(Sh.Range("G" & Rw & ":H" & Rw)) '4-6 YTM
  Sh.Cells(Rw, 6) = Application.WorksheetFunction.Sum(Sh.Range("Q" & Rw & ":R" & Rw)) '4-6 IR
  Sh.Cells(Rw, 7) = Application.WorksheetFunction.Sum(Sh.Range("I" & Rw & ":J" & Rw)) '7-10 YTM
  Sh.Cells(Rw, 8) = Application.WorksheetFunction.Sum(Sh.Range("S" & Rw & ":T" & Rw)) '7-10 IR
  Sh.Cells(Rw, 9) = Application.WorksheetFunction.Sum(Sh.Range("K" & Rw & ":L" & Rw)) '10+ YTM
  Sh.Cells(Rw, 10) = Application.WorksheetFunction.Sum(Sh.Range("U" & Rw & ":V" & Rw)) '10+ IR
 Next Rw
 
 'Clean up remaining columns
 Range("K:L,M:V").Delete
 
End Sub


Out of curiosity, does your gmail account still have any invites? I have an account too, and I've only been given 1 invite and that was about 2 months ago.  I'm just curious if its a system thing, or a personal thing.  Also, I wouldn't ever recommend putting your email address on a question, as there are many many crawlers going through.  You can easily put them in your member profile (as mine is), and just say "My email is in my profile" if you ever do need to give it out.  Not that it really matters, I'm just trying to prevent you from receiving unwanted mail is all.

Hope the sub helps, and let me know if you have any questions!
0
 
efarhatAuthor Commented:
Excellent!  That's worked wonderfully!  Thank you.

Quck question: do you know how to define ranges in excel? not using vba, through excel menu?
0
 
mvidasCommented:
As in a named range?

Sure, there are two ways to do it, both assume the range you want to name is already selected.

First way: click in the white dropdown box to the left of your formula bar, type in the range name you want, and press enter.

Second way: Go to Insert, then Name, then Define.  Type the name you want in the top box, and click Add.

Should be all set!
0
 
efarhatAuthor Commented:
ahh, yes! am i able to revise my posting that has my email address so i can remove it?
0
 
efarhatAuthor Commented:
i thought i would be able to find it on my own but didn't see it.  can i define named ranges in code?
0
 
efarhatAuthor Commented:
what do you recommend as a good resource?
0
 
mvidasCommented:
You can't edit the posting yourself, but if you send an email to the Page editor, they can edit it for you.  The page editor is always listed on the lefthand side of the page, near the top.  In VB the page editor is Bingie and his email address is on his profile ( http://www.experts-exchange.com/M_1151875.html ).  Send him an email with your member name, a link to the question, and what you want to do.  For the question link, use
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21088546.html#11767351
As it links directly to that comment.

To define named ranges in code, use syntax like:
  ActiveWorkbook.Names.add Name:="TheName", RefersTo:="=Sheet1!$A$1:$C$3"
Remember to use the $ symbols too, otherwise the RefersTo is only in reference to the active cell at the time.

As far as resources go, there are many good ones out there.  I still think EE is the best, as pretty much any question that could be asked has been asked.

First, I'd say a question like this should go in the Excel Area rather than the VB area:
http://www.experts-exchange.com/Applications/MS_Office/Excel/

A great resource is John Walkenbach's page, he is a true Excel guru:
http://www.j-walk.com/ss/excel/

In regards to sending email from excel, my favorite site is:
http://www.rondebruin.nl/sendmail.htm

The email address in my profile is from another great VBA resource site.

Google is another great one, especially when used in conjunction with EE here.  In google, enter your search like:
  site:www.experts-exchange.com excel named ranges
That restricts the pages to be EE pages, any you'll almost always find what you're looking for that way.
0
 
efarhatAuthor Commented:
Gracias.
0
 
mvidasCommented:
De nada
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now