Solved

Using Dictionary Class to find duplicate Values before copying to new worksheet

Posted on 2013-01-21
4
158 Views
Last Modified: 2013-01-22
While working on a problem with my counter in my loop, The_Barman posted a link to Patrick's article on using the Dictionary Class In VBA.

I have read the article and follow some of it and I get the concept, but working into my loop is a bit over my head. TheBarman was kind enough to let me know I could post a question for some direction (Okay, a lot of help!) Patrick's article gave two excellent examples, which I have stepped through several times, just not sure how to work into my current code.

Below is my current code. The project is pretty straight forward. I import an Excel file which contains a list of vacation request (Approved and Rejected) into my "Imported" worksheet (it's a  temp worksheet which is cleared before each import). I keep a list of "Approved" and "Rejected" as a yearly summary in their respectively named worksheets. Each record (request) has a unique identifier in the A column. Since I import from Jan 1st each time, I have duplicate request from prior imports. My current loops separates the "approved" and "rejected" into the appropriate  worksheet, but it doesn't check for any previously imported records with the same unique ID#.

I hope this makes sense.

Thanks for the help and I'll  study the code in detail.

Brent

Option Explicit
Sub SortApprovedRejected()

Dim ApprovedSheet As Worksheet
Dim ImportedSheet As Worksheet
Dim RejectedSheet As Worksheet
Dim iCol As Long, iRow As Long, iRowStart As Long, iRowEnd As Long, iNextRow As Long
Dim UniqueIDApprovedWorksheetRng As Range
Dim UniqueIDRejetectedWorksheetRng As Range
 
Set ApprovedSheet = ThisWorkbook.Worksheets("Approved")
Set ImportedSheet = ThisWorkbook.Worksheets("Imported")
Set RejectedSheet = ThisWorkbook.Worksheets("Rejected")

'//////// My ApprovedWorksheet has an extra column, so this add's the extra column prior to copying
ImportedSheet.Columns(6).Insert

'//////// This will be for my counter
iRowStart = 2
iRowEnd = Cells(Rows.Count, "A").End(xlUp).Row ' Find the last row of data
iCol = 8


For iRow = iRowStart To iRowEnd
    
    If ImportedSheet.Cells(iRow, iCol).Value = "Approved" Then
        '/// move to appropriate sheet
        iNextRow = ApprovedSheet.Cells(ApprovedSheet.Rows.Count, 1).End(xlUp).Row + 1
        ApprovedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
    Else
        iNextRow = RejectedSheet.Cells(RejectedSheet.Rows.Count, 1).End(xlUp).Row + 1
        RejectedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
    End If
Next iRow
End Sub

Open in new window

vacation-project-21-jan-13---EE.xls
0
Comment
Question by:bvanscoy678
  • 3
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38803502
I don't think you need a Dictionary for that--a simple COUNTIF will do just fine:



Option Explicit
Sub SortApprovedRejected()

Dim ApprovedSheet As Worksheet
Dim ImportedSheet As Worksheet
Dim RejectedSheet As Worksheet
Dim iCol As Long, iRow As Long, iRowStart As Long, iRowEnd As Long, iNextRow As Long
Dim UniqueIDApprovedWorksheetRng As Range
Dim UniqueIDRejetectedWorksheetRng As Range
 
Set ApprovedSheet = ThisWorkbook.Worksheets("Approved")
Set ImportedSheet = ThisWorkbook.Worksheets("Imported")
Set RejectedSheet = ThisWorkbook.Worksheets("Rejected")

'//////// My ApprovedWorksheet has an extra column, so this add's the extra column prior to copying
ImportedSheet.Columns(6).Insert

'//////// This will be for my counter
iRowStart = 2
iRowEnd = Cells(Rows.Count, "A").End(xlUp).Row ' Find the last row of data
iCol = 8


For iRow = iRowStart To iRowEnd
    
    If ImportedSheet.Cells(iRow, iCol).Value = "Approved" Then
        '/// move to appropriate sheet
        If Application.CountIf(ApprovedSheet.[a:a], ImportedSheet.Cells(iRow, 1)) = 0 Then
            iNextRow = ApprovedSheet.Cells(ApprovedSheet.Rows.Count, 1).End(xlUp).Row + 1
            ApprovedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
        End If
    Else
        If Application.CountIf(RejectedSheet.[a:a], ImportedSheet.Cells(iRow, 1)) = 0 Then
            iNextRow = RejectedSheet.Cells(RejectedSheet.Rows.Count, 1).End(xlUp).Row + 1
            RejectedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
        End If
    End If
Next iRow
End Sub

Open in new window

0
 

Author Comment

by:bvanscoy678
ID: 38803568
Patrick,

Let me look at this a bit closer. Thanks.
0
 

Author Comment

by:bvanscoy678
ID: 38803599
Patrick,

It does work perfect! Dang, I wish I would see those easy fixes. I am curious what TheBarman has to say, so if you don't mind, I'll keep the question open until morning.

If Application.CountIf(ApprovedSheet.[a:a], ImportedSheet.Cells(iRow, 1)) = 0 Then

I  kept thinking I would need to create a range for both worksheets in the A Column, assign a variable to hold my value in the A Column of my imported worksheet's current row and then compare.

Thanks!
0
 

Author Closing Comment

by:bvanscoy678
ID: 38806629
Thank you for the help, it is greatly appreciated. I place all my questions into One Note, so I can refer to them for future projects.

Brent
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now