• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

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
0
xllvr
Asked:
xllvr
  • 8
  • 6
  • 2
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
SiddharthRoutCommented:
In the above code, change

For i = 6 To LastRow

to

For i = 3 To LastRow

Sid
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now