Formula to copy name from another column and copy down where applicable

Hi Experts,

Could someone direct me as to the cleanest formula that would copy a cell from one column into another and then paste it down to the appropriate cells in that column?  This might be a Do Until piece of code instead of a formula.  Would love some guidance.

The attached should help make it clear.  Thank you! EE-Copy-Test.xlsx
LVL 1
xllvrAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
In the above code, change

For i = 6 To LastRow

to

For i = 3 To LastRow

Sid
0
 
barry houdiniCommented:
Given that all the names are 3 letters perhaps try this formula in C6 copied down

=IF(LEN(F6)=3,F6,C5)

regards, barry
0
 
xllvrAuthor Commented:
That is dummy data I sent you.  The names vary.  Also, I'm so sorry...I wasn't clear.  The names are in bold.  Those are the names I want copied over and down.  The data underneath the names stay in the column they are already in.

Does that make sense?

Thanks so much, Barry.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
xllvrAuthor Commented:
By the way, I realize the data is beyond messy.  I'm trying to clean it all up (there is tons more than what i attached) and get it to resemble a database as much as possible.  So many rows with garbage in them.  Should keep me off the streets and out of trouble!
0
 
barry houdiniCommented:
The formula I suggested will give the result you want for your dummy data but it relies on the names being 3 letters (and other data always being longer). If the only distinguishing feature is that the names are bold then you'll need VBA rather than a formula.....unless you have a list of names somewhere.....

VBA isn't my forte - I'm sure somebody else will be able to do that.....

regards, barry
0
 
xllvrAuthor Commented:
Nor is it mine.  Thanks for taking a stab at it.  I also think VBA is the answer here.  

Much appreciated!
0
 
SiddharthRoutCommented:
Is this what you want? Sample Attached.

Please run the macro "Sample"

Sid

Code Used

Sub Sample()
    Dim i As Long, LastRow As Long, CLastRow As Long
    Dim ws As Worksheet
    Dim temp As Long
    Set ws = Sheets("Flavored non FOB")
    
    LastRow = ws.Range("F" & Rows.Count).End(xlUp).Row + 1
    
    For i = 6 To LastRow
        If ws.Range("F" & i).Font.Bold = True And _
        Len(Trim(ws.Range("F" & i).Value)) <> 0 Then
            ws.Range("F" & i).Offset(, -3).Value = ws.Range("F" & i).Value
            If temp <> 0 Then
                ws.Range("C" & temp).AutoFill Destination:=Range("C" & temp & ":C" & i - 1)
            End If
            temp = i
        End If
    Next i
    
    CLastRow = ws.Range("C" & Rows.Count).End(xlUp).Row
    
    ws.Range("C" & CLastRow).AutoFill Destination:=ws.Range("C" & CLastRow & ":C" & LastRow)
End Sub

Open in new window

Updated-EE-Copy-Test.xlsm
0
 
xllvrAuthor Commented:
This is so close to perfection!!!  I could have used this 3 days ago since I'm almost done with the project but this is super useful for the future.

I am attaching what my file looks like now.  The code worked but didn't populate the first few rows.  I tweaked the code to reflect the shifting of columns since Friday.  Take a quick peek and show me where I went wrong in the minor changes.

Thank you!!! EE-Copy-Test2.xlsm
0
 
xllvrAuthor Commented:
BRILLIANT!!!!!!!!!!!!!!!!!!!!  Oh if I had only found you on Friday when I originally posted.  Thank you so much.  This is great.
0
 
SiddharthRoutCommented:
I had seen the post but you specifically mentioned "cleanest formula" so I didn't post :)

After you posted "I also think VBA is the answer here." and the question was lying around unanswered, I thought let me post the code :)

Glad to be of help!

Sid
0
 
xllvrAuthor Commented:
Just one more question:  If I delete the line of code that references the Worksheet name, can I then use this on all different worksheets?
0
 
SiddharthRoutCommented:
For different worksheets, simply assign the relevant worksheet to the variable ws. For example if you want to run it on sheet("Sheet5") then change this

Set ws = Sheets("Flavored non FOB")

to

Set ws = Sheets("Sheet5")

Sid
0
 
xllvrAuthor Commented:
I've got four files with 10 worksheets each.  So you're saying to change the Sheet name before I run it each time?
0
 
SiddharthRoutCommented:
You can do that in a loop ;)

No need to manually do it. See this code. This will run the code in all the 10 worksheets in one go. The other thing that you need to take care of is the Column Names and the starting Row of BRAND.

Sub Sample()
    Dim i As Long, LastRow As Long, CLastRow As Long
    Dim ws As Worksheet, wsheet As Worksheet
    Dim temp As Long
    
    For Each wsheet In ThisWorkbook.Sheets
        Set ws = Sheets(wsheet.Name)
    
        LastRow = ws.Range("G" & Rows.Count).End(xlUp).Row + 1
        
        For i = 6 To LastRow
            If ws.Range("G" & i).Font.Bold = True And _
            Len(Trim(ws.Range("G" & i).Value)) <> 0 Then
                ws.Range("G" & i).Offset(, -3).Value = ws.Range("G" & i).Value
                If temp <> 0 Then
                    ws.Range("D" & temp).AutoFill Destination:=Range("D" & temp & ":D" & i - 1)
                End If
                temp = i
            End If
        Next i
        
        CLastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
        
        ws.Range("D" & CLastRow).AutoFill Destination:=ws.Range("D" & CLastRow & ":D" & LastRow)
    Next
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
Sorry a slight change in the above code.

Add

Temp = 0

Open in new window


in Line 5

Sid
0
 
xllvrAuthor Commented:
Arg...couldn't make it work so am going back to what did work (your original code) since I'm under the gun here.  Thank you so much for all the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.