Updating a cell in a loop with multiple rows of duplicate data

Help Studio
, , ,
1

Hi Everyone,
I have an original excel file with the following data fields:

Column1 Column2 Column3
Aeon 87 Aeon 87 January 2, 2023 14:04
Aeon 87 Aeon 87 January 2, 2023 14:15
Aeon 87 Aeon 87 January 2, 2023 17:27
Aeon 87 Aeon 87 2/2/2023 18:27
Aeon 87 Aeon 87 2/2/2023 19:27
Aeon 86 Aeon 86 3/2/2023 19:28

I will extract column 1 and column 3 to get the following output:
Aeon 87=> 87 : save to Number variable
2/1/2023 14:04 => 2/1/2023 => save to Date variable

Then I will compare with an excel file user:

Filter on column H = Number and column I = Date
Ouput :
get userName and Email columns B and C.

Finally, check in another excel file.

Check if the email exists or not, if it exists, get employedID in column C and UserName in column A. Then update it again in column1 = userName & column2 = employedID of the original excel file.

I have solved almost 80% of the above problems. I am in need of a solution when updating to the original file. Instead of updating a single line in a loop. Then based on column1 and column3. If the first row is the same as the next row, it will update and color all. then the loop will skip the updated cells.

Column1 Column2 Column3
[Aeon 87] Aeon 87 [2/1/2023 14:04]
[Aeon 87] Aeon 87 [2/1/2023 14:15]
[Aeon 87] Aeon 87 [2/1/2023 17:27]

Is there a way to do it?
Thanks in advance,

3

@Tuannna1

All if there are not consecutive…then you cannot…

Else you can first find the indexes of rows which are matching and color all together…

And to run only one first get unique rows from
The datatable you already have…

Use dt.DefaultView.ToTable(true,"Column1",Column3")

Try it if you are stuck we can help

Hope this helps

Cheers

4

can you upload excel file @Tuannna1

I have solved almost 80% of the above problems. I am in need of a solution when updating to the original file. Instead of updating a single line in a loop. Then based on column1 and column3. If the first row is the same as the next row, it will update and color all. then the loop will skip the updated cells.

you want the VBA to just highlight the rows if its the same as next row right?

5

@jack.chan
For some reason, I’ll send you in a message.
I want VBA to update and highlight the rows if it is the same as the next row.

Thank you for your response.