writting an IF command that will skip cells

  • Thread starter Thread starter prem
  • Start date Start date
P

prem

Hi guys this is my problem.

I have serial numbers in my entire A column and dates in my entire B column.

On a separate workskeet, I want to create a command that will check if the
serial number is a multiple of 8. If it is, I want it to return the date the
the corresponding B column/row. I came up with =IF(MOD(A2,8)=0,B2,"NA").

However, I do not want "NA" returned when the A column value is not a
multiple of 8. Instead I want it to check the value in the next A row, and if
that is not a multiple of 8, to keep going till it reaches a row where the
value the data in cell A is indeed a multiple of 8 and return the date from
the coresponding B column/row.

How might I achieve this? Thank you in advance.

Prem
 
Hi

On the second sheet, change your formula to
=IF(MOD(A2,8)=0,B2,"")
This will leave Nulls on the rows where there is no match.
Copy down for as many rows as you have data in column A of sheet 1.
When finished, Sort Sheet2 Column A and all the blank rows will be moved to
the end of the list and your results will be bunched at the top.
 
Hi Roger thank you for the reply. I had already thought of that but I was
hoping to completely skip the null values and only return the ones I want so
that I would not have to sort out the second sheet. So in other words,
instead of =IF(MOD(A2,8)=0,B2,"") I need something to replace the "" (null
value) with some sort of function that will skip to the next serial number
and perform the check.
 
Assuming your serial nos run in A2 down, viz.: 1,2,3 ...
then this might suffice to directly extract the corresp. dates from col B
for serial nos: 8,16,32, etc in col A

In say, C2: =INDEX(B:B,ROWS($1:1)*8+1)
Copy C2 down as far as required
 
Hi Max

I had assumed that the serial numbers needed to divide by 8, not that the OP
wanted the 8th, 16th 24th value etc.
 
Hi Roger,
.. the OP wanted the 8th, 16th 24th value etc.
Click to expand...
Yes, those were my assumptions on the OP's underlying intents
.. the serial numbers needed to divide by 8
Click to expand...
If it was indeed the case that the serial numbers were of a random nature in
A2 down, then I was going to offer this non-array set-up to the OP (upon his
reply)

In E2: =IF(MOD(A2,8)=0,ROW(),"")
with E1 left empty

In F2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(B:B,SMALL(E:E,ROWS($1:1))))
E2:F2 copied down would return required results in col F

---
 
Hi Max,

Thank you for your reply. It works just as it should. I just adapted it for
other columns and its works out too. Thank you again.
 
Hi Max,

My serial numbers were in order and not random in nature. However the code
you provided here could prove useful in other situations. Thank you.

Prem

Max said:
Hi Roger,
.. the OP wanted the 8th, 16th 24th value etc.
Click to expand...
Yes, those were my assumptions on the OP's underlying intents
.. the serial numbers needed to divide by 8
Click to expand...
If it was indeed the case that the serial numbers were of a random nature in
A2 down, then I was going to offer this non-array set-up to the OP (upon his
reply)

In E2: =IF(MOD(A2,8)=0,ROW(),"")
with E1 left empty

In F2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(B:B,SMALL(E:E,ROWS($1:1))))
E2:F2 copied down would return required results in col F

---
Click to expand...
 
Hi guys this is my problem.

I have serial numbers in my entire A column and dates in my entire B column.

On a separate workskeet, I want to create a command that will check if the
serial number is a multiple of 8. If it is, I want it to return the date the
the corresponding B column/row. I came up with =IF(MOD(A2,8)=0,B2,"NA").

However, I do not want "NA" returned when the A column value is not a
multiple of 8. Instead I want it to check the value in the next A row, and if
that is not a multiple of 8, to keep going till it reaches a row where the
value the data in cell A is indeed a multiple of 8 and return the date from
the coresponding B column/row.

How might I achieve this? Thank you in advance.

Prem
Click to expand...

If your serial numbers are randomly entered (or entered with occasional skips
in the sequence), then this array formula, entered into some cell and filled
down until it produces an error, should return the adjacent dates.

SerialNums and Dts are named ranges, but you can substitute any single column
range reference or NAME.

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula:

=INDEX(Dts,SMALL((MOD(SerialNums,8)=0)*ROW(SerialNums),
ROWS($1:1)+SUMPRODUCT(--(MOD(SerialNums,8)<>0))))

--ron
 
Back
Top