Inserting variable amount rows between rows

**marko3** · 06-22-2011, 05:17 PM

I HAVE AN EXCEL SHEET WITH DATA PRESENTED AS BELOW

Column A B C D E
14/05/2006 16:00 2440 3160 2990 3890
14/05/2006 19:00 2430 3210 3060 3890
14/05/2006 20:00 2420 3220 3040 3940
14/05/2006 22:00 2430 3200 2980 4090
15/05/2006 01:00 2550 3160 2720 4080
15/05/2006 04:00 2610 3230 2680 4130
15/05/2006 06:00 2520 3230 2240 3680
15/05/2006 12:00 2470 3260 3240 4470
15/05/2006 16:00 2410 3210 3250 4310
15/05/2006 19:00 2410 3220 3290 4500
15/05/2006 20:00 2400 3230 3180 4520
15/05/2006 22:00 2380 3150 3070 4570
16/05/2006 01:00 2410 3250 3100 4565

I WANT THE TIMES TO BE IN EVERY HOUR, IE: ADD IN ROWS BETWEEN EACH TIME, IE BETWEEN 16:00 AND 19:00 I WANT TO ADD IN 2 EXTRA ROWS WITH THE DATA FOR COLUMNS B,C,D ETC FOR THE 17:00 TO BE EQUAL TO THE 16:00 + 1/3 OF THE DIFFERENCE BETWEEN 16:00 AND 19:00, ETC THE WHOLE WAY DOWN,
and the times changed to the theoretical new time

THERE IS 18,000 ROWS WITH THE TIMES THE SAME FOR EACH BUT THE DATES CHANGING.

THANKS VERY MUCH.

**JBeaucaire** · 06-22-2011, 06:49 PM

This seems like a simple task, but I'm repeating a warning I've heard many times here on the forum. The task your describing is essentially "faking data" and:

1) shouldn't be done
2) shouldn't be necessary

**jaslake** · 06-22-2011, 07:10 PM

Hi marko3

I'd guess that JB's correct...he certainly has much more experience than I. However, I'm not going to ask "why"...you must have a reason. I'd prefer NOT to create your data so I will ask you to post a workbook that has your raw data in one worksheet and your desired output in another worksheet so I understand what you're looking for.

**JBeaucaire** · 06-22-2011, 07:14 PM

Sorry Jas, the intended point (and I should've actually asked) was indeed "why"? Often when we look at the reason someone is attempting to create fake data, we can offer a better path to the intended FINAL result that wouldn't include fake data.

I would guess some sort of chart or report that needs no gaps in it's current design.

**jaslake** · 06-22-2011, 07:29 PM

@ JB

No "Sorry" necessary. A simple demonstration of the OP's before and after will give us this

we can offer a better path to the intended FINAL result that wouldn't include fake data.

Appreciate your feedback.

**marko3** · 06-23-2011, 01:39 AM

Hi everyone, thanks for the replies, the data is needed every hour, and i am using an interpolated values to get the estimate for the hours i dont have, it is necessary that i have data for each hour,

date and time b c d e f
14/05/2006 16:00 2440 3160 2990 3890 1540
14/05/2006 17:00 2436.666667 3176.666667 3013.333333 3890 1523.333333
14/05/2006 18:00 2433.333333 3193.333333 3036.666667 3890 1506.666667
14/05/2006 19:00 2430 3210 3060 3890 1490
14/05/2006 20:00 2420 3220 3040 3940 1490
14/05/2006 21:00 2425 3210 3010 4015 1490
14/05/2006 22:00 2430 3200 2980 4090 1490
14/05/2006 23:00
15/05/2006 00:00
15/05/2006 01:00 2550 3160 2720 4080 1540
15/05/2006 02:00
15/05/2006 03:00
15/05/2006 04:00 2610 3230 2680 4130 1560
15/05/2006 05:00
15/05/2006 06:00 2520 3230 2240 3680 1560
15/05/2006 07:00
15/05/2006 08:00
15/05/2006 09:00
15/05/2006 10:00
15/05/2006 11:00
15/05/2006 12:00 2470 3260 3240 4470 1340
15/05/2006 13:00
15/05/2006 14:00
15/05/2006 15:00
15/05/2006 16:00 2410 3210 3250 4310 1360
15/05/2006 19:00 2410 3220 3290 4500 1310

15/05/2006 22:00 2380 3150 3070 4570 1380
16/05/2006 01:00 2410 3250 3100 4500 1480
16/05/2006 04:00 2450 3080 3250 4040 1530
16/05/2006 06:00 2470 3160 3190 4310 1630
16/05/2006 12:00 2410 3210 3320 4470 1480
16/05/2006 16:00 2370 3170 3130 4440 1360