Non-Sequential Row Problem

I would like to pull data from a range of cells, but cells are irregular in sequence. I would like to pull data from column A into sequential rows in Column B.

A3
A4
A6
A7
A11
A12
A14
A15
A19
A20
A22
A23
A27
A28
A30
A31
A35
etc..

Also in Column C, I would like to extract data from Column A in this irregular sequence:

A5
A8
A13
A16
A21
A24
A29
A32
etc...

Will need to drag a lot of date from A Column into Columns B and C respectively. I'm trying to avoid coding each individual cell. Is there a way to do this? Thanks in advance.

The number sequences might look irregular, but careful examination of the first one reveals that the difference between the row numbers is 1,2,1,4, and then this sequence repeats, so you have 1,2,1,4,1,2,1,4, ... and so on. For the second set of row numbers the differences between them follow this sequence: 3,5,3,5,3,5, ... and so on.

So, what you need to be able to do is generate these sequences as a formula is copied down a column, and then you can establish the appropriate row numbers. These can then be used with an INDEX function to extract the data from the appropriate cells - in a similar way as I showed you with your earlier query.

As a start, to help you, you can use this formula:

=MOD(ROWS($1:1)-1,4)+1

When you copy this down a column you get the sequence 1,2,3,4,1,2,3,4,1,2,3,4, etc., so you need to tweak this a bit so that it generates 1 instead of 3 to get the sequence that you need.

I'm just about to go to bed now, so I'll check back here tomorrow to see how you got on.

Hope this helps.

Pete

I had no luck with that. I tried tweaking your original INDEX FUNCTION from my first query, but to no avail. Actually, I just need to be able to copy this sequence down adjacent B column:

=A3
=A6
=A11
=A14
=A19
=A22
etc.

If I drag =A3 and =A6, the sequence gets broken. I'm trying to avoid coding that sequence cell by cell down 2000 thousand cells. If that makes sense. I believe you're previous INDEX formula will work for my purposes. I just cannot figure out how to tweak it so that it works for that above sequence.

Put the number 3 into your first cell (assume this cell A2), then this formula in A3:

=A2+MOD((ROWS($1:1)-1),2)*2+3

Copy that down as far as you need to - it will give you the sequence of row numbers that you need. Then in B2 you can use this formula:

=INDEX(Sheet1!A:A,A2)

then you can copy that formula down as required.

In your other thread you were talking about having two separate files, so if that is the case you will need to amend the Sheet1 reference to include the full path, filename and sheet name in the other file.

Hope this helps.

Pete

P.S. for the other sequence that you asked about in Post #1, you could put 3 in A2, then this formula in A3:

=A2+IF(MOD(ROWS($1:1)-1,4)+1=3,1,MOD(ROWS($1:1)-1,4)+1)

Copy the formula down. You would still have the INDEX formula in column B, where the results will be shown.

Cheers

No luck. I just don't understand Excel. So brilliant and efficient on so many levels, but simply cannot understand irregular dragging sequences. I have cells that follow pattern A3, A5, A10, A16, A21 (hypothetical). I hi-lite cells for drag down, and Excel cannot understand that sequence. If it's A1,A2,A3,A4, well then it works like a charm. Your first INDEX FUNCTION worked beautifully.

I've set up a file (attached) where I have created some data in column A of Sheet1 (actually, this is just 10 times the row number, so that it is easy to see where it came from). I have 3 other sheets which represent the patterns that you have suggested in your earlier thread and in Post #1 of this thread - these represent the differences in row numbers of 5,6,5,6 etc., then 1,2,1,4,1,2,1,4 etc. and 3,5,3,5.

In the first formula that I gave you I combined the calculated row number with the INDEX function, but in Post#5 above I suggested doing this in two separate columns.

Hope this helps to clarify things a bit more.

Pete

Originally Posted by Pete_UK

Put the number 3 into your first cell (assume this cell A2), then this formula in A3:

