Column shrinking

[awd319]

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

 

[onlyadrafter]

Hello,

=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!

 

[melone]

Add an additional column to keep track of the cells (1,2,3,4,etc.), then perform modulus arithmetic on it (%4) and use it in an "IF" statement.

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," &quot"

 

[bpeirson]

Try this. In column B fill in a repeating series 1,2,3,4,1,2,3,4 as you go down to the bottom, in column C fill in a full series 1,2,3,4,5,6..n for the entire list in column A. This column C series will help you keep the data in correct order in the following steps.

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.

 

[jghrist]

If the numbers are in A1:A21, enter:

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

 

[awd319]

Thanks for the replies so far.

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.

 

[joerd]

From thread770-79067 it was suggested to use the data analysis tool. Did you try this?

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[awd319]

Spot on Joerd, Thanks!

 

[jghrist]

Or, with data in A1:A21, enter:

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.