How to do Excel SUM? Help

Help Studio
,
1

Hello, I’d like to perform a SUM function in Excel, but I’m facing a constraint. If there are 3 'P’s and 2 'B’s in a cell, it should be represented as ‘2B3P’. However, when I use CONCAT, it gives me something like ‘2BBB4PPP’. Can you please provide a solution? I’ll supplement my explanation with an image.
Thank you for your response!!

image

2

@22222222asas

Try this:

wddc
wddc777×919 23.3 KB

string.join("",dt.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.count & b.Key).toarray)

2 Likes
3

@22222222asas

try this

string.join("",dt.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.count & b.Key).toarray)
4

@Dilli_Reddy
Thank you for your response! Can you show me the code?

5
  1. use Build data table and give the output
    2.use assign activity

image

Sequence1.xaml (16.2 KB)

1 Like
6

@22222222asas

here code
in image

image
image1882×958 69.6 KB

Sequence1.xaml (9.2 KB)

here its xmal

1 Like
7

@Dilli_Reddy

Thank you for your answer! BUT Data SUM with 2 should result in 4 PLT, but it only shows 3 PLT. Can you please provide a solution for this issue?
image
image

8

@sai_gupta

Thank you for your answer! BUT Data SUM with 2 should result in 4 PLT, but it only shows 3 PLT. Can you please provide a solution for this issue?
image
image

9

Hi @22222222asas

In UiPath workflow using Invoke Code or Assign activities for the given VB.NET code:

  • Input DataTable: inputTable
  • Please change the column names
  • Grouping by Column1 and calculating the sum of Column2 for each group
  • Sorting the result in ascending order based on the sum of Column2
  • Concatenating the sorted results
Dim groupedData = inputTable.AsEnumerable().
                  GroupBy(Function(row) row.Field(Of String)("Column1")).
                  Select(Function(group) New With {
                      .Key = group.Key,
                      .SumColumn2 = group.Sum(Function(row) row.Field(Of Integer)("Column2"))
                  })

Dim sortedData = groupedData.OrderBy(Function(entry) entry.SumColumn2)

Dim result As String = String.Join("", sortedData.Select(Function(entry) $"{entry.SumColumn2}{entry.Key}"))

Sample file is here:
Main.xaml (10.3 KB)

1 Like
10

@aanandsanraj
Thank you so much for the response. I’m truly sorry to bother you, but could you please also explain how to do this using READ RANGE to read Excel?

image
image416×505 32.9 KB

11

@22222222asas

string.join("",input_dt.AsEnumerable.GroupBy(Function(a) tuple.Create(a(0).ToString,a(1).ToString)).Select(Function(b) b.count & b.key.item1).toarray)

Try this once

1 Like
12
  • Read Range activity output the result to a variable, let’s call it dtReadResult.
  • In the Invoke Code activity, you need to pass the variable dtReadResult to inputTable

image

1 Like
13

@aanandsanraj

Thank you for your response!
When proceeding as you suggested, I encounter this error. Is there a solution to resolve it?
error : Invoke Code: Exception has been thrown by the target of an invocation.

image

14

@Dilli_Reddy
Thank you for your response!
When I applied the code you provided, I got the following result. Is there any other approach I can try on my end?

image
I want result = 9P 1

Current Results
image

15

@22222222asas

string.join("",InputDT.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.key & b.sum(Function(c) cint(c(1).ToString))).toarray)
1 Like
16

No changes required please see my result below

image

Result
image

Can you please share your file?

1 Like
17

@Dilli_Reddy

Wow, you’re really amazing! Thank you so much. However, what I’m looking for is not P6B4 but rather 6P4B. I’m truly sorry, but could you please make the adjustment just for this part?

I want result = 6P4B

Current Result =
image

18

@aanandsanraj

Main.xaml (8.2 KB)
DD.xlsx (9.6 KB)
Thank you for your response. I apologize for my lack of skills! I will upload the file. Please check it for me

19

string.Join(“”,dt.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.key & b.Sum(Function(c) cint(c(1).ToString))).toarray)

1 Like
20

Hi @22222222asas

countB As Int32 = 0
countP As Int32 = 0

For Each row

ElseIf row(“B / P”).ToString.ToUpper.Trim = “B” Then
countB = countB+1
ElseIf row(“B / P”).ToString.ToUpper.Trim = “P” Then
countP = countP+ 1

result As String = countB.ToString + “B” + countP.ToString + “P”

Hope this helps

1 Like