1

I am trying to create a list of only whole numbers from another list. Suppose my range list is:

enter image description here

I want the results to appear as:

enter image description here

I want the solution to work in excel 2019 only. Any help would be appreciated, thank you.

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
MochaLatte
  • 13
  • 2

2 Answers2

2

If you have office 365 you could use =FILTER(A1:A6,INT(A1:A6)=A1:A6) For older versions: =IFERROR(INDEX($A$1:$A$6,SMALL(IF(INT($A$1:$A$6)=$A$1:$A$6,ROW($A$1:$A$6)),ROWS($1:1))),"") this should be entered withctrl+shift+enter

P.b
  • 8,293
  • 2
  • 10
  • 25
  • The formula seems to work well in an empty worksheet, however I get a different result if the formula is in a workbook that has other tables in it. – MochaLatte Jul 29 '21 at 01:10
  • If the range starts at a different row than the first row you need to subtract the number of rows from row 1 to the first row number in your range. For instance if you have Range `$A$6:$A$11` then you need to use `=IFERROR(INDEX($A$6:$A$11,SMALL(IF(INT($A$6:$A$11)=$A$6:$A$11,ROW($A$6:$A$11)-5),ROWS($1:1))),"") I also noticed a typo in my original formula which I corrected – P.b Jul 29 '21 at 06:20
0

So use MOD(): enter image description here

Or, you can return the number: enter image description here

And to make a list without spaces, you can use LARGE() or UNIQUE() whatever works for you, even sorting may work if you don't expect automatic.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • Thank you for your response! Ideally it should all be automated. How would the formula look like without spaces using the LARGE() or UNIQUE() function? Thanks again for your help. – MochaLatte Jul 28 '21 at 21:43
  • 1
    @MochaLatte I will let you play and learn since you have now unaccepted my answer. – Solar Mike Jul 28 '21 at 21:49