Solved

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

Posted on 2004-08-10
20
244 Views
Last Modified: 2010-05-02
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
Comment
Question by:efarhat
  • 12
  • 8
20 Comments
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
is this at all possible?
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
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
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
exactly.  
0
 

Author Comment

by:efarhat
Comment Utility
i'll mess around with your code a little bit and see if i can get anywhere.
0
 

Author Comment

by:efarhat
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:efarhat
Comment Utility
i doubled the pts btw.
0
 
LVL 35

Accepted Solution

by:
mvidas earned 150 total points
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
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
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
ahh, yes! am i able to revise my posting that has my email address so i can remove it?
0
 

Author Comment

by:efarhat
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
what do you recommend as a good resource?
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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
 

Author Comment

by:efarhat
Comment Utility
Gracias.
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
De nada
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now