xllvr
asked on
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
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
ASKER
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.
Does that make sense?
Thanks so much, Barry.
ASKER
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!
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
VBA isn't my forte - I'm sure somebody else will be able to do that.....
regards, barry
ASKER
Nor is it mine. Thanks for taking a stab at it. I also think VBA is the answer here.
Much appreciated!
Much appreciated!
Is this what you want? Sample Attached.
Please run the macro "Sample"
Sid
Code Used
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
Updated-EE-Copy-Test.xlsm
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BRILLIANT!!!!!!!!!!!!!!!!! !!! Oh if I had only found you on Friday when I originally posted. Thank you so much. This is great.
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
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
ASKER
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?
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
Set ws = Sheets("Flavored non FOB")
to
Set ws = Sheets("Sheet5")
Sid
ASKER
I've got four files with 10 worksheets each. So you're saying to change the Sheet name before I run it each time?
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.
Sid
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
Sid
Sorry a slight change in the above code.
Add
in Line 5
Sid
Add
Temp = 0
in Line 5
Sid
ASKER
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!
=IF(LEN(F6)=3,F6,C5)
regards, barry