Fill down and up untill a value is reached in another column?

[CaptainCsaba]

Hello! So I have quite a weird problem. In column B and C I both have stuff and a lot of blank cells. What I need to figure out is that I need to fill up column B (not everywhere) the following way (this is just an example, not the real values):

What I have:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Potato[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Potato[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

There are other columns that is why we need to do it this way and I don't want to further confuse you guys, but basically we need to fill up the B column with it's values. The values need to go both upwards and downwards. They need to fill upwards until something is reached in column C and that value in column C should be included. And they need to fill downward until a value is reached in column C but that value should not be included. This might seem a bit weird but you can see it above in the example. The value "C" was filled upward until it reached Apple and appeared next to it. It was also filled downward until it reached Potato but it did not appeared next to potato, since the next value is probably going to be next to it which is "D".

It might seem a bit weird but there are other columns and we actually need it like this. Some of the "fruits" in column C does not have a value in column "b" and they need to be left empty, that is why it needs to happen like this. Does somebody have an idea?

So what is needed basically: the values in column B to be filled upward until reach a value in column C and the value in column B should appear next to the value in column C. And the same values also need to be filled downward until they reach a value column C but should NOT appear next to that value.

 

[MickG]

With you data in columns "B and C", try this:-

[COLOR="Navy"]Sub[/COLOR] MG01Mar06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Aph [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant
    [COLOR="Navy"]Set[/COLOR] Rng = Range("B1", Range("B" & Rows.Count).End(xlUp))
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 1)
    [COLOR="Navy"]If[/COLOR] Dn.Value <> "" [COLOR="Navy"]Then[/COLOR] temp = Dn.Value
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, -1).Value <> "" [COLOR="Navy"]Then[/COLOR] Aph = Dn.Offset(, -1)
        [COLOR="Navy"]If[/COLOR] Not .Exists(temp) [COLOR="Navy"]Then[/COLOR]
            .Add temp, Array(Dn, Aph)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(temp)
                [COLOR="Navy"]Set[/COLOR] Q(0) = Union(Q(0), Dn)
                Q(1) = Aph
            .Item(temp) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    .Item(K)(0).Offset(, -1).Value = .Item(K)(1)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Rick Rothstein]

As MickG assumed, if your data is in Columns B and C, and further assuming you have less than 65,536 rows of data, then here is another macro that you can consider using...

[table="width: 500"]
[tr]
	[td]Sub FillUpAndDownColumnB()
  Dim FirstCell As Range, Ar As Range
  Set FirstCell = Cells(1, "B").End(xlDown).Offset(, 1).End(xlUp)
  With Range(FirstCell, Cells(Rows.Count, "B").End(xlUp).Offset(, 1))
    For Each Ar In .SpecialCells(xlBlanks).Areas
      Ar.Offset(-1, -1).Resize(Ar.Count + 1).Value = Intersect(Ar.Offset(-1).Resize(Ar.Count + 1).EntireRow, Columns("B").SpecialCells(xlConstants)).Value
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[CaptainCsaba]

I tried them and both have worked perfectly. Thank you for the help guys, you are the best!

 

[MickG]

You're welcome