Compare range in excel

Help Activities
, ,
1

I want to compare rate in column FYP and column APPLAN from file Input with file ConfigRate as below.

image
image1492×456 50.5 KB

Condition comparing

  1. check data in column FYP and column APPLAN
    If data in column APPLAN in input file = Column Campaign in file config → compare rate in column C in file Config.
    But if column APPLAN not match in column Campaign in file config → cpompare rate in column D

Example row 2 in file Input → FYP = 750,000 and APPLAN = BU03—> match in file ConfigRate → Rate 750,000 = Bag20

Write output in column Bag and write column Amount =1

and count amount in each Channel in sheet Summary as below.

image
image753×341 14.3 KB

  • remark : I don’t want to fix column name in code —> I want to use file config to pass column name , column index as below.

image
image813×187 8.91 KB

Please guide me about it.

Config.xlsx (12.3 KB)
ConfigRate.xlsx (19.3 KB)
Input (9).xlsx (452.5 KB)

1 Like
2

Hi @fairymemay,

Please, take a look at the attached draft solution.

If you have any question, let me know.

If it solves your problem, kindly mark this post as solution to close this topic.

Thanks!

Main.xaml (32.7 KB)

3

@gustavo.cervelin It take time to run 10 minute, It can run fast then now?

and If in future I have more bag , But I don’t edit code.

image

Please guide me more for solve it.

1 Like
4

Hi @fairymemay,

If you debug, it will take a long time.

If you just run, it will finish the process quickly.

I will make some changes to consider more bags in the future.

1 Like
5

@gustavo.cervelin Ok , It run quickly.

Thank you for your support about more bags in future.

1 Like
6

Hello again @fairymemay,

Find attached the new solution.

I had to add one extra column in the Config_rate sheet:

image

It must match the respective column in the summary sheet.

image

It is taking around 20 seconds to run the process :slight_smile:

If you have any question, let me know.

If it solves your problem, kindly mark this post as solution to close this topic.

Thanks!

Main.xaml (38.9 KB)

1 Like
7

@gustavo.cervelin Sheet Summary original not have data as below. (sheet blank)
Input (9).xlsx (106.8 KB)

1 Like
8

Oh…

Should the summary sheet be created?

I mean, I thought its structure would be already defined and the automation would only enter the values. In other words, this input file will only have one tab (Details) before the automation runs, right? And after it runs, it will have two sheets (Details and Summary), right?

If that’s what you are looking for, I can do this tomorrow and send you a new version :slight_smile:

@fairymemay

9

@gustavo.cervelin yes, Before run it have 1 sheet (Detail) → After run have 2 sheets (Summary , Detail)

1 Like
10

I think @gustavo.cervelin is making a great solution!! Go ahead guys!!

2 Likes
11

Thanks @gabrielribas4. You are doing a fantastic job here in the forum, keep rocking mate :slight_smile:

@fairymemay, I’ll send you the new version as soon as possible. Time to get some sleep :sweat_smile:

2 Likes
12

@gustavo.cervelin okay :grinning:

1 Like
13

@fairymemay

Here you go the new version :slight_smile:

There is need of that extra column in the Config_rate sheet anymore

If you have any question, let me know.

If it solves your problem, kindly mark this post as solution to close this topic.

Thanks!

Main.xaml (42.4 KB)

2 Likes
14

@gustavo.cervelin Wow. Perfect
Use time to run = 2 second.

1 Like
15

@gustavo.cervelin If good , It can delete flag green → convert to number

image

1 Like
16

@gustavo.cervelin Can​ convert​ to​ num?

1 Like
17

Hi @fairymemay

Yeah, I think so…

I’ll try as soon as possible and send you another version

1 Like
18

Hi @fairymemay,

Sorry for taking to long, today was a busy day.

Here is the new version:

Main.xaml (42.4 KB)

2 Likes
19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.