Formula to remove empty cells

[Stevetron]

Hi,
I would like some help. I have a daily record that requires 5 data entries per day. I record the date one, then the 5 entries of data. Then repeat daily.

the problem I have is, in column A the first date is in A3, then the next date is in A8, A13 and so on.

i want a second sheet, let’s call it sheet2, to show the dates without the 4 empty cells.

for example I want sheet2A3 to show sheet1A3, sheet2A4 to show sheet1A8.

I hope this makes sense and somebody can help me. Thanks in advance

 

[Stevetron]

Hi,
I would like some help. I have a daily record that requires 5 data entries per day. I record the date one, then the 5 entries of data. Then repeat daily.

the problem I have is, in column A the first date is in A3, then the next date is in A8, A13 and so on.

i want a second sheet, let’s call it sheet2, to show the dates without the 4 empty cells.

for example I want sheet2A3 to show sheet1A3, sheet2A4 to show sheet1A8.

I hope this makes sense and somebody can help me. Thanks in advance

I have tried in sheet2A3 (=sheet1A3) but it I try to drag that formula down, it doesn’t recognise I want to miss out the next 4 cells.

 

[pjmorris]

Your attempt is a good start - but never forget that computers are really really stupid - they really are unable to infer anything (well almost anything) that we think is obvious!

in sheet 2A3: =OFFSET(Sheet1!$A$3,(ROW()-3)*5,0) and copy down.

HTH

 

[Stevetron]

in sheet 2A3: =OFFSET(Sheet1!$A$3,(ROW()-3)*5,0) and copy down.

HTH

Thank you so much for your quick reply. I cannot right now as I left my laptop at work, but will let you know if this works.

could you explain what this formula is doing please?I am trying to learn as much about excel as possible.

 

[pjmorris]

Hi Steve,

The OFFSET function does just that, it returns a value which is offset from the reference, in this case Sheet1!$A$3, the $ signs make the row and column 'absolute' so when you copy the formula down the column the reference stays the same..

The ROW() returns the row number of where the formula is - so for row 4 it returns 4. By deleting 3 (the starting row) the (ROW()-3)*5 equates to 0 and so the reference cell is returned. On row 4 that same formula results in 5 so Offset adds 5 rows to the reference and looks at Cell Sheet1!A8,

HTH

 

[Stevetron]

Hi Steve,

The OFFSET function does just that, it returns a value which is offset from the reference, in this case Sheet1!$A$3, the $ signs make the row and column 'absolute' so when you copy the formula down the column the reference stays the same..

The ROW() returns the row number of where the formula is - so for row 4 it returns 4. By deleting 3 (the starting row) the (ROW()-3)*5 equates to 0 and so the reference cell is returned. On row 4 that same formula results in 5 so Offset adds 5 rows to the reference and looks at Cell Sheet1!A8,

HTH

Thank you very much

 

[pjmorris]

a pleasure - thanks for the feedback

 

[mikerickson]

It is bad practice to construct a data base where blank cells are interpreted as "same as above".
The OP database will be more flexible if those A4:A7 were filled with the date in A3.
If you want the visual separation you could Conditionaly format A4 with the formula =(A3=A4) turning the font color white, so the user only sees the first row for that date.
But Excel works better when all the columns are filled with the availible data.

 

[pjmorris]

Mike, couldn't agree more - personally I'd have imported it into Powerquery and used the data from there.

 

[Stevetron]

Ok, so i think i may have inut this wrong, but it didnt seem to work as i expected. This is sheet1, column A is the date.

Date	Cup A	Gross A	Net A	Cup B	Gross B	Net B	Ratio	Target	Upper Limit	Lower Limit	Temperature ©	Daily Average	Notes
01-Jun	0.000			0.000				8.500	8.755	8.245		0.000
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
02-Jun​	0.000			0.000				8.500	8.755	8.245		0.000
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
03-Jun​	0.000			0.000				8.500	8.755	8.245		0.000
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
04-Jun​	0.000			0.000				8.500	8.755	8.245		0.000
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
05-Jun​	0.000			0.000				8.500	8.755	8.245		0.000
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245
	0.000			0.000				8.500	8.755	8.245

This is sheet 2, where i want the date from sheet1 to fill colum a, but without gaps. I also need sheet1 column M to fill sheet 2 column B, so that i end up with the date and the corresponding daily average in sheet2 without any gaps.

Date	Daily average

When i input the offest formula, i get this

Date	Daily average
01-Jun​
00-Jan​
00-Jan​
00-Jan​

The formula seems to work for the first cell, then defaults to 00-jan. Also, there are still gaps in the data.