Smart Paste Information into cells

[Jim Murphy]

I have a spreadsheet with several hundred records. I would like to populate blank fields in each record with information from another record with the same value in a particular field.

In the example below (blank cells have been replaced with ___), I want to populate all the blank fields in the column fk_unit based on the Equipment ID.

[TABLE="width: 374"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]EquipmentID[/TD]
[TD]Component Type[/TD]
[TD]fk_Unit[/TD]
[/TR]
[TR]
[TD]150082[/TD]
[TD]Header[/TD]
[TD]GDU[/TD]
[/TR]
[TR]
[TD]150082[/TD]
[TD]Tube[/TD]
[TD]___[/TD]
[/TR]
[TR]
[TD]150083[/TD]
[TD]Header[/TD]
[TD]FCC[/TD]
[/TR]
[TR]
[TD]150083[/TD]
[TD]Tube[/TD]
[TD]___[/TD]
[/TR]
[TR]
[TD]150096[/TD]
[TD]Header[/TD]
[TD]TGT[/TD]
[/TR]
[TR]
[TD]150096[/TD]
[TD]Tube[/TD]
[TD]___[/TD]
[/TR]
[TR]
[TD]150097[/TD]
[TD]Header[/TD]
[TD]GDU[/TD]
[/TR]
[TR]
[TD]150097[/TD]
[TD]Tube[/TD]
[TD]___[/TD]
[/TR]
</tbody>[/TABLE]

The table below shows my end goal. The underlined values are the ones I want to populate. Heres the catch.. I have a tables with several hundred records and about 160 columns. There are multiple columns I would like to populate based on the equipment ID, and the storage location for the date varies from record to record. In the example above, fk_unit was always populated for the component 'Header'. In practice, fk_unit may be populated for the component 'Tube', in which case I would like to populate the 'Header' record. Any help is much appreciated!

[TABLE="width: 374"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]EquipmentID[/TD]
[TD]Component Type[/TD]
[TD]fk_Unit[/TD]
[/TR]
[TR]
[TD]150082[/TD]
[TD]Header[/TD]
[TD]GDU[/TD]
[/TR]
[TR]
[TD]150082[/TD]
[TD]Tube[/TD]
[TD]GDU[/TD]
[/TR]
[TR]
[TD]150083[/TD]
[TD]Header[/TD]
[TD]FCC[/TD]
[/TR]
[TR]
[TD]150083[/TD]
[TD]Tube[/TD]
[TD]FCC[/TD]
[/TR]
[TR]
[TD]150096[/TD]
[TD]Header[/TD]
[TD]TGT[/TD]
[/TR]
[TR]
[TD]150096[/TD]
[TD]Tube[/TD]
[TD]TGT[/TD]
[/TR]
[TR]
[TD]150097[/TD]
[TD]Header[/TD]
[TD]GDU[/TD]
[/TR]
[TR]
[TD]150097[/TD]
[TD]Tube[/TD]
[TD]GDU[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

Based on the example of the goal and assumming that the data is is in columns A, B and C respectively, this should work:

Code:
Sub fillBlank()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("C2:C" & lr)
For Each c In rng
If c = "" And LCase(c.Offset(0, -1)) = "header" Then
c = c.Offset(1, 0).Value
ElseIf c = "" And LCase(c.Offset(0, -1)) = "tube" Then
c = c.Offset(-1, 0).Value
ElseIf c = "" Then
MsgBox "Location " & c.Address & " Needs your attention"
Beep
End If
Next
End Sub
Code:

To incorporate the other 157 columns will require a better explanation or which columns are involved, what part the location and date play in the issue, if any. Remember, we cannot see your worksheet unless you post a screen shot of it or provide a link to it.

 

[ZVI]

Welcome to the board, Jim!

There can be formula solution as well.

Let’s assume that fk_Unit is in C-column, then do as follows:

1. Activate C3 and set the name X with formula:
=IF(A3=A4,C4,IF(A3=A2,C2,""))

2. Select the range from C1 down to the last non-empty cell in C-column

3. Press Ctrl-G, press button "Special", choose "Blanks", press "OK"

4. Type formula =X and press Ctrl-Enter

 

[Biz]

Another Vba which warns users via msgbox if Header is Blank.

Sub Test()
    Dim sh As Worksheet, lr As Long, rng As Range, c As Range
    Set sh = Sheets(1) 'Edit sheet name
    lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
    Set rng = Range("B2:B" & lr)
    For Each c In rng
        Select Case Application.Proper(c.Value)
        Case "Tube"
            c.Offset(0, 1).Value = c.Offset(-1, 1).Value
        Case "Header"
            If c.Offset(0, 1).Value = vbNullString Then MsgBox "Location " & c.Offset(0, 1).Address & " Needs your attention"
        End Select
    Next c
End Sub

Biz