Latest Stories
M1 Max MacBook Pro and External Display: When Productivity Becomes Frustration Social Media Limbo kamshin.com on hiatus Some thoughts on the new MacBook Pro Opportunities And Challenges With Personal Health Data – Looking at Garmin Data

kamshin

  • Home
  • All Posts
  • Categories
    • General
    • Tech Field Day
    • Storage
    • Nutanix
    • Certifications
    • Conferences
    • Worth reading
    • Design
    • Rants
    • Active Directory
  • Media & Press
  • Podcast
  • About me
    • About me
    • Where is Max?
    • Disclosure & Policies

Compare and reconciliate data from two Excel lists

June 2, 2010

FacebookTwitter

Addendum : I've been asked what is the point of this script when excel offers the VLOOKUP function. The aim of this script is to scan lists that cannot be sorted for several reasons (e.g. chronological data).

 

Besides my activities as system administrator, I am also known (most probably wrongly) for my "über-skills" with Microsoft Excel. I am very often dealing with data reconciliation in huge excel lists and so on. Most of this involves post-analysis with Active Directory data.

Being a firm believer of the "if you can, script it" credo, I have done so and I present you with a modest creation: my "Correlate IDs" VBS Script.

Let's use a practical case : we have inherited two lists :

List1.xls has Employee IDs and Employee Names.
List2.xls has Employee IDs and their Line Manager Names only.

We want to show in one list both Employee ID, Employee Name and Line Manager Name. How do we proceed?

Let's add both lists in a single document with four colums :

A – Employee ID
B – Employee Name
E – Employee ID
F – Line Manager Name

You have gathered data in columns A and B from List1.xls, and data from columns E and F from List2.xls.
You want to fill column C with the Line Manager name. How do we proceed ?

We call our script, which presents us with 7 dialog boxes :

Dialog Box 1 : Specify the Excel Sheet name
Dialog Box 2 : Cell position to start searching for IDs (ex. A2)
Dialog Box 3 : Column where data will be written (we said above we want to use column C, therefore we input C)
Dialog Box 4 : We specify where do we scan for Target IDs (in our case, column E, as we compare employee IDs between columns A and E)
Dialog Box 5 : We specify here where is located the data that we will copy back in column C (in our case, column F)
Dialog Box 6 : We specify the number of rows to scan in column A
Dialog Box 7 : We specify the number of rows to scan in column E 

After that, the script will execute and will autofill the data as you requested.

Magic, isn't it ?

Have good fun correlating your lists and enjoy a nice cup of coffee or tea!

Max

Code starts here :

Option Explicit
Sub CorrelateIDs()

'by Max - www.kamshin.com

'Scans for IDs in column LeftIDCol$, checks for a match in Column RightIDCol$

'then fills the matching LeftDataCol$ cell with the value contained in RightDataCol$ cell

Dim Tab1Cursor, Tab2Cursor, MaxRowsLeft, MaxRowsRight As Integer

Dim CurrentLeftTblCursor$, CurrentLeftTblWriteCursor$

Dim CurrentRightTblCursor$, CurrentRightTblReadCursor$

Dim CurrentLeftTblID$, CurrentRightTblID$, CurrentRightTblWriteVal$

Dim Extr$, UserSelectedSheet$, WriteCol$, RightIDCol$, RightDataCol$, LeftIDCol$, LeftDataCol$

UserSelectedSheet$ = InputBox("Please enter Sheet name", "Sheet Name ?")

Worksheets(UserSelectedSheet$).Activate

CurrentLeftTblCursor$ = InputBox("Please enter first SOURCE IDs are located (ex. A2)", "Start Cell", "A2")

LeftIDCol$=Left(CurrentLeftTblCursor$,1)

Extr$ = Right(CurrentLeftTblCursor, 1)

Tab1Cursor = CInt(Extr$)

LeftDataCol$ = InputBox("Please enter column name where data will be written (ex. B)", "Start Cell", "B")

RightIDCol$  = InputBox("Please enter column name where TARGET IDs are located (ex. C)", "Start Cell", "C")

