?
Solved

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

Posted on 2013-01-21
4
Medium Priority
?
164 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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

762 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