setting up sequence sets with only 3 repeat once in excel

S

sierra

I have a return data of 12 23 24 25 26 29 31 33 34 43 (example only, the data is much bigger). I need to set up sequence sets of 5 numbers in each set. The sequence sets are very big so I only want 3 number repeat only once in one set, once a sequence has 3 exact number it needs to move to the next sequence. For example:

12 23 24 25 26
12 23 24 29 31 (NO! 3 numbers repeat already on the previous set)

I don't even know how to begin the formula for the Excel 2007. Please help.

thank you very much!!!

EggHeadCafe - Software Developer Portal of Choice
VB : Numeric to Word converter
http://www.eggheadcafe.com/tutorial...24-23ed3b456112/vb--numeric-to-word-conv.aspx
 
J

JLatham

It would really help if you'd also tell us what the correct results you
expect from this sequence are. Also, tell us if your return data is all in a
column, or in a row. Finally, tell us how the sets of 5 are to be displayed
(on rows, in columns, ??)

Assuming that your return set is in column A beginning on row 1, you can
return the set broken up into 5 number sets in other columns this way. This
example starts the first group in column C, row 1
in cell C1 enter this formula:
=OFFSET($A1,(COLUMN()-COLUMN($C$1))*5,0)
extend/fill it down to row 5. Then select C1:C5 and fill it to the right
into columns D, E, F, etc. for as far as it needs to go to pick up all the
numbers in the list in column A.

If that formula/solution doesn't work, we definitely need the information I
asked for in the first paragraph.
 
S

sierra

Nope, the formula didn't works. The values are layout across (not down): 3 4 6 7 8...
And the answer or result sequence set needs to be across also: 3 4 6 7 8

thanks



JLatham wrote:

It would really help if you would also tell us what the correct results
22-Oct-09

It would really help if you would also tell us what the correct results yo
expect from this sequence are. Also, tell us if your return data is all in
column, or in a row. Finally, tell us how the sets of 5 are to be displaye
(on rows, in columns, ??

Assuming that your return set is in column A beginning on row 1, you ca
return the set broken up into 5 number sets in other columns this way. Thi
example starts the first group in column C, row
in cell C1 enter this formula
=OFFSET($A1,(COLUMN()-COLUMN($C$1))*5,0
extend/fill it down to row 5. Then select C1:C5 and fill it to the righ
into columns D, E, F, etc. for as far as it needs to go to pick up all th
numbers in the list in column A

If that formula/solution does not work, we definitely need the information
asked for in the first paragraph

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Design Pattern Interview Questions Part 3
http://www.eggheadcafe.com/tutorial...84-70948cdfa6b2/design-pattern-interview.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top