Use same input for all duplicates

[mergim]

Hello,

Is there any function or coding that can autofill duplicates based on my first input. For example below, when I add "1" into the cell called "apple", I want excel to add "1" to all apples, so I won't have to manually go into every cell and add 1. I know you can filter, and then drag, however I want something that is quicker. Thanks

 

[bebo021999]

After all "Apple" cells filled as 1, if come back for 2nd input in A2 ( 1 change to 2 for instant), do the other cells (A5, A7,...)with "Apple" change to 2 accordingly?

 

[mergim]

After all "Apple" cells filled as 1, if come back for 2nd input in A2 ( 1 change to 2 for instant), do the other cells (A5, A7,...)with "Apple" change to 2 accordingly?

Hello,

Yes, the should change accordingly. The value shall always be the same for the same "fruit" in this case

 

[bebo021999]

Suppose "Fruit" is in column A, from A2, "Scoring" is from B2
This code is placed in worksheet module (Right click tab name, view code)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, rng, val
If Intersect(Target, Columns("B")) Is Nothing Or Target.Count > 1 Then Exit Sub
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A2:B" & lr).Value
val = Target.Offset(, -1).Value
For i = 1 To UBound(rng)
    If rng(i, 1) = val Then rng(i, 2) = Target.Value
Next
Range("A2:B" & lr).Value = rng
End Sub

 

[mergim]

Suppose "Fruit" is in column A, from A2, "Scoring" is from B2
This code is placed in worksheet module (Right click tab name, view code)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, rng, val
If Intersect(Target, Columns("B")) Is Nothing Or Target.Count > 1 Then Exit Sub
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A2:B" & lr).Value
val = Target.Offset(, -1).Value
For i = 1 To UBound(rng)
    If rng(i, 1) = val Then rng(i, 2) = Target.Value
Next
Range("A2:B" & lr).Value = rng
End Sub

Hello again,

Very nice coding. I have another issue now. My columns are placed differently compared to the example with fruits. Right now, I have my values in Column E (fruit column) and input column in column O (Scoring). How do I adapt the coding ?

 

[bebo021999]

Try again:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, c1, c2, val
If Intersect(Target, Columns("O")) Is Nothing Or Target.Count > 1 Then Exit Sub
lr = Cells(Rows.Count, "E").End(xlUp).Row ' last used row of column E
c1 = Range("E2:E" & lr).Value ' fruit range
c2 = Range("O2:O" & lr).Value ' Scoring range
val = Target.Offset(, -10).Value ' fruit name of input scoring (-10 = count back from column O to E)
For i = 1 To UBound(c1)
    If c1(i, 1) = val Then c2(i, 1) = Target.Value
Next
Range("O2:O" & lr).Value = c2
End Sub

 

[mergim]

Try again:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, c1, c2, val
If Intersect(Target, Columns("O")) Is Nothing Or Target.Count > 1 Then Exit Sub
lr = Cells(Rows.Count, "E").End(xlUp).Row ' last used row of column E
c1 = Range("E2:E" & lr).Value ' fruit range
c2 = Range("O2:O" & lr).Value ' Scoring range
val = Target.Offset(, -10).Value ' fruit name of input scoring (-10 = count back from column O to E)
For i = 1 To UBound(c1)
    If c1(i, 1) = val Then c2(i, 1) = Target.Value
Next
Range("O2:O" & lr).Value = c2
End Sub

Hello

It doesn't seem to work... When I put in a number, nothing happens.

 

[mergim]

Hello

It doesn't seem to work... When I put in a number, nothing happens.

The first one did work with column A and B

 

[mergim]

Hello

It doesn't seem to work... When I put in a number, nothing happens.

Hello,

I managed to figure it out! Thnaks for a very nice coding! have a nice day