RightDataCol$  = InputBox("Please enter column name where TARGET DATA is located (ex. D)", "Start Cell", "D")

MaxRowsLeft = CInt(InputBox("Please specify how many lines to check in the left table (ex. 300)", "Max Left Table lines", "300"))

MaxRowsRight = CInt(InputBox("Please specify how many lines to check in the right table (ex. 300)", "Max Right Table lines", "300"))

Do While Tab1Cursor < MaxRowsLeft

   CurrentLeftTblCursor$ = LeftIDCol$ & Tab1Cursor

   CurrentLeftTblWriteCursor$ = LeftDataCol$ & Tab1Cursor

   Tab1Cursor = Tab1Cursor + 1

   Tab2Cursor = CInt(Extr$)

   Range(CurrentLeftTblCursor$).Select

   CurrentLeftTblID$ = ActiveCell.FormulaR1C1

   Do While Tab2Cursor < MaxRowsRight

      CurrentRightTblCursor$ = RightIDCol$ & Tab2Cursor

      CurrentRightTblReadCursor$ = RightDataCol$ & Tab2Cursor

      Tab2Cursor = Tab2Cursor + 1

      Range(CurrentRightTblCursor$).Select

      CurrentRightTblID$ = ActiveCell.FormulaR1C1

                If CurrentRightTblID$ = CurrentLeftTblID$ Then

                        Range(CurrentRightTblReadCursor$).Select

                        CurrentRightTblWriteVal$ = ActiveCell.FormulaR1C1

                        Range(CurrentLeftTblWriteCursor$).Select

                        ActiveCell.FormulaR1C1 = CurrentRightTblWriteVal$

                        Exit Do

               End If

   Loop

Loop

End Sub

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Related

A note to our readers

kamshin.com has a strict no advertisement policy. If you enjoy this website, please consider making a donation to one of these non-profit organizations that I personally support:


People in Need - Czech Republic

A Czech-based non-governmental, non-profit organization founded on the ideals of humanism, freedom, equality and solidarity, helping people in the Czech Republic and in the entire world.

People In Need Logo

Greenpeace

Hopefully this one doesn't requires any explanation. Act for our planet. Act now.

Greenpeace Logo

826 National

US-based charity. An international proof point for writing as a tool for young people to ignite and channel their creativity, explore identity, advocate for themselves and their community, and achieve academic and professional success.

826 National Logo

 


Electronic Frontier Foundation

The leading nonprofit defending digital privacy, free speech, and innovation.

EFF Logo

 


Thank you!

RSS Latest Podcast Episodes

  • EP 30 -Rose Ross Chief Tech Trailblazer on the Tech Trailblazer awards
  • EP29 – Imagine the possibilities to manage your data with Data Dynamics StorageX – with Piyush Mehta
  • EP28 – Introducing Clumio, A Cloud-Based Data Platform Launching With Data Protection As A Service – with Poojan Kumar
  • EP27 – VAST Data – A Revolutionary Storage Platform For The Next Decade – with Howard Marks

Categories

  • Active Directory (5)
  • Certifications (8)
  • Conferences (22)
  • Design (1)
  • Featured (1)
  • General (89)
  • Nutanix (4)
  • Rants (2)
  • Storage (38)
  • Tech Field Day (44)
  • Worth reading (4)

Latest Tweets

My Tweets

Popular posts this week

  • Using Virtual Machine custom attributes with PowerCLI for snapshotting
  • My move from Gmail to ProtonMail: a comprehensive report on gaining back my privacy
  • Is the 16-Inch MacBook Pro Crippled By Thermal Issues?

Categories

  • Active Directory
  • Certifications
  • Conferences
  • Design
  • Featured
  • General
  • Nutanix
  • Rants
  • Storage
  • Tech Field Day
  • Worth reading

Pages

  • Blog
  • Disclosure & Policies
  • Home
  • Media & Press
  • VCAP5-DCD Resources
  • VCP5 Certification Resources
  • About me

Archives

Copyright ©2016 kamshin