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....
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

1​

[/td][td][/td][td]

7​

[/td][td]

14​

[/td][td]

20​

[/td][/tr]

[tr][td]

2​

[/td][td]

2​

[/td][td][/td][td]

7​

[/td][td]

14​

[/td][td]

20​

[/td][/tr]

[tr][td]

3​

[/td][td]

3​

[/td][td][/td][td]

1​

[/td][td]

8​

[/td][td]

15​

[/td][/tr]

[tr][td]

4​

[/td][td]

4​

[/td][td][/td][td]

2​

[/td][td]

9​

[/td][td]

16​

[/td][/tr]

[tr][td]

5​

[/td][td]

5​

[/td][td][/td][td]

3​

[/td][td]

10​

[/td][td]

17​

[/td][/tr]

[tr][td]

6​

[/td][td]

6​

[/td][td][/td][td]

4​

[/td][td]

11​

[/td][td]

18​

[/td][/tr]

[tr][td]

7​

[/td][td]

7​

[/td][td][/td][td]

5​

[/td][td]

12​

[/td][td]

19​

[/td][/tr]

[tr][td]

8​

[/td][td]

8​

[/td][td][/td][td]

6​

[/td][td]

13​

[/td][td]

20​

[/td][/tr]

[tr][td]

9​

[/td][td]

9​

[/td][td][/td][td]

7​

[/td][td]

14​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

13​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

15​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]

16​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

17​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]

19​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

20​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
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