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.

 

[arthurklein43]

Hi . thank you
but unfortunately I am not familiar with macros at all.

 

[arthurklein43]

Here is another macro that you can consider...

[TABLE="width: 500"]
<tbody>[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]
</tbody>[/TABLE]

Hi . thank you
but unfortunately I am not familiar with macros at all.

 

[arthurklein43]

Maybe...

	A​	B​	C​
1​	Numbers​		Integers​
2​	1,11​		2​
3​	1,52​		3​
4​	2​
5​	2,63​
6​	3​
7​	3,4521​
8​

<tbody>
</tbody>

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.

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

 

[Marcelo Branco]

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

You are welcome. Glad to help

M.

 

[Rick Rothstein]

Hi . thank you
but unfortunately I am not familiar with macros at all.

If you wanted to give them a try, here are the instructions on how to implement them...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the whichever macro code you want to try (since both macros have the same name, make sure to remove one before copy/pasting thinto the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FindInt) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[arthurklein43]

I apologize for the double posts. got so exited and didnt use reply with quote. New to this forum and still learning . I would like to thanks everyone who spent some time to work on this and specially Marcelo who provided the solution. Thanks guys

 

[arthurklein43]

If you wanted to give them a try, here are the instructions on how to implement them...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the whichever macro code you want to try (since both macros have the same name, make sure to remove one before copy/pasting thinto the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FindInt) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

I appreciate this explanation. I always wanted to learn more how to use macros but never had time and chance to do it. maybe this will give me the little push i need. Thanks

 

[Peter_SSs]

Hmm, a fair bit has happened while I was asleep.
Just to add one more to the mix - similar to Marcelo's but this does not require the Ctrl+Shift+Enter confirmation.

Excel Workbook
	A	B	C
1	Numbers		Integers
2	1.11		2
3	1.52		3
4	2
5	2.63
6	3
7	3.4521
8
List Integers

I know you said you wanted them in the order they appear in column A, but if that wasn't the case, the formula becomes simpler.

Excel Workbook
	A	B	C
1	Numbers		Integers
2	1.11		1
3	1.52		2
4	2		2
5	2.63		3
6	3
7	3.4521
8	1
9	2.2
10	2
11
List Integers (2)

 

[arthurklein43]

Hmm, a fair bit has happened while I was asleep.
Just to add one more to the mix - similar to Marcelo's but this does not require the Ctrl+Shift+Enter confirmation.

List Integers

	A	B	C
1	Numbers		Integers
2	1.11		2
3	1.52		3
4	2
5	2.63
6	3
7	3.4521
8

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91px;"><col style="width:27px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =IFERROR(INDEX(A$2:A$7,AGGREGATE(15,6,(ROW(A$2:A$7)-ROW(A$2)+1)/(MOD(A$2:A$7,1)=0),ROWS(C$2:C2))),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


I know you said you wanted them in the order they appear in column A, but if that wasn't the case, the formula becomes simpler.

List Integers (2)

	A	B	C
1	Numbers		Integers
2	1.11		1
3	1.52		2
4	2		2
5	2.63		3
6	3
7	3.4521
8	1
9	2.2
10	2
11

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91px;"><col style="width:27px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =IFERROR(AGGREGATE(15,6,A$2:A$10/(MOD(A$2:A$10,1)=0),ROWS(C$2:C2)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Peter
Thank you for your respond. I think both of these will work since the numbers in the A column I have are in descending order. looks like the order in result column is in ascending order and this will be the only difference.
Anyway unfortunately I got busy trying to resolve another project and wasted my whole day off today trying to do it without asking for help. This clearly was a mistake since there is a great community over here willing to help.
I will post my new question in separate post since it is not related to this one. Thank you again.

 

[Peter_SSs]

Thank you again.

You are welcome.