CODEWITHSUNDEEP
excel
dave111
dave111

Reputation: 305

Excel - Replace blank if only 1 contiguous value

Okay this one is a bit strange to explain, but I have a file like this:

     A       B
1 | Person         
2 | Person  test
3 | Record  
4 | Record  test
5 | Tiger
6 | Scott
7 | Scott   test
8 | Scott   test
9 | Scott   test

As you can see, in column A the first row where a new value starts, column B is blank. What I need is to fill in a placeholder (NULL) value into column B if there is only one contiguous value in column A. So for the above example row 5 only has 1 contiguous value so the result should look like this:

     A       B
1 | Person         
2 | Person  test
3 | Record  
4 | Record  test
5 | Tiger   (NULL)
6 | Scott
7 | Scott   test
8 | Scott   test
9 | Scott   test

I tried something like this but doesn't work:

IF(EXACT($A5,$A4)=FALSE AND EXACT($A5,$A6)=FALSE, "(NULL)", $B5)

I just want a formula I can paste all the way down column B. Any suggestions?

Upvotes: 0

Views: 42

Answers (2)

user3896255
user3896255

Reputation:

=IF(OR(A2=A1,A2=A3),"Test","(NULL)")

Replace Test and (NULL) with the values you want to display.

This checks to see if the AX = A(X-1) or A(X+1). If either of those is true, then it means that AX is contiguous with another row.

I should note that I tested this with Excel 2013.

Also, if you want the formula to use the value already in BX when the rows are contiguous, then you can't do that in the same column.

I'm not sure if that's what you're asking to do, but the formula you tried would have been attempting to do that, creating a circular reference.

You can easily do this in column C. Replace "Test" with BX. Then, if you only want 2 rows in the end, you can copy and paste the values of column C into column B, then delete column C. But, this way you'll lose your formula.

Upvotes: 2

L42
L42

Reputation: 19727

Suppose you have your data like this:

enter image description here

This formula seem to work:

=IF(OR(A2=A1,A2=A3),IF(B2="","",B2),"(NULL)")

Upvotes: 0

Related Questions