Excel Automation

Hi all,

Hope you all are doing great. This is my first post. Thank you all for creating and contributing to this forum. Really helpful.

I am trying to automate a process which includes multiple excel sheets. Normalization of these excel sheets is required which includes various excel features mentioned below:

1. Sorting
   
2. Removing Duplicates
   
3. Filtering - Normal and text filters
   
4. VLOOKUP
   
5. Highlighting Rows
   

I am not able to use Excel features when I open an Excel sheet in Kapow. Could any of you help regarding the same? Thanks in advance!! Cheers!

Hi,


Normally I will store my excel data into SQL database, then when query from database I am able to sort & filtering.

This is the SQL filtering code. Here is the link.

Code:

SELECT column1, column2,
FROM table_name
WHERE condition;

Sorting Code, link.

Code:

SELECT column1, column2,
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Duplicate Code, link.

Code:

SELECT DISTINCT column1, column2,
FROM table_name;

To insert formula, you can use the build in function Picture 1.
If your formula consist of cell number that will constantly change, then assign a variable and use expression to write the formula (Picture 2). 

Picture 1



For example:

Within the formula "VLOOKUP(H1,CC,2,0)", the cell number is always changing.

Code:

VLOOKUP(H"+Test+",CC,2,0)

Hence, the "Test" in this case is a Variant that replace the number.

Picture 2


I have yet to explore on the highlighting row. 
However, hope the above explanation could help you.
If you explore a better way share with us.

Kind Regards,
Kuan

Thank You so much Kuan.

Let me try this out and I would let you know if I find a different approach.

Meanwhile, I was trying to work with Google sheets in order to use the excel features directly. I am facing below error:

"This version of Google Chrome is no longer supported. Please upgrade to a supported browser Dismiss


Please find the details below:

• Google Chrome is up to date
  

           Version 72.0.3626.119 (Official Build) (64-bit)

• Design Studio 10.3.0.0 166
  

Thank you so much for your help.

Hi Kuan,

Thank You so much for the help. Could you please help me with suggestions to automate excel features directly in Kapow and not by using the database?

I have not set up any database yet and was trying to create snippets for the excel actions:

1. Sorting
   
   
2. Removing Duplicates
   
   
3. Filtering - Normal and text filters
   
   
4. VLOOKUP
   
   
5. Highlighting Rows
   
   

Could you please help me with that?