×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Column shrinking

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

RE: Column shrinking

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!

RE: Column shrinking

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," ")"

RE: Column shrinking

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.

RE: Column shrinking

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

RE: Column shrinking

(OP)
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.

RE: Column shrinking

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.

RE: Column shrinking

(OP)

Spot on Joerd, Thanks!

RE: Column shrinking

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources