Help with this Excel automation, please

Help Studio
, ,
1

I need to count duplicate values in the same column “Lote” from sheet 2 and then I’d like to compare that with values in sheet 1 on column “quantity”. If this match on the first attempt It should finish and print the value of column c which means what value matched.

For example:
In sheet 2, there are 8 values of “L1” so It should look for in sheet 1 and try to match with 1 but “quantity” has 9 so It doesn’t match.
So next attempt:
In sheet2, there are 4 values of “L2” so It should look for in sheet1 It matches with quantity “4” of 2 and It should print “L2” which is the value of column Lote".

L3 is not evaluated because of the matched L2.

Any idea, thanks.

image

image
image710×354 1.98 KB

I’ve uploaded the file
test.xlsx (9.1 KB)

2

image
image906×885 70.4 KB

dt1 = sheet1
dt2 = sheet2

3

@Eric_Wong_Test

Please follow below steps…

  1. Read sheet 1 into dt1 and sheet2 into dt2
  2. Use filter dattable on dt1 to remove empty rows
  3. Loop through eqch row in dt1
  4. Inside loop use filter datatable and filter dt2 with currentrow(“lote”).ToString
  5. Now use if condition to check the count giltereddt2.Rowcount.Equals(cint(currentrow("quantity").ToString)
  6. On then side use log message and write currwntrow("lote").Tostring and then end loop/break
  7. On else side you can leave blank or for purpose can write did not match

Hope this helps

Cheers

4

Could you upload the sample, please.

5

To automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them. A macro can do that for you. You can record a macro applying the format you want, and then replay the macro whenever needed.

Excel automation streamlines your use of the application by automatically performing tasks like formatting cells, updating values, and running macros. With an RPA solution, you can also integrate Excel tasks in automated processes with other tasks across the enterprise.

Regards,
Will

6

@pprin001

please check this.adapt to your files and sheetnames

Sample1.zip (3.2 KB)

cheers

1 Like