Solved

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

Posted on 2013-01-21
4
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

696 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