Automatic cell references based on similar text values in adjacent column

[CatalysTim]

Hi,

I have a large database resembling the image below, with instances of similar text values in one column and a corresponding numerical column next to it. I am trying to modify the numerical column such that when I edit one numerical cell, all cells in the same column with similar (not identical) adjacent text values inherit the same value (e.g. green cells). The challenges are:

1. Similar text values = contains same starting phrase (e.g. Apple - Green is matched with Apple - Green and Big, but not Apple - Red)
2. I can't use color coding for the numerical column per the image below, because the spreadsheet is already color coded for other purposes.

I've thought of creating a separate worksheet that would automatically populate itself with text values (in alphabetical order) from the equivalent of Column B in the main database worksheet. The second worksheet would then contain user-inputted numerical cells, to which the numerical cells in the main database worksheet would refer.

Any advise on creating a solution for this would be greatly appreciated.

 

[CodeNinja]

CatalysTim,
I would create "Common Identifier" column (lets say column D in your sample above) and put numbers in that column... so...
Seller Item Price Unique Identifier


Seller Item Price Unique Identifier
AA Apple - Green 1
BB Orange - Big 2
CC Apple - Green and Big 1
DD Apple - Red 3
EE Banana - Yellow 4
FF Orange 2
GG Banana - Big and Yellow 4









Then add code to the sheet on change event to check for the unique identifier....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim identifier As Integer
Dim identifierRange As Range
Dim lastRange As Range

If Target.Column = 3 Then
    Application.EnableEvents = False
    Set identifierRange = Sheet1.Range("D2:D" & Sheet1.UsedRange.Rows.Count)
    identifier = Target.Offset(0, 1)
    Set lastRange = Sheet1.Range("D2")
    Do
        identifierRange.Find(what:=identifier, after:=lastRange, lookat:=xlWhole).Offset(0, -1) = Target
        Set lastRange = identifierRange.Find(what:=identifier, after:=lastRange, lookat:=xlWhole, searchdirection:=xlNext)
    Loop Until identifierRange.Find(what:=identifier, after:=lastRange, lookat:=xlWhole).Address = Target.Offset(0, 1).Address
    Application.EnableEvents = True
End If
    

 

End Sub

Good luck,

CN.