Excel VBA compare values on multiple rows and execute additional code

0 votes

I need to do the following: In my document, there are several fields whose combinations need to be compared, and if they match, another field on the same rows needs to be modified.

As of now, I use select statements per column to add the values in arrays (skipping the first row as the header, resulting in iNum = 2) and concatenate them per row for the comparison.

Dim conc As Range                               'Concatenated fields
Dim iconc() As Variant

ReDim iconc(UBound(iMatn) - 1, 1)

For iNum = 2 To UBound(iMatn)
    iconc(iNum - 1, 1) = iMatn(iNum, 1) & iVendr(iNum, 1) & iInd1(iNum, 1) & iInd2(iNum, 1)    'Current concatenation

    Select Case iNum - 1
    Case 2:                     'Compare two records
        If iconc(iNum - 2, 1) = iconc(iNum - 1, 1) Then         'Compare first and second records
            'Execute code to update the two fields from Extra field column
        End If

    Case 3:                     'Compare three records
        If AllSame(iconc(iNum - 3, 1), iconc(iNum - 2, 1), iconc(iNum - 1, 1)) Then
            'Execute code to update the three fields from Extra field column
        End If

Even though there could be several hundred lines, I go through each value of the concatenation and use a case statement to compare if it is the same as the previous ones (I don't anticipate more than 4 or 5 to be the same). Thus, I have two problems.

  1. If there are 3 equal values, for example, the code first jumps to the case for 2. How can I make it so that it skips to the maximum value?
  2. It needs to resume checking after the rows that were already checked. E.g. if the first two are the same, the code should start checking from the third one; basically to start at from the line after the last of any duplicate ones that are located.
Feb 7, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

I would use a Dictionary & Collection object to count how many are in each category in order to determine how you would update the extra fields column.


'Set reference to Microsoft Scripting Runtime
'    (or use late-binding)

Option Explicit

Sub due()
  Dim myDict As Dictionary, col As Collection
  Dim i As Long, v As Variant
  Dim sKey As String
  Dim rTable As Range
  Dim vTable As Variant, vResults As Variant

'there are more robust methods of selecting the table range
'depending on your actual layout
'And code will also make a difference if the original range includes
'  or does not include the "Extra Field" Column
' Code below assumes it is NOT included in original data

Set rTable = ThisWorkbook.Worksheets("sheet2").Cells(1, 1).CurrentRegion

vTable = rTable

Set myDict = New Dictionary
For i = 2 To UBound(vTable)
    sKey = vTable(i, 1) & vTable(i, 2) & vTable(i, 3) & vTable(i, 4)
    Set col = New Collection
    If Not myDict.Exists(sKey) Then
        col.Add Item:=WorksheetFunction.Index(vTable, i, 0)
        myDict.Add Key:=sKey, Item:=col
        myDict(sKey).Add Item:=WorksheetFunction.Index(vTable, i, 0)
    End If
Next i

For Each v In myDict.Keys
    Select Case myDict(v).Count
        Case 2
            Debug.Print v, "Do update for two rows"
        Case 3
            Debug.Print v, "Do update for three rows"
        Case Else
            Debug.Print v, "No update needed"
    End Select
Next v

End Sub


1234V22341212 Do update for three rows
1234v22351215 No update needed
2234v22361515 Do update for two rows
2234v22361311 No update needed

However, I would definitely use Power Query, which can simply group by the four columns and produce a count and is available in Windows Excel 2010+ and 365. Depending on that count, you can then create a new column.

It is impossible to provide any code for that purpose without understanding the specifics of your updating Extra Field and the differences between what occurs for 2, 3, 4,... the same.

answered Feb 7, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel VBA code window opened cropped and can't view code

So it turns out after some online ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

I want to compare two Excel files and highlight the differences with VBA

The workbook doesn't have the UsedRange property ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

VBA Change Cell colors based on value, and it can deal with single cell and multiple cells changes

Before looping through all of the cells ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Copy the respective values from another workbook depend on specific conditions

Try this: Sub Get_Respective_Values_Of_Last_Closing_Date() Dim wb1 ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

VBA How do I replace the range with an array in SUMIF

You can't, in my opinion. When you ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

How to concatenate elements of a single-dimensional array using VBA?

Using Microsoft 365's UNIQUE and SORT in VBA ' This is a ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Loops through Check Box in VBA

Please attempt the next option. Assuming you ...READ MORE

answered Apr 1, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP