Solved

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

Posted on 2011-03-11
17
505 Views
Last Modified: 2012-05-11
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
Comment
Question by:xllvr
  • 8
  • 6
  • 2
17 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 35113158
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
 
LVL 1

Author Comment

by:xllvr
ID: 35113171
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
 
LVL 1

Author Comment

by:xllvr
ID: 35113195
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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 50

Expert Comment

by:barry houdini
ID: 35113218
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
 
LVL 1

Author Comment

by:xllvr
ID: 35113249
Nor is it mine.  Thanks for taking a stab at it.  I also think VBA is the answer here.  

Much appreciated!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122389
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
 
LVL 1

Author Comment

by:xllvr
ID: 35122428
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35122456
In the above code, change

For i = 6 To LastRow

to

For i = 3 To LastRow

Sid
0
 
LVL 1

Author Closing Comment

by:xllvr
ID: 35122465
BRILLIANT!!!!!!!!!!!!!!!!!!!!  Oh if I had only found you on Friday when I originally posted.  Thank you so much.  This is great.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122491
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
 
LVL 1

Author Comment

by:xllvr
ID: 35122501
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122547
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
 
LVL 1

Author Comment

by:xllvr
ID: 35122563
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122582
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35122589
Sorry a slight change in the above code.

Add

Temp = 0

Open in new window


in Line 5

Sid
0
 
LVL 1

Author Comment

by:xllvr
ID: 35122670
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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
remove dups 10 37
Auto Populate Day Month  2 digit Date 4 19
how to add loop into this VBA 3 29
macro modification Column C 14 31
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question