Solved

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

Posted on 2011-03-11
17
481 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now