=A2+MOD((ROWS($1:1)-1),2)*2+3

Copy that down as far as you need to - it will give you the sequence of row numbers that you need. Then in B2 you can use this formula:

=INDEX(Sheet1!A:A,A2)

then you can copy that formula down as required.

In your other thread you were talking about having two separate files, so if that is the case you will need to amend the Sheet1 reference to include the full path, filename and sheet name in the other file.

Hope this helps.

Pete

P.S. for the other sequence that you asked about in Post #1, you could put 3 in A2, then this formula in A3:

=A2+IF(MOD(ROWS($1:1)-1,4)+1=3,1,MOD(ROWS($1:1)-1,4)+1)

Copy the formula down. You would still have the INDEX formula in column B, where the results will be shown.

Cheers

Per first solution, here is the sequence the formula returns, starting with 3 in A2, and pasting formula into B3 and dragging down:

3
6 6
11 19
14 38
19 51
22 70
27 83
30 102
35 115
38 0
43 0
46 0
51 0
54 0
59 0
62 0
67 0
70 0
75 0
78 0
83 0
86 0
91 0
94 0
99 0
102 0
107 0
110 0
115 0

If you still have a problem, please attach a sample Excel workbook and explain in that what the expected values should be. At the top of the screen is a yellow banner which gives details of how to attach a file to one of your posts.

Pete

Originally Posted by Pete_UK

I've set up a file (attached) where I have created some data in column A of Sheet1 (actually, this is just 10 times the row number, so that it is easy to see where it came from). I have 3 other sheets which represent the patterns that you have suggested in your earlier thread and in Post #1 of this thread - these represent the differences in row numbers of 5,6,5,6 etc., then 1,2,1,4,1,2,1,4 etc. and 3,5,3,5.

In the first formula that I gave you I combined the calculated row number with the INDEX function, but in Post#5 above I suggested doing this in two separate columns.

Hope this helps to clarify things a bit more.

Pete

I've attached spreadsheet with data set. Thanks in advance, Pete.Non-sequential data set.xlsx

Originally Posted by windknife

For O365, clean all expected results.

Please Login or Register  to view this content.

For 2021, clean all expected results.
Try this in B3 and copy to D3

Please Login or Register  to view this content.

Works like a charm, kind sir. Many thanks!

I see that you have an answer and that you have marked this thread as Solved. Just for completeness, however, I've attached a file which demonstrates how you could do this using the approach I highlighted above.

All along, I have assumed that you wanted to extract the data into a separate sheet, so I have continued with that, using the formulae from the sheet 3535 in the earlier attachment. I made a few amendments to the formula in B2, which are shown in red:

=INDEX('Irregular row sequence'!$A:$A,$A2+COLUMNS($B:B)-1)

Obviously, the sheet name where the data comes from has to change, and as the extract needs to be in different columns I have anchored the source column using the $ symbols. The amendment at the end adjusts the row number to accommodate the different columns.

That formula is copied across and down as required.

Hope this helps.

Pete

You are welcome.

Originally Posted by Pete_UK

I see that you have an answer and that you have marked this thread as Solved. Just for completeness, however, I've attached a file which demonstrates how you could do this using the approach I highlighted above.

All along, I have assumed that you wanted to extract the data into a separate sheet, so I have continued with that, using the formulae from the sheet 3535 in the earlier attachment. I made a few amendments to the formula in B2, which are shown in red:

=INDEX('Irregular row sequence'!$A:$A,$A2+COLUMNS($B:B)-1)

Obviously, the sheet name where the data comes from has to change, and as the extract needs to be in different columns I have anchored the source column using the $ symbols. The amendment at the end adjusts the row number to accommodate the different columns.

That formula is copied across and down as required.

Hope this helps.

Pete

Thanks again, Pete, for solving this problem. Both methods work beautifully.

Thanks for commenting on it.

Pete