Sum two or three different values in a cell differentiated with an alphabet and a comma in a cell

[anne7808]

Hi,
I wanted the sum total of different nos. separated by a comma in a row of cells in the exact order with commas in another cell
and also the alphabets written along with the nos. just appear as the normal letter alongside the total number.

like = _V, _S, _CC

 

[anne7808]

This is incredible! It's working perfectly! thank you so so much!! I almost thought it would not be possible. Thanks again :D

Assuming that if any of your numbers are floating point numbers, then the decimal point is a dot and the decimal portion of the number never ends with trailing zeroes (that is, you never have a value like 12.30V, rather, it is always presented as 12.3V), then this UDF (user defined function) should work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function LetterSum(Rng As Range) As String
  Dim X As Long, Num As String, Letter As String, V As Variant, Parts() As String
  Parts = Split(Replace(Join(Application.Index(Rng.Value, 1, 0), ","), " ", ""), ",")
  With CreateObject("Scripting.Dictionary")
    For X = 0 To UBound(Parts)
      If Len(Parts(X)) Then
        Num = Val(Parts(X))
        Letter = Mid(Parts(X), Len(Num) + 1)
        .Item(Letter) = .Item(Letter) + Val(Num)
      End If
    Next
    For Each V In .keys
      LetterSum = LetterSum & ", " & .Item(V) & " " & V
    Next
  End With
  LetterSum = Mid(LetterSum, 3)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LetterSum just like it was a built-in Excel function. For example,

