Copying certain values from one column into another column

[Eddie Morris]

Dear Experts,

I hope you can provide me with a solution to the following problem I have.

Column A contains both whole numbers and non-whole numbers (decimals). I
wish to write some simple code to tell excel to copy cells that contain whole
numbers only from column A into another column B located elsewhere on the
sheet.
For example, Column A may have whole numbers in every third or seventh cell
(Random). I would like these values to be transferred to column B in order as
they appear.

How do I approach this problem? I understand that I may have to use the IF
function, however I do not know how to implement it.

Any information will be greatly appreciated. Looking forward to hearing from
you.

Kind regards

Eddie

 

[Max]

Assume source data (numbers) in A2 down

In B2:
=IF(A2="","",IF(ISNUMBER(SEARCH(".",A2)),"",ROW()))
Leave B1 blank

In C2:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Select B2:C2, copy down to cover the max expected extent of data in col A.
Minmize/hide away col B. Col C will return the required results, all nmeatly
bunched at the top

 

[Eddie Morris]

Hi Max,

Thank you for your quick response, that was a useful tip although I am not
quite sure I understand the code.

Could you explain what you have done?

Thanks Eddie

 

[Max]

In B2:

=IF(A2="","",IF(ISNUMBER(SEARCH(".",A2)),"",ROW()))
Leave B1 blank

Col B is the criteria col, which flags col A where col A does not contain
decimals with arb row numbers (ie where the search for the "." is positive,
to return blanks: "", if not return the arb row number). These arb row
numbers will be read by SMALL in col C. The refrain to leave B1 blank is to
ensure that the arb row numbers returned in B2 down will not be "disrupted" &
prevent a mess-up in col C.

In C2:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))

SMALL(B:B,ROWS($1:1)) uses an incrementer part: ROWS($1:1) to return the
smallest number in col B in the top cell, C2. When copied down, the ROWS(...)
part of it will return the series: 1,2,3,4,... hence SMALL(B:B,ROWS(...))
will return the 2nd smallest number in C3, the 3rd smallest in C4, and so on.
These numeric returns from SMALL(B:B,ROWS(...)) are then passed to INDEX(A:A
to yield the required results from col A.

The front IF part: IF(ROWS($1:1)>COUNT(B:B),"",
serves to "suppress" the ugly #NUM errors which would appear otherwise, once
all the returns are exhausted in the copy down.

 

[Eddie Morris]

Hi Max,

Thanks you for your swift response. That is certainly clearer to me. I also
need to do an additional task which is the following.

For every whole number that is assigned to col C, I would like to copy 2
other values (also whole numbers) from different columns but in same rows
that are associated with this value. For example:

To copy the values in B5, C5, and D5 (also whole numbers) and place them
along side E5 which was assigned the desired value as per your code. So now,
the desired arrangement is the following

E5=desired whole number using code, F5=value copied from B5, G5=value
copied from C5 and H5=value copied value from D5.

I hope I have clarified my problem sufficiently? As you can see I m not
very familiar with Excel programming, so I am on a big learning curve.

Any information will be greatly appreciated.

Kind regards

Eddie

 

[Max]

Eddie,

See whether this sample reflects what you have, and your intents:
http://www.freefilehosting.net/download/3cbi6
Copy integers to new col.xls

(If it helped, click the "Yes" button below from where you're reading this)

Set-up in the sample:
Source data assumed in cols A to D
Key col = col A, where only whole numbers are to be extracted (ignore
decimals)
Cols B to D are ancillary cols to be extracted together with the key col A

In I2:
=IF(A2="","",IF(ISNUMBER(SEARCH(".",A2)),"",ROW()))
Leave I1 blank

In E2:
=IF(ROWS($1:1)>COUNT($I:$I),"",INDEX(A:A,SMALL($I:$I,ROWS($1:1))))
Copy E2 to H2. Select E2:I2, copy down to cover the max expected extent of
data. Cols E to H returns the required results. Min/hide col I.

 

[Eddie Morris]

Hi Max,

Thanks again for your response. That is exactly what I am looking for! Its
great to have online help like this since I am not a regular Excel user.

Kind Regards

Eddie

 

[Max]

Welcome, Eddie. Good to hear that.