2

So, the situation is this, I want to know if there's a way to convert the sum of a range of cells into the sum resulted from adding each cell, separately, as shown in the title. For instance, say one has this computation in cell E1, E1 = SUM(A1:D1) and wants to split it into sum of the separate component cells, like in E1 = A1 + B1 + C1 + D1.

Also, if it would be great if someone could indicate how to do this for a range of cells, like in the case: E1 = SUM (A1:D2) being converted into E1 = A1 + B1 + C1 + D1 + A2 + B2 + C2 + D2.

EDIT: Someone suggested that I use VBA, and I thought about that too. If anyone has some suggestions on the matter, it would be greatly appreciated (I'm not very good at programming in VBA, although I know the basics and I'll give it a try on my own.

Dave
  • 25,513
streamline
  • 121

2 Answers2

1

Although you've tagged this with Worksheet function you talk about using VBa in the quesiton. This VBa does both of the examples you gave

Option Explicit
Sub EeekPirates()

Dim formula As String
formula = Range("B4").formula

Dim split1() As String
split1 = Split(formula, "(")

Dim temp As String
temp = Replace(split1(1), ")", "")

Dim splitty() As String
splitty = Split(temp, ":")

Dim firstCol As Integer
firstCol = AscW(Left(splitty(0), 1))

Dim secondCol As Integer
secondCol = AscW(Left(splitty(1), 1))

Dim firstRow As Integer
firstRow = Right(splitty(0), 1)

Dim secondRow As Integer
secondRow = Right(splitty(1), 1)

Range("B5").Value = ""   ' this could be updated to `B4 = ` 
Dim i As Integer
Dim j As Integer

For j = firstRow To secondRow
    For i = firstCol To secondCol
        Range("B5").Value = Range("B5").Value & Chr(i) & j & "+"
    Next i
Next j

Dim length As Integer
length = Len(Range("B5").Value) - 1
Range("B5").Value = Left(Range("B5").Value, length)

End Sub

Just remeber there is no undo, so take a back up first.

How do I add VBA in MS Office?

Example with A1:D1

enter image description here

Example with A1:D2

enter image description here

As per the comments in the code, if you update from

Range("B5").Value = ""

to

Range("B5").Value = "B4 = "

You will end up with (in B5)

B4 = A1 + B1 + C1 + D1
Dave
  • 25,513
1

Minimal working example with VBA function unroll(), which takes a reference to a cell with a single function (like sum,count, min) and unrolls its argument (a list of ranges) as a list of single cells.

Option Explicit

Function rangeText(s As String) As String
Dim i As Integer, j As Integer
i = Excel.WorksheetFunction.Find("(", s)
j = Excel.WorksheetFunction.Find(")", s)
rangeText = Mid(s, i + 1, j - i - 1)
End Function

Function rangeToList(s As String)
Dim rg As Range: Set rg = Range(s)
Dim i, j As Integer: Dim c As String
For j = 0 To rg.Rows.Count - 1
  For i = 0 To rg.Columns.Count - 1
    c = c + IIf(c <> "", ",", "") + Chr(64 + rg.Column() + i) + Format(rg.Row() + j)
  Next i
Next j
rangeToList = c
End Function

Function unroll(x As Range) As String
  Dim s As String: Dim i, j As Integer: Dim list() As String
  If Not x.HasFormula Then
   s = "Not a formula"
  Else
  s = rangeText(x.Formula)
  list = Split(s, ",")
  s = ""
  For i = 0 To UBound(list)
    s = s + IIf(i > 0, ",", "") + rangeToList(list(i))
  Next i
End If
unroll = s
End Function

enter image description here

Note: as a minimal example, it does not handle two-letters column references correctly.

* Edit *

Added Function ColumnNoToName to handle cell references with columns > 26.

enter image description here

Option Explicit

Function rangeText(s As String) As String
Dim i As Integer, j As Integer
i = Excel.WorksheetFunction.Find("(", s)
j = Excel.WorksheetFunction.Find(")", s)
rangeText = Mid(s, i + 1, j - i - 1)
End Function

Function ColumnNoToName(colNo As Integer) As String
  Dim lo, hi As Integer: Dim s As String
  lo = (colNo - 1) Mod 26
  If colNo > 26 Then
    hi = (colNo - 1 - lo) \ 26
    s = Chr(64 + hi)
  End If
  s = s + Chr(64 + lo + 1)
  ColumnNoToName = s
End Function

Function rangeToList(s As String)
Dim rg As Range: Set rg = Range(s)
Dim i, j As Integer: Dim c As String
For j = 0 To rg.Rows.Count - 1
  For i = 0 To rg.Columns.Count - 1
    c = c + IIf(c <> "", ",", "") _
      + ColumnNoToName(rg.Column() + i) _
      + Format(rg.Row() + j)
  Next i
Next j
rangeToList = c
End Function

Function unroll(x As Range) As String
  Dim s As String: Dim i, j As Integer: Dim list() As String
  If Not x.HasFormula Then
   s = "Not a formula"
  Else
  s = rangeText(x.Formula)
  list = Split(s, ",")
  s = ""
  For i = 0 To UBound(list)
    s = s + IIf(i > 0, ",", "") + rangeToList(list(i))
  Next i
End If
unroll = s
End Function

Function cellFormula(x As Range) As String
  cellFormula = x.Formula
End Function
g.kov
  • 889