=LetterSum(B2:AF2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[anne7808]

....

 

[anne7808]

Assuming that if any of your numbers are floating point numbers, then the decimal point is a dot and the decimal portion of the number never ends with trailing zeroes (that is, you never have a value like 12.30V, rather, it is always presented as 12.3V), then this UDF (user defined function) should work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function LetterSum(Rng As Range) As String
  Dim X As Long, Num As String, Letter As String, V As Variant, Parts() As String
  Parts = Split(Replace(Join(Application.Index(Rng.Value, 1, 0), ","), " ", ""), ",")
  With CreateObject("Scripting.Dictionary")
    For X = 0 To UBound(Parts)
      If Len(Parts(X)) Then
        Num = Val(Parts(X))
        Letter = Mid(Parts(X), Len(Num) + 1)
        .Item(Letter) = .Item(Letter) + Val(Num)
      End If
    Next
    For Each V In .keys
      LetterSum = LetterSum & ", " & .Item(V) & " " & V
    Next
  End With
  LetterSum = Mid(LetterSum, 3)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Hello Sir, Thanks again for the VBA coding. It was a lifesaver!
I needed your help again regarding that. Could you tell me how to use the lettersum with other formulas. Would the existing coding work or would it need modifications?

I need it with a multiplying constant and also a subtraction formula based on the alphabet assigned to the numbers.

The subtraction formula in Purchases!AH4 is supposed to be an addition of all the numbers with constant alphabets (200V, 400S) in Report!C4 minus the amount paid which is mentioned from B5 to AF5

The Report!C4 contains constant price of ITEM V which is 200. So it multiplies the quantity with Price. I want to feed multiple item quantities with their price denoted by an alphabet constant and their sum total to be showed like 400V, 500ST .
but for the Purchases!AH4 I want the total price i.e. 900 to be used for calculations. I would feed the quantities like 2V, 2ST in cells B4 to AF4 in purchases sheet. and based on that the prices multiplied to the quantities would be showed in Report!C4 {for eg. 400V, 500ST) and then the sum total {900} to be used for calculation in Purchases!AH4.
I could add Additional Helping Cells if needed.

Please let me know if this is even possible. I am really a beginner and your help would mean a lot. Thank you so much

 

[Rick Rothstein]

I am having trouble visualizing what you want. Let's see if we can simplify the presentation so I can figure out what you want without me having to know or become familiar with your data layout. If I remember this thread correctly, you will have a result similar to this from the LetterSum UDF that I posted...

35 V, 15 S, 25 CC

Now, you have some numbers that you to use in a multiplication and subtraction. Can you show me the number or numbers (if more than one per letter value), tell me what each number should be used for (multiplying or subtracting), tell me which of these numbers applies to which letter value and then show me the final result you want.

 

[anne7808]

I am having trouble visualizing what you want. Let's see if we can simplify the presentation so I can figure out what you want without me having to know or become familiar with your data layout. If I remember this thread correctly, you will have a result similar to this from the LetterSum UDF that I posted...

35 V, 15 S, 25 CC

Now, you have some numbers that you to use in a multiplication and subtraction. Can you show me the number or numbers (if more than one per letter value), tell me what each number should be used for (multiplying or subtracting), tell me which of these numbers applies to which letter value and then show me the final result you want.

Okay so I need two results,

In Report!C4 -> For e.g., Multiplying by 200 for V, 100 for M, 500 for S (for multiplying with the quantity in purchases!AG4) Actually this data would by varying and I might have to add new prices for different different items (denoted by alphabets). Or change the price of items i.e. 200, 100 etc.

After that, I need the total of all the prices from Report!C4 i.e. (for eg.) 400 V, 200 M = 600.
And I need that total for the calculations of *PURCHASES!AH4* (subtraction of paid amount from total value). This calculation needs report!C4's total numeric value (no alphabets involved)

I hope you understood what I was trying to say. I couldn't explain without using my data layout.
thank you so much

 

[Rick Rothstein]

Alright, I think I see the overall concept, now lets get down to specifics... this is where I am having my troubles (you, of course, are familiar with your layout whereas I have no clue about any part of it, so you will have to be very specific in telling me where things are located and how I would know the locations for them). Show me an example of what my UDF outputs, tell me what cell it is in, then tell me where the multipliers for each of its values are located and tell me how I would know which letter's value gets multiplied by that number (specific colum or row for each particular letter maybe, or possibly a specific header text, something else?), finally, show me what output you expect and tell me where that output should appear.

 

[anne7808]

Alright, I think I see the overall concept, now lets get down to specifics... this is where I am having my troubles (you, of course, are familiar with your layout whereas I have no clue about any part of it, so you will have to be very specific in telling me where things are located and how I would know the locations for them). Show me an example of what my UDF outputs, tell me what cell it is in, then tell me where the multipliers for each of its values are located and tell me how I would know which letter's value gets multiplied by that number (specific colum or row for each particular letter maybe, or possibly a specific header text, something else?), finally, show me what output you expect and tell me where that output should appear.

The letter's value which gets multiplied by a number is not fixed as of now. if you could put random numbers and teach me how to edit it later with the actual values.

I want the multiplied total (i.e. 400 V + 200 M = 600) in Report!C4,
so I can use that total numeric value for calculations in Purchases!AH4

The data to be multiplied that is (for eg. 2V, 1M) is in the cell Purchases!AG4

and I can make a separate row or column if needed for individual letters after the 25th Row or F column in 'Report' worksheet.

 

[Rick Rothstein]

The letter's value which gets multiplied by a number is not fixed as of now. if you could put random numbers and teach me how to edit it later with the actual values.

Alright, I can probably come up with something that you can easily work with and/or change later on, but I am unclear about one thing. In your example you said you got this...

400 V, 200 M

from this....

2 V, 1 M

(there are spaces between the numbers and letters, correct...your example did not show them?) So in that example, each number was multiplied by the same amount (200)... will that always be the case or could some of the numbers in front of a letter be multiplied by different numbers from the rest (that is, say, V numbers are multiplied by 150 and M numbers by 85)?

 

[anne7808]

No, the numbers would be different for each alphabet or combos. for eg. V = 200, SS = 300, M= 100. And the spaces could be added, not necessary to do it without the spaces.

 

[anne7808]

Alright, I can probably come up with something that you can easily work with and/or change later on, but I am unclear about one thing. In your example you said you got this...

400 V, 200 M

from this....

2 V, 1 M

(there are spaces between the numbers and letters, correct...your example did not show them?) So in that example, each number was multiplied by the same amount (200)... will that always be the case or could some of the numbers in front of a letter be multiplied by different numbers from the rest (that is, say, V numbers are multiplied by 150 and M numbers by 85)?

No, the numbers would be different for each alphabet or combos. for eg. V = 200, SS = 300, M= 100. And the spaces could be added, not necessary to do it without the spaces.