Selecting and copying a value, but if cell blank go to next cell

marc01

Board Regular
Joined
Sep 17, 2018
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

Having a slight issue in trying to achieve what I would like with the following data:
[TABLE="width: 309"]
<tbody>[TR]
[TD][TABLE="width: 373"]
<tbody>[TR]
[TD][/TD]
[TD]Federer Serving[/TD]
[TD]Djokovic Serving[/TD]
[TD]Winner[/TD]
[TD]Error[/TD]
[/TR]
[TR]
[TD]Shot type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unforced error[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unforced error[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unforced error[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unforced error[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unforced error[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unforced error[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unforced error[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Unforced error[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Unforced error[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Unforced error[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winner[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
I would like to first check if the shot type is a winner or an error. If it is a winner, then copy and paste the value in column B into the Winner column(D), and put #NA () in the Error column. If the shot type was error then the value is copied in to the error column(E), and #NA () in to the Winner column (D).

I had thought I had achieved this with the following functions:

For column D
IF(A3="Winner",B3,NA())
For column E
IF(A3="Error",B3,NA())

However, this won't work when the server changes and the value shifts to being in column C.
<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Can anyone help? (sorry I hope this makes sense!)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
For column D

=IF(B3<>"",IF(A3="Winner",B3,"#NA()"),IF(A3="Winner",C3,"#NA()"))

For column E

=IF(B3<>"",IF(A3="Unforce error",B3,"#NA()"),IF(A3="Unforce error",C3,"#NA()"))
 
Upvote 0
Hi,

Is this what you mean:


Book1
ABCDE
1Shot typeFederer ServingDjokovic ServingWinnerError
2Winner11#N/A
3Unforced error0#N/A0
4Winner11#N/A
5Unforced error0#N/A0
6Winner11#N/A
7Winner00#N/A
8Winner-1-1#N/A
9Winner00#N/A
10Unforced error-1#N/A-1
11Winner00#N/A
12Unforced error-1#N/A-1
13Unforced error0#N/A0
14Unforced error-1#N/A-1
15Unforced error-2#N/A-2
16Winner-3-3#N/A
17Unforced error-2#N/A-2
18Winner-1-1#N/A
19Winner00#N/A
20Unforced error1#N/A1
21Winner22#N/A
22Winner33#N/A
23Winner44#N/A
24Winner33#N/A
25Winner44#N/A
26Unforced error5#N/A5
27Winner44#N/A
Sheet394
Cell Formulas
RangeFormula
D2=IF(A2="Winner",IF(B2,B2,C2),NA())
E2=IF(A2="Unforced error",IF(B2,B2,C2),NA())
 
Upvote 0
Hi both,

Many thanks for your quick replies!

Both methods seem to work which is ideal! From an understanding point of view I can see what the formula from Dante is doing, however can't get my head around IF(B2,B2,C2)…..are you able to help this excel newbie comprehend this?
 
Upvote 0
You're welcome.

IF(B2,B2,C2), for an IF statement, Excel is looking for TRUE/FALSE, any Number Other than 0 is evaluated as TRUE, 0 and Blank as FALSE, so essentially, it's "shorthand" to

IF(B2<>0,B2,C2) or IF(B2<>"",B2,C2)

In your case, if B2 is 0, this part of the formula with return 0 Regardless of B2 or C2 is Blank.
 
Upvote 0
Thanks for taking the time to explain.
I think I get it....in theory when B2 does contain 0, the formula see's the argument as being FALSE (as B2 is actually showing 0) so returns the value in C2 which is blank thus showing 0 as the answer. Is that the correct way of interpreting it?
 
Upvote 0
Yes, that is correct.

If you want the formula to be more "user understandable", you can swap out IF(B2,B2,C2) with IF(B2<>"",B2,C2), then it will return the 0 in B2 (again, given the way your data is, you'll get the correct result either way).
 
Upvote 0
phew....my head was beginning to hurt!
again many thanks for your assistance!:biggrin:
 
Upvote 0
You're welcome, glad to be of assistance.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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