If cell is blank then search for Value above.

[kiasor]

Morning all,

I am sure someone here on MrExcel will be able to help with this as the community is so knowledgeable.

I will attach a file image to help explain what i am trying to do.

But basically:

• Column H (Actual finish date has some blanks)
• Column A is order A-Z and if the value in column A is the same then i want to copy the date down into the blanks
• The curve ball is that further down this database there could be 20+ blanks attached to the same order number with a date only on the top order.

I can solve it if there is just one blank underneath - i inserted another column before H and i used the formula =IF(A16=A15,I15,I16) like so:

 

[kiasor]

Apologies i pressed post before inserting correctly. This is the formula working for a single cell gap.

But i need it to search upwards until it finds a date then copy down.

I am not adverse to using VBA if you can find a solution that way i have VBA loops for other things.

PS the columns are blank in the middle because its sensitive information, usually they are full of data.

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.

 

[kiasor]

Apologies Fluff - its office 365. Build version 2002 currently.

 

[Fluff]

Thanks for that, could you have an order number that has dates in col I on more than one row, or will there only be one date for a particular order number?

 

[kiasor]

Good question!

Just checked down the list a bit further in the full version.

• There will always be at least one date attached to an order number - with X blanks
• And some are actually full and do not have any gaps.
• Some have a couple, but have gaps, E.G 4 out of the 7 lines for example, filled with dates on the same order

PS - the Date will always be the same on the same order. If there are 8 lines and 5 lines have the date in - that will always be the same date and i just need to fill the other gaps with that date.

 

[Fluff]

Ok, in that case how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	County								Introduced
2	Berkshire								01/01/1980
3	Berkshire							01/01/1980
4	Berkshire							01/01/1980
5	County Durham								01/01/2005
6	Cumbria								01/01/1980
7	Derbyshire								01/12/1997
8	East Riding of Yorkshire								01/01/1980
9	Essex								01/01/1980
10	Essex							01/01/1980
11	Greater London								01/01/1985
12	Greater London							01/01/1985
13	Greater London							01/01/1985
14	Greater London							01/01/1985
15	Hampshire								01/06/1996
16	Hampshire							01/06/1996
17	Hertfordshire								01/10/2009
18	Hertfordshire							01/10/2009
19	Hertfordshire							01/10/2009
20	Leicestershire								01/01/1986
21	Leicestershire							01/01/1986
22	Leicestershire							01/01/1986
23	Lincolnshire								01/08/2007
24	Merseyside								01/07/1986
25	Norfolk								01/01/1980
26	Northamptonshire								01/12/2001
27	Nottinghamshire								01/01/2006
28	Nottinghamshire								01/05/2006
29	Nottinghamshire							01/05/2006
30	South Yorkshire								01/01/1980
Data

Cell Formulas
Range	Formula
H2:H30	H2	=IF(I2<>"","",LOOKUP(2,1/(A$2:A2=A2)/(I$2:I2<>""),I$2:I2))

 

[kiasor]

Wow.

That is amazing!! Really great stuff - Thankyou.

I just filled down the entire 30,000 columns.

Is there a way to then bring over the cells from the original column into the inserted column which didn't need any actions? the ones colored in yellow? is that the concatenate function? or could it be in the formula?

Finally - and this really is the icing on the cake. - i found a few errors (it appears to be only where the date is somewhere in the middle?)

 

[kiasor]

Just looking at the formula, i can see its looking upwards in the date column, could it be adjusted to look up AND down?

 

[Fluff]

Ok, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	County								Introduced
2	Berkshire							01/09/2020
3	Berkshire							01/09/2020	01/09/2020
4	Berkshire							01/09/2020
5	County Durham							01/01/2005	01/01/2005
6	Cumbria							01/01/1980	01/01/1980
7	Derbyshire							01/12/1997	01/12/1997
8	East Riding of Yorkshire							01/01/1980	01/01/1980
9	Essex							01/01/1980	01/01/1980
10	Essex							01/01/1980
11	Greater London							01/01/1985	01/01/1985
12	Greater London							01/01/1985
13	Greater London							01/01/1985
14	Greater London							01/01/1985
15	Hampshire							01/06/1996	01/06/1996
16	Hampshire							01/06/1996
17	Hertfordshire							01/10/2009	01/10/2009
18	Hertfordshire							01/10/2009
19	Hertfordshire							01/10/2009
20	Leicestershire							01/01/1986	01/01/1986
21	Leicestershire							01/01/1986
22	Leicestershire							01/01/1986
23	Lincolnshire							01/08/2007	01/08/2007
24	Merseyside							01/07/1986	01/07/1986
25	Norfolk							01/01/1980	01/01/1980
26	Northamptonshire							01/12/2001	01/12/2001
27	Nottinghamshire							01/01/2006	01/01/2006
28	Nottinghamshire							01/05/2006	01/05/2006
29	Nottinghamshire							01/01/2006
30	South Yorkshire							01/01/1980	01/01/1980
Data2

Cell Formulas
Range	Formula
H2:H30	H2	=IF(I2<>"",I2,INDEX(FILTER(I$2:$I$30,($A$2:$A$30=A2)*($I$2:$I$30<>"")),1))