How to display whole numbers from list in separate column

[arthurklein43]

Hello
I have a question how to pick and display only the whole numbers (integers) to separate column.
For example i have a few hundreds numbers in column A. some of them are integers and I need them to be displayed in order in separate column let say column C so i can see them separate.
ex. column A has numbers 1.11;1.52;2;2.63;3;3.4521. I need integer numbers 1;2 and 3 to be picked from that list and displayed in column C in order they are 1;2;3
Thank you everyone in advance for the help.

 

[Peter_SSs]

i have a few hundreds numbers in column A. some of them are integers and I need them to be displayed in order in separate column let say column C so i can see them separate.
ex. column A has numbers 1.11;1.52;2;2.63;3;3.4521. I need integer numbers 1;2 and 3 to be picked from that list and displayed in column C in order they are 1;2;3

You say some of them are integers and you want those integers extracted into another column. I can't see the integer 1 in your sample data. How does it get to be in column C?

When you say you want them "in order", do you mean in numerical order, or the order that they appear in column A?

Can an integer appear more than once in column A? If so, does it get listed more than once in column C?

 

[arthurklein43]

Oops. my bad. I missed the 1.
I meant in order the way they appear in column A
I dont think there will be duplicates but even if there are they can appear in column C as much as they are integers. For me it is important to see all integers no matter if duplicates or not. Thank you so much for the quick response

 

[Fluff]

How about

Sub FindInt()
   Dim ary1 As Variant, ary2 As Variant
   Dim Itm As Variant
   Dim i As Long
   
   ary1 = Range("A2", Range("A" & Rows.Count).End(xlUp))
   ReDim ary2(1 To UBound(ary1))
   For Each Itm In ary1
      If Itm = Int(Itm) Then
         i = i + 1
         ary2(i) = Itm
      End If
   Next Itm
   Range("C2").Resize(i).Value = Application.Transpose(ary2)
End Sub

 

[Dave Patton]

Excel 2010
	A	B
2	1	1
3	1.1
6b

Cell Formulas
Range	Formula
B2	=(MOD(A2,1)=0)*A2
B3	=(MOD(A3,1)=0)*A3

Set the workbook to hide 0 or revise the formula to show blank.

 

[Dave Patton]

Excel 2010
	A	B	C	D
2	2.63
3	1	1	1	1
6b

Cell Formulas
Range	Formula
B2	=(MOD(A2,1)=0)*A2
B3	=(MOD(A3,1)=0)*A3
C2	=IF(MOD(A2,1)=0,A2,"")
C3	=IF(MOD(A3,1)=0,A3,"")
D2	=IF(MOD(A2,1),"",A2)
D3	=IF(MOD(A3,1),"",A3)

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub FindInt()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 2) = Evaluate(Replace("IF(INT(@)=@,@,"""")", "@", .Address(0, 0)))
    .Offset(, 2).RemoveDuplicates 1, xlNo
  End With
  Range("C2").Delete xlShiftUp
End Sub[/td]
[/tr]
[/table]

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Numbers​

[/TD]
[TD][/TD]
[TD]

Integers​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1,11​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1,52​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

2,63​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

3,4521​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in C2 copied down
=IFERROR(INDEX(A$2:A$7,SMALL(IF(INT(A$2:A$7)=A$2:A$7,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(C$2:C2))),"")
Ctrl+Shift+Enter

M.

 

[arthurklein43]

Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]2.63[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

6b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=(MOD(A2,1)=0)*A2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(MOD(A2,1)=0,A2,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(MOD(A2,1),"",A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=(MOD(A3,1)=0)*A3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=IF(MOD(A3,1)=0,A3,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=IF(MOD(A3,1),"",A3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi
Thank you for your response. are these 3 formulas alternatives and I can use either one or they need to be used all 3.
I havent tried them yet but looks like they display the integer number in the same row where the original is. but the problem here is if I have a long column of numbers I still have to scroll down to see each number.
My whole point was to display them in separate column in consecutive cells so i can see them easy all together. Sorry I should of be more clear. Any way we can do this. thanks

 

[arthurklein43]

Thank you so much. this is what i need. You guys rock