Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
I have a range of cells filled with comma separated values e.g.
8,2,0,0,1,0,0 3,0,7,1,0,0,2 5,0,3,0,2,0,2 I'd like to be able to total the values: 16,2,10,1,3,0,4 Right now I use a function that adds the 1st argument of each individual cell (I have to identify each one) and the then a second function for the next argument and so on... (too much typing of ab123 etc. I'd really like to simplify the work and be able to have one function work for any range of cells. The reason for the comma separated values was an attempt to make the data easier to read (fewer columns). thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
here's a UDF, worked on your sample...a string.."16,2,10,1,3,0,4"
Option Explicit Public Function ColumnsSum(Source As Range) As String Dim Result As String Dim aResult() As Variant Dim arr() As Long Dim index As Long Dim rowindex As Long Dim ThisRow As Range Dim splitter As Variant Dim maxdepth As Long Dim maxwidth As Long maxdepth = Source.Rows.Count For Each ThisRow In Source.Rows splitter = Split(ThisRow.Value, ",") rowindex = rowindex + 1 maxwidth = UBound(splitter, 1) ReDim Preserve arr(1 To maxdepth, 0 To maxwidth) For index = 0 To maxwidth arr(rowindex, index) = splitter(index) Next Next ReDim aResult(0 To maxwidth) For rowindex = 1 To maxdepth For index = 0 To maxwidth aResult(index) = aResult(index) + arr(rowindex, index) Next Next Result = Join(aResult, ",") ColumnsSum = Result End Function NOTE if you wnat the output as an array, remove Result = Join(aResult, ",") change ColumnsSum = Result to ColumnsSum = aResult then change the function return type to Variant "bthoron" wrote in message ups.com... I have a range of cells filled with comma separated values e.g. 8,2,0,0,1,0,0 3,0,7,1,0,0,2 5,0,3,0,2,0,2 I'd like to be able to total the values: 16,2,10,1,3,0,4 Right now I use a function that adds the 1st argument of each individual cell (I have to identify each one) and the then a second function for the next argument and so on... (too much typing of ab123 etc. I'd really like to simplify the work and be able to have one function work for any range of cells. The reason for the comma separated values was an attempt to make the data easier to read (fewer columns). thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
Thank you this worked great!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
Here is a formula solution
=SUMPRODUCT(--MID($A$1:$A$3,FIND("~",SUBSTITUTE(","&$A$1:$A$3&", ",",","~",RO W(A1))),FIND("~",SUBSTITUTE(","&$A$1:$A$3&",",",", "~",ROW(A2)))-FIND("~",SUB STITUTE(","&$A$1:$A$3&",",",","~",ROW(A1)))-1)) This will get the first digits, copy down a row to get the second, etc. -- HTH RP (remove nothere from the email address if mailing direct) "bthoron" wrote in message ups.com... I have a range of cells filled with comma separated values e.g. 8,2,0,0,1,0,0 3,0,7,1,0,0,2 5,0,3,0,2,0,2 I'd like to be able to total the values: 16,2,10,1,3,0,4 Right now I use a function that adds the 1st argument of each individual cell (I have to identify each one) and the then a second function for the next argument and so on... (too much typing of ab123 etc. I'd really like to simplify the work and be able to have one function work for any range of cells. The reason for the comma separated values was an attempt to make the data easier to read (fewer columns). thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
Bob- this didn't work out very well for me, I think I got rid of the
hyphens, but all I get is #value. The other thing I note about the formula method is that it limits the number of csv to the number in the formula. The code above could handle any number of csv (provided the entire range had the same) I'm curious about --MID and Substitute how are these used typically? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
You shouldn't get rid of the hyphens, they are intrinsic to the solution.
The number of CSV is dependant on the range size, it would be simple to define a named range, and use that name in the formula. I don't know about typically, but here SUBSTITUTE is used to replace an instance of the comma with a ~ for uniqueness which FIND will locate, thereby getting the comma's position. This is passed to the MID function to extract the value between two commas. SUMPRODUCT Then sums across the range that location. The -- is used for coercing Booleans to a number. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for details. I don't know why it is not working for you. I can assure you that it does work. I created test data as presented by you, and got the results you gave, albeit in separate cells. -- HTH RP (remove nothere from the email address if mailing direct) "bthoron" wrote in message oups.com... Bob- this didn't work out very well for me, I think I got rid of the hyphens, but all I get is #value. The other thing I note about the formula method is that it limits the number of csv to the number in the formula. The code above could handle any number of csv (provided the entire range had the same) I'm curious about --MID and Substitute how are these used typically? Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
I'm quite certain it's a personal problem on my part- I'll try again.
Thanks for the additional information. B |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing comma separated values in a range
Just try to explain what it does that you don't want or vice versa/
-- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... You shouldn't get rid of the hyphens, they are intrinsic to the solution. The number of CSV is dependant on the range size, it would be simple to define a named range, and use that name in the formula. I don't know about typically, but here SUBSTITUTE is used to replace an instance of the comma with a ~ for uniqueness which FIND will locate, thereby getting the comma's position. This is passed to the MID function to extract the value between two commas. SUMPRODUCT Then sums across the range that location. The -- is used for coercing Booleans to a number. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for details. I don't know why it is not working for you. I can assure you that it does work. I created test data as presented by you, and got the results you gave, albeit in separate cells. -- HTH RP (remove nothere from the email address if mailing direct) "bthoron" wrote in message oups.com... Bob- this didn't work out very well for me, I think I got rid of the hyphens, but all I get is #value. The other thing I note about the formula method is that it limits the number of csv to the number in the formula. The code above could handle any number of csv (provided the entire range had the same) I'm curious about --MID and Substitute how are these used typically? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to I create a comma separated values file | Excel Discussion (Misc queries) | |||
How do I convert a column of numbers into comma separated values . | Excel Worksheet Functions | |||
Fixing Comma Separated Values (.csv) | Excel Discussion (Misc queries) | |||
how could I import a text file with comma separated values into ex | Excel Worksheet Functions | |||
CSV (Comma Separated Values) delimiter ? | Excel Programming |