Formula That Replaces Values With Filler Values (Simplified and Updated)

[artiststevens]

PLEASE SEE POST #11 FOR AN UPDATED VERSION OF MY QUESTION

Hello,
I need a formula that separates data by a certain number of rows. The worksheet consist of columns of data and a column that contains “X”s. I need a formula that separates all of the “X”s by 7 rows. Most of the time that’s the case but there are a few instances where the “X”s are separated by less than 7 rows and I wanted a formula that creates filler rows.

Example:
Original Data
8 12/24/2014 7:45 1.2190 1.2191 1.2187 1.2189 95 X
1 12/24/2014 8:00 1.2189 1.2191 1.2187 1.2190 147
2 12/24/2014 8:15 1.2190 1.2192 1.2189 1.2190 78
3 12/24/2014 8:30 1.2189 1.2195 1.2189 1.2191 117
4 12/24/2014 8:45 1.2191 1.2192 1.2186 1.2187 73
5 12/24/2014 9:00 1.2187 1.2190 1.2186 1.2190 109
6 12/24/2014 9:15 1.2189 1.2192 1.2189 1.2192 57
7 12/24/2014 9:30 1.2192 1.2192 1.2191 1.2192 41
8 12/24/2014 9:45 1.2192 1.2192 1.2189 1.2189 85 X
1 12/24/2014 10:00 1.2189 1.2189 1.2187 1.2188 97
2 12/24/2014 10:15 1.2188 1.2193 1.2188 1.2193 150 X
1 12/25/2014 14:00 1.2221 1.2222 1.2217 1.2220 75
2 12/25/2014 14:15 1.2220 1.2221 1.2220 1.2220 30
3 12/25/2014 14:30 1.2219 1.2224 1.2219 1.2221 110
4 12/25/2014 14:45 1.2221 1.2225 1.2220 1.2225 131
5 12/25/2014 15:00 1.2224 1.2224 1.2211 1.2212 686
6 12/25/2014 15:15 1.2212 1.2213 1.2204 1.2206 310
7 12/25/2014 15:30 1.2207 1.2210 1.2205 1.2205 508
8 12/25/2014 15:45 1.2205 1.2208 1.2203 1.2207 528 X

Formula Result
8 12/24/2014 7:45 1.2190 1.2191 1.2187 1.2189 95 X
1 12/24/2014 8:00 1.2189 1.2191 1.2187 1.2190 147
2 12/24/2014 8:15 1.2190 1.2192 1.2189 1.2190 78
3 12/24/2014 8:30 1.2189 1.2195 1.2189 1.2191 117
4 12/24/2014 8:45 1.2191 1.2192 1.2186 1.2187 73
5 12/24/2014 9:00 1.2187 1.2190 1.2186 1.2190 109
6 12/24/2014 9:15 1.2189 1.2192 1.2189 1.2192 57
7 12/24/2014 9:30 1.2192 1.2192 1.2191 1.2192 41
8 12/24/2014 9:45 1.2192 1.2192 1.2189 1.2189 85 X
1 12/24/2014 10:00 1.2189 1.2189 1.2187 1.2188 97
1 12/24/2014 10:00 0 0 0 0 0
1 12/24/2014 10:00 0 0 0 0 0
1 12/24/2014 10:00 0 0 0 0 0
1 12/24/2014 10:00 0 0 0 0 0
1 12/24/2014 10:00 0 0 0 0 0
1 12/24/2014 10:00 0 0 0 0 0
1 12/24/2014 10:00 0 0 0 0 0
2 12/24/2014 10:15 1.2188 1.2193 1.2188 1.2193 150 X
1 12/25/2014 14:00 1.2221 1.2222 1.2217 1.2220 75
2 12/25/2014 14:15 1.2220 1.2221 1.2220 1.2220 30
3 12/25/2014 14:30 1.2219 1.2224 1.2219 1.2221 110
4 12/25/2014 14:45 1.2221 1.2225 1.2220 1.2225 131
5 12/25/2014 15:00 1.2224 1.2224 1.2211 1.2212 686
6 12/25/2014 15:15 1.2212 1.2213 1.2204 1.2206 310
7 12/25/2014 15:30 1.2207 1.2210 1.2205 1.2205 508
8 12/25/2014 15:45 1.2205 1.2208 1.2203 1.2207 528 X

