Excel – how to modify a cell value based on another cell value


A       B      C 
aa1     b1     ac1
aa2     b2     bd2
aa3     b3     ae3
aa4     b1     bc4
aa5     b2     ad5
aa6     b3     be6

now in the above sheet, i need to change the value like this..

A       B      C 
xy1     b1     ac1
aa2     b2     bd2
aa3     b3     ae3
aa4     b1     bc4
aa5     b2     ad5
pq6     b3     be6

as shown in the above part, based on the cells in the column "C" i need to change values in the column A for each cells of it..

Kindly help me…

Best Solution

You need to specify a formula in first cell of A and then simply propagate it across all the column.

  1. In column A, select the cell with value "aa1"
  2. Press F2 to edit that cell
  3. Type =CONCATENATE("a",$C1) and press Enter
  4. Press Ctrl+C to copy the cell you just edited
  5. With the mouse (or with keys shift+down) select the next two cells in column A (aa2 and aa3)
  6. Press Ctrl+V

In VBA, you need something like this:

Sub Button1_Click()
    For Each cell In ActiveSheet.Range("A1:A3").Cells
        cell.Value = "a" + ActiveSheet.Cells(cell.row, 3)
End Sub

You need to specify the range you want to change, in this case from A1 to A3 and then simply change the value for each cell as the concatenation of "a" with the cell value on same row but column 3 (C)

Related Question