If cell equals X THEN copy and paste in another row
Hi there,
I was hoping someone could help me figure out the code for this in excel or VB.
I have data that looks like the following:
Column A____Column B___Column C
1_____+/+ _____ 10
2_____+/y _____ 12
3_____+/x ______9
4_____+/y ______13
What I could like to do is say IF a cell in column A equals +/+, THEN copy and paste the corresponding value from column B into column C. (ie. since A1 equals +/+, B1 would be copied and pasted into C1). This is so I can average out the numbers that are in column C eventually.
I would also like to do the same thing for +/y and +/x into column d and column e.
I have Excel 2007.
If you guys could give me the correct code - either in an excel cell or in a visual basic module, that would be appreciated.
Thanks
RE: If cells equals...
Hello,
Instead of Copy/Paste all values you can use follow excel formula:
= AVERAGEIF(A1:A4, "+/+", B1:B4)
= AVERAGEIF(A1:A4, "+/x", B1:B4)
= AVERAGEIF(A1:A4, "+/y", B1:B4)
Enter this formula in three different cells by your choice. In example you post they will return:
10
9
12.5
If there is no values that match your criterion you get #DIV/0! error, which you can conceal, for example, with this:
= IFERROR(AVERAGEIF(A1:A4, "+/x", B1:B4), "There is no values to evaluate.")
One more thing...
If your data range intend to extend you can use dynamic range to avoid frequently changes of your formula:
= AVERAGEIF(AverageRange, "+/+", ActualAverageRange)
Select Column A and in Name Box enter AverageRange or what name you desire. Do same for Column B and ActualAverageRange (if you know this, sorry for my detailed explanation).
Please excuse me for my English.
Best regards.
It works perfectly. Thank
It works perfectly. Thank you!