Sub duplicate () Dim rng As Range, r As Range, chkVal As Integer Set rng = Range ("A1: A30") Set ist = Range ("B1: B30") For Each r In rng For Each s In ist chkVal = Application.CountIfs (rng, r, ist, s) If chkVal>1 Then s.Offset (0, 1) .Value = "Duplicate" End If Next s Next r End Sub
I want to check if there are duplicates in multiple conditions in columns A and B in the list.
If even one duplicate is found, all the lines related to it will be checked.
Example: Row A, Row B
1st line ● △ Duplicate
Second line ● △ Duplication
3rd line ●
I would like to do, but the current line will contain "duplicate" up to the third line.
What is the cause?
Answer # 1
How about looping first?
The premise that we want to process each chunk of lines is broken.
What about the following?
Dim rng As Range, r As Range, chkVal As Integer set rng = Range ("A1: B30") Dim arng as Range: set arng = Range ("A1: A30") Dim brng as Range: set brng = Range ("B1: B30") dim row for each r in rng.rows chkVal = Application.CountIfs (arng, r.Range ("A1"), brng, r.Range ("B1")) If chkVal>1 Then r.Range ("B1"). Offset (0, 1) .Value = "Duplicate" End If next
Answer # 2
How about separating functions and changing them to AND conditions?
chkVal = Application.CountIfs (rng, r, ist, s)
If chkVal>1 Then
chkVal1 = Application.CountIf (rng, r)
chkVal2 = Application.CountIf (ist, s)
If chkVal1>1 And chkVal2>1 Then
Answer # 3
Simply using Cells,
Dim i As Long 'for statement For i = 0 To 100 If Cells (i, 2) .Value = Cells (i-1, 2) .Value Then ' Cells (i, 3) .Value = "Duplicate" End If Next
What should I do?
In this case
The combination of row A and row B is also important, so you need to twist a bit.
Answer # 4
Why do you play For Each twice?
- vba - i want to pass class data to the user form
- Des encryption for Android data encryption
- vba data extraction copy and paste
- JS array property deduplication and check duplicate data
- Method for saving data to disk file in Vue
- Tips for data statistics in mysql
- Method for submitting Json data by Ajax
- Hill data sorting for java data structures
- Ajax check whether duplicate implementation code
- vba - i want to check a book that is opened read-only
- about vba data acquisition
- Java find duplicate data instances in list
- Method for reading data from Java jar file
- Rsa encryption for Android data encryption
- Des Encryption for Android Data Encryption
- Rules for Java data types
- Methods for operating json data in lua
- vba - for next double loop
- [vba] about deleting duplicate rows
- vba - i want to know how to check the default value of a property
- python - how to know the arguments of a vba function in a protected excel file
- vba - i want to transfer to a cell that meets two conditions
- vba error occurs after the second time
- vba sheet 1 a7, c7, sheet 2 a2 "2020" 03 "01" numerical value is added and displayed in the cell as "20
- the process is interrupted when i click the button on the web page with vba
- vba - i want to copy the last line and paste it to the last line +1
- vba - using the entirerow property
- vba - i want to divide the sheet by the same data
- about excel vba loop
- vba - how to apply change events to multiple cells at the same time