How to copy values from one column into other columns using if then statements

[clairebeginner]

Hi,

I'm an excel formula newbie and was having trouble trying to figure out the correct formula to do the following:

In Column A I have a list of various values ranging from 0-4000. I'd like to reorganize them (or recopy them) into 3 columns delineating the values into certain ranges based off of size (i.e. one column for smaller values (between 0-200), one column for medium values (between 201-999), and one column for large values (greater than 1000). What would be the correct if then statement to achieve this?

I would appreciate anyone's help on this. Thanks!

 

[FDibbins]

This a bit long-winded, but gives you what you want...AS LONG AS you want your data extracted in numerical sequence . I created a small sample table, then split it into 3....

	A​	B​	C​	D​	E​
1​	1​		7​	14​	20​
2​	2​		7​	14​	20​
3​	3​		1​	8​	15​
4​	4​		2​	9​	16​
5​	5​		3​	10​	17​
6​	6​		4​	11​	18​
7​	7​		5​	12​	19​
8​	8​		6​	13​	20​
9​	9​		7​	14​
10​	10​
11​	11​
12​	12​
13​	13​
14​	14​
15​	15​
16​	16​
17​	17​
18​	18​
19​	19​
20​	20​

C2:E2 contains the top of each range that you want
C1=RANK(C2,$A$1:$A$20,1) copied across

C3=IF(ROWS($A$1:A1)+B$2<=C$2,SMALL($A$1:$A$20,ROWS($A$1:A1)+B$2),"")
copied down and across as needed

 

[My Aswer Is This]

Try this:

Sub CopyValue()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrob As Long
Dim Lastroc As Long
Dim Lastrod As Long

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Cells(Rows.Count, "B").End(xlUp).Row
Lastrowc = Cells(Rows.Count, "C").End(xlUp).Row
Lastrowd = Cells(Rows.Count, "D").End(xlUp).Row


    For i = 1 To Lastrow

    If Cells(i, 1).Value > 0 And Cells(i, 1) < 200 Then Cells(i, 1).Copy Destination:=Cells(Lastrowb, 2): Lastrowb = Lastrowb + 1
    If Cells(i, 1).Value > 199 And Cells(i, 1) < 1001 Then Cells(i, 1).Copy Destination:=Cells(Lastrowc, 3): Lastrowc = Lastrowc + 1
    If Cells(i, 1).Value > 1000 Then Cells(i, 1).Copy Destination:=Cells(Lastrowd, 4): Lastrowd = Lastrowd + 1
    
    Next
Application.ScreenUpdating = True
End Sub