# 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.
###### Who is Participating?

Commented:
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

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

Commented:
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

Commented:
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 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

Author Commented:
is this at all possible?
0

Commented:
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:

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

Commented:
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 Commented:
exactly.
0

Author Commented:
i'll mess around with your code a little bit and see if i can get anywhere.
0

Author 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

Author Commented:
i doubled the pts btw.
0

Author 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

Commented:
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 Commented:
ahh, yes! am i able to revise my posting that has my email address so i can remove it?
0

Author 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

Author Commented:
what do you recommend as a good resource?
0

Commented:
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:
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 Commented:
Gracias.
0

Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.