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

M

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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

G
  • Question Question
Embedded Range or Array Lookup
Replies
5
Views
484
Fluff
Fluff
JenniferMurphy
  • Solved
Help my macro store a value in a cell
Replies
5
Views
489
JenniferMurphy
JenniferMurphy
M
  • Solved
VBA - how to add a copy and paste values on current module
Replies
2
Views
162
Minx_Minxy
M
F
  • Solved
Web search using Excel VBA error and script corrections
2
Replies
10
Views
239
Edgar_
Edgar_
J
  • Solved
Copy /past cells by inputbox on sheet 2 to sheet1 using VBA
2
Replies
12
Views
644
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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