Fill Up Automatically?

[svendiamond]

The Job Function/Area/Department columns in this report all come out AFTER the list of names for each. I.e. Raymond G worked in "SUPPORT" -- Steve P worked in CRTVLOAD -- Matt S and Steve P worked in RCVCRTV.

I want to (automatically) FILL UP the Department/Area/Job Function columns so that they line up with each name. AKA I would manually have to fill up "SUPPORT" from cell C4 into C3 and C2. Then I would fill up "CRTVLOAD" from C8 into C7, C6 and C5. Etc etc etc Down this list which is about 2000 rows long. Any ideas as to how I might automatically fill all of them up? Maybe if I make the #N/A's into Blanks then copy values only into a new sheet... then FILL UP all blanks? I don't know... any help would be appreciated.

 

[ranman256]

(sorry , I changed threads and didnt know) Why wont they show if they exist in the excel list?

 

[NiceCyte]

That's an interesting way of having data come out of a report!

You're only wanting to fill upwards for the first 3 columns, for Department/Area/Job Function? What about all of the #N/A in Columns D and E? I'm assuming once you fill up columns A, B, and C that you can filter out the #N/A and have your final list.

Is there anything in columns F, G, or H? Can those columns be used for formulas?

 

[svendiamond]

You should see how the original report looks! This is after my macro which lines up some columns and then another page full of formulas to straighten everything out... In rows where a name exists in column D, there are numbers to the right... the report is mostly like double-spaced and sometimes extra spaces... Here is a better screenshot:

This is what it looks like now (I made them blanks instead of "#N/A"):

And here's what I want it to look like, since I want to FILL UP the first three columns so that when I INDEX/MATCH someone's name I can see what department/area/function they were in:

Forgot to hide the names, oh well. So I want excel to look down column C until it finds something (in this case the first one would be "SUPPORT") and FILL UP that column into the blanks. Then go down and find the next non-blank cell in column C (which would be CRTVLOAD in C8) and fill that up into the blanks above it... and so on and so forth.

 

[NiceCyte]

This code works with the #N/A in the cells. They register as errors.

Sub FillerUP()
LastRow = Range("A65000").End(xlUp).Row
For i = LastRow To 2 Step -1
    If Not IsError(Range("A" & i)) Then
        RecordA = Range("A" & i)
        RecordB = Range("B" & i)
        RecordC = Range("C" & i)
    Else
        Range("A" & i) = RecordA
        Range("B" & i) = RecordB
        Range("C" & i) = RecordC
    End If
Next i
End Sub

This following code works with empty cells

Sub FillerUP()
LastRow = Range("A65000").End(xlUp).Row
For i = LastRow To 2 Step -1
    If Range("A" & i) <> "" Then
        RecordA = Range("A" & i)
        RecordB = Range("B" & i)
        RecordC = Range("C" & i)
    Else
        Range("A" & i) = RecordA
        Range("B" & i) = RecordB
        Range("C" & i) = RecordC
    End If
Next i
End Sub

Are macro solutions an option? Or does it need to be an excel formula solution?

 

[svendiamond]

THANK YOU THANK YOU THANK YOUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU NiceCyte!!!!!!!

 

[NiceCyte]

You're welcome! Happy to help