Sum & difference between multiple values in a single cell

iuliandonici · #1 04-12-2011, 10:42 PM

Hi,

I have given a search on this forum for this matter but I didn't find any solution.

I have 4 coloumns (IN, OUT, IN+OUT, IN-OUT) and N rows. I need to know how can I be able to make a SUM & DIFFERENCE between the IN and OUT values. These values, both in IN and OUT, come into a single cell, separated by ";". Now, you'll probably tell me it's wrong to have multiple values in a single cell but I can't work any other way because the NUMBER of these VALUES is not constant in order to be able to separate them in each coloumn.

Also, the NUMBER of these multiple values in a single cell can be up to 50, 50 values. And each value can have up to 7 digits.

I know little about VBA programming but I'm willing to do some damage.
I would really appreciate it.
Thank you.

macropod · #2 04-13-2011, 12:18 AM

Hi iuliandonici,

So what makes for a IN+OUT value or an IN-OUT value? Is there a reason for the 'IN' values having spaces after the semi-colons, whilst the 'OUT' values don't have spaces? What should be the results for the sample you gave? Some more data with different numbers of elements and worked results would be useful too.

iuliandonici · #3 04-13-2011, 12:26 AM

Thank you for your reply.

I ALSO thought about using "Text to coloumns" too but it's not efficient for my the work I'm trying to do.

Also, I have revised my SAMPLE and tried to make it more specific:
- the values in each cell from IN/OUT/IN+OUT/IN-OUT coloumns should be separeted one from another by ";" and a BLANK SPACE ("34; 45; etc.).
- these values, will never be NEGATIVE;
- here's an example of how IN+OUT would like to be calculated:
"=SUM(24+23); SUM(33+12); SUM(45+34); etc."

I'm really sorry if I confused someone, these are some things I just realised. I have just attached a revised SAMPLE.

macropod · #4 04-13-2011, 01:55 AM

Hi iuliandonici,

Try:

Code:

Sub InOut()
Application.ScreenUpdating = False
Dim StrIn As String, StrOut As String, i As Long
Dim StrSub As String, StrAdd As String, j As Long
With ActiveSheet()
  For i = 7 To .Range("C" & .Rows.Count).End(xlUp).Row
    StrAdd = ""
    StrSub = ""
    StrIn = Trim(Replace(.Cells(i, 3).Value, ";", ""))
    StrOut = Trim(Replace(.Cells(i, 4).Value, ";", ""))
    If UBound(Split(StrIn, " ")) = UBound(Split(StrOut, " ")) Then
      For j = 0 To UBound(Split(StrIn, " "))
        StrAdd = StrAdd & CLng(Split(StrIn, " ")(j)) + CLng(Split(StrOut, " ")(j)) & " "
        StrSub = StrSub & CLng(Split(StrIn, " ")(j)) - CLng(Split(StrOut, " ")(j)) & " "
      Next
      StrAdd = Replace(StrAdd, " ", "; ")
      StrSub = Replace(StrSub, " ", "; ")
    Else
      StrAdd = "IN/OUT item counts differ"
      StrSub = "IN/OUT item counts differ"
    End If
    .Cells(i, 5).Value = StrAdd
    .Cells(i, 6).Value = StrSub
  Next
End With
Application.ScreenUpdating = True
End Sub

iuliandonici · #5 04-13-2011, 09:27 PM

It works! Thank you!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying Multiple tables from excel into a single word document	dineshtgs	Word Tables	1	04-07-2011 01:27 AM
moving data from single to multiple columns?	mzimmers	Excel	3	08-23-2010 08:20 AM
How can I write the following (whole) formula in a single cell?	Learner7	Excel	1	07-19-2010 10:06 AM
Pasting multiple photos - one photo per cell in a single oepration	mccruise	Word	0	04-07-2010 04:13 AM
multiple colums under a single column	RobotChicken	Excel	3	01-06-2010 09:17 AM