Concatenation and creating a pivot chart on excel

Help
1

Hi all,
I was wondering if you could help with an excel issue I am having.

Essentially, I have an excel sheet with some data. I would like to insert a new column and concatenate the data from two already existing columns. Once that is done, insert a pivot table based on the data from the concatenated column. I have tried recording (basic) but that didn’t work properly. I read through some of the other solutions provided to some similar questions but was not able to tailor the latter to my data.

The process is as follows:
(1) Open excel
(2) Go to a certain tab and click a button that will generate a pivot table of numbers. Click on the grand total value which will open the underlying data in a new tab “Sheet 1”
(3) Insert a column in the 24th position and write a formula to concatenate the data from 2 other columns.
(4) Create a pivot table from the concatenated data to show the sum of the different products, depending on their ID.

I was able to do 1 & 2 and am stuck on 3 & 4.

Recording would work well, I would think. For some reason, it is not giving the proper results. Thank you!

2

Hi @CCC,

I don’t know why you are trying to record, instead, you can use the existing activities to do so.

  1. If you have less data, then
    a) Use for each loop to get the two-column values from the same row.
    b) Assign activity to concatenate or write cell activity by giving the two inputs as a string. Ex: a.tostring + " " + b.tostring
    c) Create Pivot Table activity to select the required columns and pivot it.

  2. If you have more data, better to use this (My suggestion), you can use Invoke code activity so that you can do whatever you required by writing VB code.

Let me know if you need code for the above.

3

Thank you! I actually tried the “write cell” activity but the data is too big and it errored out (I might be doing something wrong).

What would the VB code be, please?

EDIT: added what I tried with concatenating and writing the formula. Capture

4

something like this would add a calculated column to the end of your pivot using vba

Worksheets(1).PivotTables(1).CalculatedFields.Add “PxS”, _
“= Product * Sales”

then just use the ‘Execute Macro’ activity to run it without even opening excel.

5

I don’t know why you are giving in quotes,
If client and ID are two variables, try giving them as

Client.tostring + " " + ID.tostring

Nothing else required

6

Thank you! What if my column name in the excel sheet and the variable I have declared are different. For e.g - I declared the variable “Client” but the column sheet name that will be used to concatenate is “Client Jan432”?

7

Thank you! I actually want to make the bot add the concatenate field prior to creating the pivot table. So,:
STEP 1: have the bot insert a new column (I was able to do that using “insert column” activity)
STEP 2: have the bot write the concatenation formula.
STEP 3: have the bot select that table and then create the pivot table.

Are you suggesting that I have the bot create the pivot table and then add the column I want to concatenate using activity “invoke code”?

8

I’m not saying anything related to the column names here, I’m mentioning about the variables you have to declare. Column names are used to read the item from the row in the for each loop.

If you can share the excel file and the columns you need to concatenate, will try to build the solution u need

9
Country Seasonal Watch Current Client Business Unit Year of Operation Credit Status Concatenated Field Prod ID Initiated Month
UK Yes ABC Co 2011 2019 3 This is where I would insert the column that will concatenate the client and the prod ID with another statement in between. So, when the bot is done, the final result for UK should read “ABC COChicago2345” 2345 Jan
France Yes DEF Co 2009 2019 4 4564 Feb
Italy No GHI Co 2009 2019 1 5675 Feb
Germany Yes JKL Co 2011 2019 3 5768 Apr
USA No MNO Co 2007 2019 4 4562 Jun
Singapore No PQR Co 2003 2019 1 3457 Dec
HK Yes STU Co 2009 2019 1 3568 Dec
Spain Yes VWX Co 2011 2019 1 4567 Nov
France Yes YZZ Co 2012 2019 3 3576 Apr

Thank you so much! I really appreciate the help :slight_smile:
I have copied a dummy version of some of the data. Do you think you can use this?

10

This will do the trick. I have used your data provided.

  1. It will create a datatable of your data.
  2. It will add a concatenated column at the end.
  3. It will populate the column using your logic.
  4. It will move the column (this uses the ordinal parameter in invoke method. the parameter number is the position you wish to move the column to)

Outputs the new table to a CSV.

concat2.xaml (7.9 KB)

11

@ronanpeter My apologies for the delay! I re-started working on my project and just downloaded your solution. I will try it out and see.

1 Like