Solved

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

Posted on 2011-03-11
17
522 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

679 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