Excel If statement in increments of 4 (1 Viewer)

[USMCFROSTY]

I want to use an if statement but in increments of 4.
The situation is this:
I have 400 rows. Row 1 is a # row 234 are text, row 5 is a # ect…. I wrote a if statement for each #-(My if statement is set up to say if the # is a 1 to mark it as "A" if a 2 "B" if a 3"C" if a 4"X"). But I only want to look at every 4 row which are the #'s. The problem is that i only want this statement to apply to every 4th cell going down the column. If I write It once then drag it down it doesn’t work. I think I have to have a nested statement within my If statement but have no idea how.

Help plz

 

[rosito]

Im not sure if I ubderstand right...but where is prob?
=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"X",""))))...drag down..

 

[USMCFROSTY]

I forgot to mention that i am taking information from one sheet to another. The information on the first sheet has merged cells and no real pattern so it would be helpful if i could find a formula to just look at every 4th row without having to drag. Hard to explain I am trying offset now but no luck. My real problem is creating a formula that will look at just increments.

 

[unmarkedhelicopter]

Can you give us an example of what the row numbers are and what column ?
e.g. C6, C10, C14, C18 ... etc.
What do you mean you want to "look at them" ??? add, average, max, min, what ???
You really should not use merged cells. In 99.9% of cases you can get away with centre across selection instead. Merged cells are a nightmare.
When you say you have 400 rows, is that fixed ? is it about 400 +/- ? will it grow ?

 

[USMCFROSTY]

Their are 400 rows which i seperated to 200 each. I am looking at sheet 1 cells a1, a5, a9, ect. Some of the cells are merged (Not my spreadsheet i just analyse it) Because of the merges i can''t drag and if i unmerge the cells it creates new problems. I want to take information from sheet one and apple it to sheet 2 using the "IF". No sum or anything. I need to take the every 4th from sheet one and apply it to sheet two. The easy way to explain was How do i write a formule to nest inside my "IF" that just looks at every 4th row which would by using the "IF" put the correct letter in each column in sheet 2. I've tried offset but no luck
tks

 

[unmarkedhelicopter]

We're nearly there, "apple it" ?
Why using "if" what does "if" give you ? if what ?
How about just getting all those values dragged down on to sheet 2
i.e. A1 > A1, A5 > A2, A9 > A3 etc.

 

[Brianwarnock]

I don't think you can do this by a formula, I think you are going to need to write code to scan down one sheet incrementing by 4 and the other by 1.
Sorry I don't have time at the moment to to this.

Brian

 

[unmarkedhelicopter]

I don't think you can do this by a formula, I think you are going to need to write code to scan down one sheet incrementing by 4 and the other by 1.
Sorry I don't have time at the moment to to this.

Brian

No, I am confident you can do this by formula.
Admittedly, I'll cheat, but then that's how I get the results

 

[USMCFROSTY]

Scan vs Formula

Either one i'll try just give me some search tips when i use the help section.
Tks

 

[Brianwarnock]

OK you need to do something like this

Sub copy4throw()
Dim x As Integer
Dim y As Integer
Dim lngLastRow As Long
With ActiveSheet.UsedRange
lngLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With
x = 1
y = 1
Application.ScreenUpdating = False
Sheets(1).Select
Do
If Cells(x, colindex).Value = "yourcriteria" Then   'Caps sensitive
Cells(x, 2).EntireRow.Copy
Sheets(2).Select
Cells(y, 1).Select
ActiveSheet.Paste
Sheets(1).Select
y = y + 1
End If
x = x + 4

Loop Until Cells(x, 2) = ""
Application.ScreenUpdating = True

End Sub

Brian

 

[unmarkedhelicopter]

Either one i'll try just give me some search tips when i use the help section.
Tks

So you are just gonna ignore the questions I left you ?

 

[USMCFROSTY]

So you are just gonna ignore the questions I left you ?

I didn't see any Q?

 

[unmarkedhelicopter]

We're nearly there, "apple it" ?
Why using "if" what does "if" give you ? if what ?
How about just getting all those values dragged down on to sheet 2
i.e. A1 > A1, A5 > A2, A9 > A3 etc.

Do you see the little "?" symbols ?

 

[USMCFROSTY]

If turns the # into a letter which i need for codes.

 

[Brianwarnock]

Thinking about it you should use a Select Case rather than nested ifs in the code.
Mind you you haven't commented on the code.

Brian

 

[USMCFROSTY]

Im not familiar with select case but will look it up

 

[DanG]

I'm not sure I understand everything with this but to identify every 4th row can't you do... =mod(row(),4)?

 

[USMCFROSTY]

I tried it but it still goes in increments of one?

 

[unmarkedhelicopter]

If turns the # into a letter which i need for codes.

Eh ! You don't need the letters so why the if's and what about the other questions ? (apple and if just the value list is ok ?)

 

[unmarkedhelicopter]

Assuming apple is just flowery decoration and if's are irellevant and that the values are okay then :- (see attached for example =INDIRECT("Sheet1!A"&ROW()*4-3))

Alternatively I could do it with an array but that would slow your spreadsheet down ... alot ...