Comparing Two Lists of ID Numbers in Excel
Solved/Closed
digitiser
rizvisa1
- Posts
- 1
- Registration date
- Tuesday October 16, 2012
- Status
- Member
- Last seen
- October 16, 2012
rizvisa1
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Related:
- Comparing Two Lists of ID Numbers in Excel
- Excel - Compare two lists and change row color - How-To - Excel
- How to create a drop down list with multiple columns in excel - Guide
- Compare the words in 2 columns in Excel ✓ - Forum - Excel
- Compare two lists and change row colour ✓ - Forum - Office Software
- Compare two cells and return value in excel ✓ - Forum - Excel
1 reply
rizvisa1
Oct 16, 2012 at 07:55 PM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Oct 16, 2012 at 07:55 PM
this macro is based on assumption that "Rosters" is the name of the sheet where all names are written
1. open the excel file
2. press alt + f11 (alt and f11 keys at same time) to launch VBE
3. in VBE, click on the sheet where id would be entered
4. Paste the code given
1. open the excel file
2. press alt + f11 (alt and f11 keys at same time) to launch VBE
3. in VBE, click on the sheet where id would be entered
4. Paste the code given
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim idRange As Range Dim idCell As Range Dim matchRow As Long Dim rosterSheet As String rosterSheet = "Rosters" Set idRange = Intersect(Range("A:A"), Target) If (idRange Is Nothing) Then Exit Sub Application.EnableEvents = False For Each idCell In idRange If (idCell.Value = vbNullString) Then idCell.Offset(0, 1) = vbNullString idCell.Offset(0, 2) = vbNullString idCell.Offset(0, 3) = vbNullString Else idCell.Offset(0, 1).Value = Format(Now, "yyyy-mm-dd hh:nn:ss") matchRow = getItemLocation(idCell.Value, Sheets(rosterSheet).Columns(1), True, False, True) If (matchRow = 0) Then idCell.Offset(0, 2) = "** UNKNOWN **" idCell.Offset(0, 3) = "** UNKNOWN **" Else idCell.Offset(0, 2) = Sheets(rosterSheet).Cells(matchRow, "B") idCell.Offset(0, 3) = Sheets(rosterSheet).Cells(matchRow, "C") End If End If Next Application.EnableEvents = True End Sub Public Function getItemLocation(sLookFor As String, _ rngSearch As Range, _ Optional bFullString As Boolean = True, _ Optional bLastOccurance As Boolean = True, _ Optional bFindRow As Boolean = True) As Long 'find the first/last row/column within a range for a specific string Dim Cell As Range Dim iLookAt As Integer Dim iSearchDir As Integer Dim iSearchOdr As Integer If (bFullString) _ Then iLookAt = xlWhole Else iLookAt = xlPart End If If (bLastOccurance) _ Then iSearchDir = xlPrevious Else iSearchDir = xlNext End If If Not (bFindRow) _ Then iSearchOdr = xlByColumns Else iSearchOdr = xlByRows End If With rngSearch If (bLastOccurance) _ Then Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir) Else Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir) End If End With If Cell Is Nothing Then getItemLocation = 0 ElseIf Not (bFindRow) _ Then getItemLocation = Cell.Column Else getItemLocation = Cell.Row End If Set Cell = Nothing End Function
Oct 27, 2012 at 02:15 PM
Oct 28, 2012 at 12:03 PM