Column shrinking
Column shrinking
(OP)
If I have a massive column of data, How do I form a new smaller column consisting of, say, every fourth cell?
i.e, If column A consists of cells containing the numbers 1,2,3,4,5,6,7,8,9
I want column B to be 1,5,9 by referencing A1,A5 and A9 in incremental steps.
Thanks
i.e, If column A consists of cells containing the numbers 1,2,3,4,5,6,7,8,9
I want column B to be 1,5,9 by referencing A1,A5 and A9 in incremental steps.
Thanks





RE: Column shrinking
=LEFT(A1,2)&MID(A1,9,2)&MID(A1,17,2)
will work for the given example, but will encounter problems if cell = 10,11,12,13,14,15,16,17,18 or any of the digits being bigger than 9.
You could always do TEXT TO COLUMNS, then create a CONCATINATING formula
e,g, If A1 = 1,2,3,4,5,6,7,8,9
then do TEXT TO COLUMNS, delimited, check COMMA, DESTINATION C1, then in AA1 enter this formula
=C1&","&G1&","&K1
----------------------------------
Hope this helps.
----------------------------------
maybe only a drafter
but the best user at this company!
RE: Column shrinking
If column A contains the data, Column B contains 1,2,3..., Column 3 contains "Mod{A1,4)", Column D contains "If(C1=0,A1," ")"
RE: Column shrinking
Select all the rows with your data and sort ascending by column B first and column C second. Do this sort in one action using the sort criteria provided by excel.
Every fourth cell in the original list had a number 1 in column B. Now all the 1's in column B will be grouped together along with the appropriate number that was beside it in columns A & C. Column C insures that all data are still in the same order as the original list.
RE: Column shrinking
B1: 1
B2: =B1+4
copy down 5 rows (last row should be 21)
C1: =INDEX($A$1:$A$21,B1,1)
copy down 5 rows
RE: Column shrinking
Our own solution uses a very similar solution to jghrist, but using OFFSET rather than INDEX. Unfortunately, like most solutions, this involves creating a "dummy" column to do the counting. Can anyone come up with some arithmatic within the OFFSET (or INDEX) function to avoid the use of dummy columns?
Thanks again.
RE: Column shrinking
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Column shrinking
Spot on Joerd, Thanks!
RE: Column shrinking
B1: =INDEX($A$1:$A$21,(ROW(A1)-ROW(A$1))*4+1,1)
copy down 5 rows
The Data Analysis Sampling solution is easier, though.