Hello Fullhouse,
You're welcome and I'm glad to have been able to assist.
I'm really happy that you spent the time foraging through information and working this out basically on your own. I laud you for that.
If you're interested, below is a condensed version of your code (but excluding the date part that you added in):-
Option Explicit
Sub Format_Data2()
Dim i As Long, lr As Long, ar As Variant
Dim ws As Worksheet: Set ws = Sheet1
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("E1:E" & lr).AdvancedFilter 2, , ws.[M1], 1 'Unique values moved temporarily to Column M.
ar = ws.Range("M2", ws.Range("M" & ws.Rows.Count).End(xlUp))
Application.ScreenUpdating = False
For i = 1 To UBound(ar)
With ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp))
.AutoFilter 1, ar(i, 1)
With .Offset(1)
If ar(i, 1) = "abc.com" Then
.Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany1"
.Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment1"
End If
If ar(i, 1) = "bcd.com" Then
.Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany2"
.Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment2"
End If
If ar(i, 1) = "edc.com" Then
.Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany3"
.Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment3"
End If
If ar(i, 1) = "blah.com" Then
.Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany2"
.Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment2"
End If
If ar(i, 1) = "bbb.com" Then
.Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany3"
.Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment3"
End If
If ar(i, 1) = "aa.com" Then
.Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany2"
.Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment2"
End If
.AutoFilter
End With
End With
Next i
ws.Columns.AutoFit
ws.Columns("M").Clear
Application.ScreenUpdating = True
End Sub
Display More
If you're willing to do a bit more research, what I've done in the above code is extract all the unique values in Column E using the AdvancedFilter and temporarily placing them in Column M. These unique values are then placed into an array(ar) which is then looped through and filtered for each unique value. This prevents many, many iterations which in turn speeds up the code and, as you stated earlier, you have around 100K rows of data so the saved iterations would be in the thousands. I tested this code on 150K rows and it took about three seconds to execute on my machine.
Column M is cleared at the end of code execution.
As you can see in the code, I've used 'IF' statements to identify the unique values for processing once filtered. You may want to research 'case statements in VBA' if you have the time. 'Case statements' can be used in place of 'IF' statements and may actually work more quickly.
Anyway, once again "well done" and good luck with your project.
I've attached your sample workbook with the above code implemented just so you can see how it works. There are about 230 rows of data in the sample.
Cheerio,
vcoolio.
Fullhouse.xlsm