Adding Values Based On Matching Cells

americantuna

New Member
Joined
May 8, 2019
Messages
11
Hello,

I am trying to create a salary table to upload to a fantasy sports site. In order to do this quickly, I need to find a way (if it's possible) to automatically fill in values from one list to another.

In the image attached, I would like to compare Column C and Column J. If a name in C matches a name in J, I would like to copy the value of Column M to that name in Column F.

For example, in the image, Justin Abdelkader is matched in C7 and J1. I would like to take the value listed in M1 (the new salary) and have it automatically copy to cell F7 (overwriting the old salary).

**NOTE: At no time can the values in Columns A thru E be changed, otherwise the file cannot be uploaded.

I have a feeling I've done this a long time ago, but completely forgot how to do it. If this is possible, any help would be appreciated.

EDIT: These two tables are on the same sheet. I don't know if that matters, but if they need to be on separate sheets to make this work, let me know.

voESt6N.jpg
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Does this do what you want...

Code:
Sub test()
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub
 
Upvote 0
Does this do what you want...

Code:
Sub test()
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub

PERFECT!

I'm not a code guy, so all of that looks like gibberish to me. But I know how to input it and that's all that matters.

Thank you!
 
Upvote 0
You are welcome. I was happy to help. There is not much to the code. I put some comments in to give you an idea of what is happening...

Code:
Sub test()
    'Declare the variables
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    
    'Create two ranges that are column C as one range and column J as the second
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    
    'Using a nested loop, compare each value in column C to each value in column J
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                'If a match is found, make the value of the cell 3 columns to the
                'right of the cell in column C = to the value of the cell 3 columns
                'to the right of the matching cell in column J
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub
 
Upvote 0
You are welcome. I was happy to help. There is not much to the code. I put some comments in to give you an idea of what is happening...

Code:
Sub test()
    'Declare the variables
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    
    'Create two ranges that are column C as one range and column J as the second
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    
    'Using a nested loop, compare each value in column C to each value in column J
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                'If a match is found, make the value of the cell 3 columns to the
                'right of the cell in column C = to the value of the cell 3 columns
                'to the right of the matching cell in column J
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub

Thank you, again.

I keep a cheatsheet at my desk and put these types of things in it for future reference. Makes things so much easier.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top