As you can see in the original data the two “X”s between 9:45 and 10:15 are not separated by 7 rows of data. In the example of what I want the formula to do. Filler columns are created by repeating the date and time of the last row that separates the “X”s and all other data is zero.

Please see spreadsheet for a better understanding. Any and all help you could provide for any of the formulas would be greatly appreciated. Thank you.

[FlameRetired]

Are there any instances where the x's will be separated by more than 7 rows?

[samba_ravi]

see the attached file
differences comes at between Rows 75 and 81

[artiststevens]

No always just seven or less. Thank you NFLsales, I'll take a look.

[artiststevens]

see the attached file
differences comes at between Rows 75 and 81

Ok I see it. I made the mistake when I manually entered everything. Too many rows there and the separation was wrong for all of the entries. I'm uploading a new workbook with the changes. I'd really appreciate if you could take a look and adjust the formula accordingly. Thank you again for all of the help.

[artiststevens]

Any further help would be greatly appreciated.

[artiststevens]

Any further help would be greatly appreciated.

[samba_ravi]

is it correct the data in the rows between 75 & 81
i think the logic was not matching with rows 12 to 16 and 146 to 153

[artiststevens]

is it correct the data in the rows between 75 & 81
i think the logic was not matching with rows 12 to 16 and 146 to 153

I made changes in the updated spreadsheet with how the data should be explained. The formula is correct but how it should be displayed is wrong because of the error on my part. I've tried to make changes to the formula myself but was unsuccessful.

[artiststevens]

Any further help would be greatly appreciated.

[artiststevens]

Hello AM,
Thank you for offering to help me. I greatly appreciated. I will try to explain the logic better.

I have 8 columns [A – H] that contain data multiple years of data I would like to arrange and sort based upon the locations of the “X”s in column [I]. The majority of the “X”s in column [I] are 7 spaces apart but in a few places the spaces between the “X”s are less than 7 spaces apart. The spaces are always 7 spaces apart or less and never more.

I need a formula that finds the points in the data where the “X”s are less than 7 rows apart and repaste the data creating enough rows so the “X”s are seven rows apart. Most of the time that’s the case but there are a few instances where the “X”s are separated by less than 7 rows and I wanted a formula that creates filler rows.

The way I would the formula to display the data can be found below:

DATA
8 12/23/2014 17:45 1.2181 1.2183 1.2178 1.2178 85 X
1 12/23/2014 18:00 1.2179 1.2179 1.2177 1.2178 93
2 12/23/2014 18:15 1.2177 1.2177 1.2173 1.2173 82
3 12/23/2014 18:30 1.2173 1.2173 1.2170 1.2171 52
8 12/23/2014 19:45 1.2177 1.2177 1.2174 1.2175 58 X


FORMULA
8 12/23/2014 17:45 1.2181 1.2183 1.2178 1.2178 85 X
1 12/23/2014 18:00 1.2179 1.2179 1.2177 1.2178 93
2 12/23/2014 18:15 1.2177 1.2177 1.2173 1.2173 82
3 12/23/2014 18:30 0 0 0 0 0
3 12/23/2014 18:30 0 0 0 0 0
3 12/23/2014 18:30 0 0 0 0 0
3 12/23/2014 18:30 0 0 0 0 0
3 12/23/2014 18:30 1.2173 1.2173 1.2170 1.2171 52
8 12/23/2014 19:45 1.2177 1.2177 1.2174 1.2175 58 X

As you can see the last row (in bold) before the next “X” is repeated but just the date and time and that final row is pushed to the bottom.

Please see attached spreadsheet for a better understanding. Any and all help you could provide for any of the formulas would be greatly appreciated.

Happy New Year And Thank You Again!

[artiststevens]

Any further help or advice would be greatly appreciated.

[samba_ravi]

see the attached file

[artiststevens]

Thank you NFLsales that's exactly what I needed.