How to get row index if Excel has duplicate row

Help Activities
, ,
1

I am getting the first occurance of duplicate row as row index in my excel .
I’ve tried lookup range. Can someone help me here.
See attached Sample Excel.
Query :
Sample.xlsx (12.5 KB)
I want to read and filter Excel to get only blank from column “P”.
Get the value from column “C” , search on website and compare data from Excel Row to Website.
IF data match then update column P’s row for that C cell’s value. “Completed”.

I have to do this for all the data in excel sheet and sometimes it may be available sometimes not and each of the time will get more data in this sheet.

Let me know if you need further detail for this.

Thanks

2

Hi,

How about the following sample?

image
image821×868 26.1 KB

Sample20220826-2.zip (11.9 KB)

Regards,

1 Like
3

What should I write in write range cell values for each of the rows , it should be dynamic?

4

Hi,

Can you elaborate your question with specific sample?

Regards,

5

Yes, Sure.

I will execute this bot once or twice in a month.
SO, first I will check column “Decision” IF any blanks are there .
For that blank rows EX: P2 is blank then I have to pick the value of C2 and search on website.
Need to match the records from Excel to website.
IF match : Update P2 Completed
If not match : Update P2 Not Match
I am able to got it upto this.

Now the Query is :
EX: C2 = 120 , C3=120, C4
: P2= Completed, P3 = “”, P4=“”

When Bot do the above process and try to update P3, it is again updating P2 because It is taking first occurance of the duplicate rows.

I am using Lookup range here, And when multiple same rows on column C then it is just taking first all the time.

Let me know if you still have doubt on the question.
Thank You

6

Hi,

The above sample in my previous post will work what you expect, I think. Can you try that?

Regards,

7

But, The range in Write range activity is “A1” so, what range can i use ? It should be dynamic everytime right ?

8

Hi,

The above sample overwrites whole table. Usually it’s no problem. However, if you want to use WriteCell, put it in ForEach and set cell address dynamic using value of item : row index value.

Regards,

9

Still not get it . What to write in Write range ?

10

HI,

Top left Cell address of the table. In the above sample, “A1”.

image

Or do you mean, top-left cell of the table will change evertime?

Regards,

11

No, JUst see the entire flow I wrote in above post.

This is updating all blank rows as “Completed”.

I just want to update for that particular row (C) which has matching data on Website. I have to compare and find something after using C cell. Then have to update different "Status " on Decision columns. I have almost 8 conditions to check before write the status and have 8 different status to update on excel. For each cell it would be different.

12

Hi,

Please add proper condition to write your expected value using IF activity and variable c. As there is no information about it, I omitted IF.

Regards,

13

Ok.

  1. First I am filtering blank rows on Column P. EX: P2
  2. Checking Data on website by the column C Value. EX: C2 (there is another for each for Web data because multiple rows are there and have to check each to match)
  3. IF : Data available , Check Column J2 is there on website ? IF Not Update P2= Not Match
  4. J2 match then check N2 on web , If not match , Update P2 : N Not match
  5. If both J2 and N2 match with web , Process something on website and at last update P2 = Completed.

I Hope Above explanation clears the Question.

Current Workflow :
Read Range (Excel)
Filter Data TB (Filter Excel to get blank)
For Each Row : excel
if conditions
Update Excel Column P

Thank you

14

Hi,

It’s okay just add condition as the following, for example.

img20220826-1
img20220826-11262×2449 83.7 KB

Sample20220826-2v2.zip (12.6 KB)

Regards,

1 Like
15

Got it , but my question is what should I write in below highlighted part?
image

16

HI,

Just “A1” if your table starts from “A1” cell.

Regards,

17

Hey, @Yoichi

I think this should work.
I will try in my workflow and will inform once done.

Thank You

18

Hello @PALKUMARI_PATEL
Kindlu try this metod

  1. Read the Excel file and store in Variable as a “DT1”
  2. Set lookuprange and enter the desired value you needed
  3. you can get vale in Excel postion like “C5”
  4. Using regex you can seperete the number and -1 is its index
rowindex=cint(System.text.RegularExpression.regex.match(LookupString,"\d+").tostring.trim)-1

Kindly refer this video, you may got some idea

19

I will try this also. Thanks for the respond.

20

Maybe this is solved… But in Excel, any VLOOKUP HLOOKUP etc formulae will only pick the first occurrence. If there are duplicate values / multiple occurrences; it will not move to the 2nd one and give the lookup value.