If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
comparing more then one column with numbers and letters
I have 6 columns and many rows in a spreadsheet i want to create a formula
that looks at two common columns and if the same replace a column with an amount from another column. Below is sort of an example of what I mean: column a column b column c column d column e column f acc50138 ACCCS20-1 6.14 acc50138 acccs20-1 10.00 acc50140 Prevention 31.05 acc50269 RTU 15.00 acc50379 concentrate 5.00 acc50379 concentrate 5.00 so what i need is the formula to look at column a and d if they are the same then replace column c with the amount of column f. Any suggestions? |
#2
|
|||
|
|||
comparing more then one column with numbers and letters
You can put this in G2:
=IF(A2="","",IF(ISNA(MATCH(A2,D,0)),C2,INDEX(F:F ,MATCH(A2,D,0)))) Copy down to return required results. Then copy col G and overwrite col C with a paste special as values. Clear col G. You're done in about 10-15 secs. Inspiring? hit the YES below -- Max Singapore --- "CherylK" wrote: I have 6 columns and many rows in a spreadsheet i want to create a formula that looks at two common columns and if the same replace a column with an amount from another column. Below is sort of an example of what I mean: column a column b column c column d column e column f acc50138 ACCCS20-1 6.14 acc50138 acccs20-1 10.00 acc50140 Prevention 31.05 acc50269 RTU 15.00 acc50379 concentrate 5.00 acc50379 concentrate 5.00 so what i need is the formula to look at column a and d if they are the same then replace column c with the amount of column f. Any suggestions? |
Thread Tools | |
Display Modes | |
|
|