excelvbadictionary

Labeling a column's values by its criteria based on 2 different columns


Hoping that everyone is having a good one. (This is a continuation of another question I asked which can be found here: Does Excel VBA's "Scripting.Dictionary" work in languages other than English?)
I'm hoping that this will be the last question that I will ask about this macro that I am trying to make since I don't want to trouble people too much.

I was thinking that it would be better to clear a column and label each buyer respectively if they have conflicting stores or not. So far, I have this code that a few people have helped me with...

Private Sub Check_RR()

    Application.ScreenUpdating = False
    If Not Cells(2, 8).Value = "WS_Sales" Then
        End
    End If
    
    AROW = ActiveCell.row
    ACOL = ActiveCell.Column
    
'Uniqueエンドユーザー
    Dim wsActive As Worksheet
    Dim lastRow As Long
    Dim buyerStoreMap As Object
    Dim buyerName As String
    Dim rowIndex As Long
    Dim storePairKey As String
    Dim storePairDict As Object
    Dim selectedBuyer As String
    Dim isBuyerFound As Boolean
    Dim activeRow As Long

'S列クリアする
    Range("S3", Range("S" & Rows.Count).End(3)).ClearContents

'WorksheetとDictionary
    Set wsActive = ThisWorkbook.ActiveSheet
    lastRow = wsActive.Cells(wsActive.Rows.Count, "G").End(xlUp).row
    Set buyerStoreMap = CreateObject("Scripting.Dictionary")

'エンドユーザーの一次店と二次店の組み合わせ
    For rowIndex = 1 To lastRow
        buyerName = Trim(wsActive.Cells(rowIndex, "G").Value)
        If buyerName <> "" Then
            storePairKey = wsActive.Cells(rowIndex, "C").Value & "|" & wsActive.Cells(rowIndex, "E").Value
            If Not buyerStoreMap.Exists(buyerName) Then
                Set storePairDict = CreateObject("Scripting.Dictionary")
                buyerStoreMap.Add buyerName, storePairDict
            End If
            buyerStoreMap(buyerName)(storePairKey) = 1
        End If
    Next rowIndex

'エンドユーザー
    For rowIndex = 1 To lastRow
        buyerName = Trim(wsActive.Cells(rowIndex, "G").Value)
        If buyerName <> "" Then
            If buyerStoreMap.Count > 1 Then
                wsActive.Cells(rowIndex, "S").Value = "コンフリ有り"
            Else
                wsActive.Cells(rowIndex, "S").Value = "コンフリ無し"
            End If
        End If
    Next rowIndex

'S列に「コンフリ有/無」をラベルします
        
    
    
'フィルターをかける
    If wsActive.AutoFilterMode Then wsActive.AutoFilter.ShowAllData
    wsActive.Range("A1:G" & lastRow).AutoFilter Field:=7, Criteria1:=ActiveCell

'The code continues with more filters that will have the needed outcome.

The important part that I need help with is the top part, wherein the data in Column G will be labeled as "Conflict/No Conflict(but in Japanese ahaha)" in Column S depending on the store combination in Columns C and E. If it's easier to not put a label on non-conflicted buyers, that's fine too. Also, the Dictionary that I currently have always ends up being Empty. I might need a bit of assistance there too. I'm very sorry for the trouble! Also, is it okay to put filter over filter like how I did in my current code? As someone who is learning, I would love some advice. I appreciate you all very much. Thank you in advance.


Solution

  • Here's sub that would put appropriate information in S column. I have put inline comments for clarity.

    The approach is similiar to what you have presented:

    Sub PopuateWithAdditionalInformationAbutConflictingStores()
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim dict As Object
        Dim i As Long
        Dim buyer As String
        Dim uniquePair As String
        Dim pairsDict As Object
    
        Set ws = ThisWorkbook.ActiveSheet
        lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    
        Set dict = CreateObject("Scripting.Dictionary")
    
        ' Build dictionary: Buyer -> Collection of "ColC|ColE" values
        For i = 2 To lastRow ' Assuming headers in row 1
            buyer = Trim(ws.Cells(i, "G").Value)
            If buyer <> "" Then
                uniquePair = ws.Cells(i, "C").Value & "|" & ws.Cells(i, "E").Value
                If Not dict.exists(buyer) Then
                    Set pairsDict = CreateObject("Scripting.Dictionary")
                    dict.Add buyer, pairsDict
                End If
                dict(buyer)(uniquePair) = 1
            End If
        Next i
    
        ' Loop once again over buytes and check if they
        ' have conflicts and put information in S column
        For i = 2 To lastRow ' Assuming headers in row 1
            buyer = Trim(ws.Cells(i, "G").Value)
            If buyer <> "" Then
                If dict(buyer).Count > 1 Then
                    ws.Cells(i, "S").Value = "CONFLICT"
                Else
                    ws.Cells(i, "S").Value = "NO CONFLICT"
                End If
            End If
        Next i
    End Sub