Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Don't add Duplicates

0

Hello,

I was wondering how i can adding data into tickets sheet without having duplicated into it?

Because i want to create 1 entry only for selection.

Demo included.

Press 'Fill Data' button to load data

Data collected from TicketsImport sheet.

Thanks

Answer
Discuss

Discussion

Hello,
Okay after some few tests it seems my vba code
Search on unique value only
The ticket # row has no unique value so thats why it copy them all?
How can i fix that please?

Thank you
GhostofWanted (rep: 46) Nov 29, '21 at 5:20 am
Add to Discussion

Answers

0
Selected Answer

Ghost 

Your example file doesn't help too much (since the Advanced filter is not removing duplicates) so I'm not exactly clear what you want. If you want to remove entries which are repeated (in one or more columns), you could use the Remove Duplicates method and just add this line (in bold) to your existing code:

        .Range("A2:K" & LastLogRow).AdvancedFilter xlFilterCopy, CriteriaRange:=.Range("P2:P3"), CopyToRange:=.Range("R2:AB2"), Unique:=True
        '  remove duplicates in range columns stated in array
        .Range("R2:AB" & LastLogRow).RemoveDuplicates Columns:=Array(1,2,4,6,7,8,9), Header:=xlYes
where the Array lists the combinations of columns where duplicates are removed. In the case of Array(1,2,4,6,7,8,9) above, if the entries for column 1-2 (Ticket #, Created On), 4 (Reported By,) and 6-9 (IAnswer, Issue Type, Issue, Status) are the same, the duplicates are removed. If you just used Array(7) then all duplicates in column 7 (Issue) would be removed.

Hope this fixes it for you (or you an create a column array which does)..

Discuss

Discussion

Awesome John_Ru
Thanks so much ;) just what i wanted :D
GhostofWanted (rep: 46) Nov 29, '21 at 5:36 am
Glad it worked for you. Thanks for selecting my Answer, Ghost.
John_Ru (rep: 6092) Nov 29, '21 at 5:37 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login