Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
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,R OWS($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eddie Morris" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
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" wrote: 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,R OWS($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eddie Morris" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
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,R OWS($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eddie Morris" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
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" wrote: 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,R OWS($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eddie Morris" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eddie Morris" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
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" wrote: 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eddie Morris" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying certain values from one column into another column
Welcome, Eddie. Good to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eddie Morris" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying the values in a row to column | Excel Discussion (Misc queries) | |||
Text to column and selecting values based on a different column | Excel Worksheet Functions | |||
Text to column and selecting values based on a different column | Excel Worksheet Functions | |||
Copying cells with similar column values | New Users to Excel | |||
To copy values in a column relevant to text in an adjacent column? | Excel Worksheet Functions |