[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 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