//1.  Scan Top of Row Till End and return 3 columns
Sub lineemupSAS()

Dim i As Long

Dim dr As Long

For i = 2 To Cells(2, Columns.Count).End(xlToLeft).Column Step 2

dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
 
Cells(2, 1).Resize(65).Copy Cells(dr, 1)

Cells(2, i).Resize(65).Copy Cells(dr, 2)

Cells(2, 1 + i).Resize(65).Copy Cells(dr, 3)

Next i

End Sub

//2.  Opposite of Lineemup

Sub Mergeitems()

    Dim cl As Range
    Dim rw As Range

    Set rw = ActiveCell

    Do While rw <> ""
        ' for each row in data set
        '   find first empty cell on row
        Set cl = rw.Offset(0, 1)
        Do While cl <> ""
            Set cl = cl.Offset(0, 1)
        Loop

        ' if next row needs to be processed...
        Do While rw = rw.Offset(1, 0)
            i = 1
            Do While rw.Offset(1, i) <> "" 
                cl = rw.Offset(1, i)      
                Set cl = cl.Offset(0, 1)  
                 i = i + 1
            Loop
            rw.Offset(1, 0).EntireRow.Delete xlShiftUp  'delete old data
        Loop

        ' next row
        Set rw = rw.Offset(1, 0)
    Loop
End Sub

//3.  Delete Rows When Second Column Is Blank
Sub DeleteRowsWithBlanks()
 
    Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

//4.  Delete everything after certain character in quotes:

Sub CleanText()

Dim iPos As String

Dim c As Range

For Each c In Range("a1:h66" & Range("a" & Rows.Count).End(xlUp).Row)
 
iPos = InStr(1, c.Value, "<a")

If iPos > 0 Then

c.Value = Left(c.Value, iPos - 1)

End If

Next

End Sub

//5.  Remove everything left of NTH character

=LEFT(K2, 13)

or

=RIGHT(B2, LEN( B2)-2)

//6.  Remove everything left and right of middle

=MID(A2, 10, 100)

//7.  Remove everything before

=RIGHT(E2,LEN(E2)-FIND("src",E2)+1)

//8.  Remove everything after

=LEFT(F2,FIND("png",F2,1) + 2)

//9.  Remove trailing characters

=IF(RIGHT(A1,1)="/",LEFT(A1,LEN(A1)-3),A1)

//10.  Remove string contained in neighboring cell

=SUBSTITUTE(F2,B2,"")

//11.  Remove first character if ="/"

=IF(LEFT(E2,1)="/",MID(E2,2,255),E2)

//12.  Remove last character if ="."

=IF(RIGHT(G2,1)=".",LEFT(G2,LEN(G2)-1),G2)

//13.  Bulk find and replace

Sub multiFindandReplace()
Dim myList, myRange
Set myList = Sheets("aid").Range("B2:C4139")
Set myRange = Sheets("narea").Range("F2:F200044")
For Each cel In myList.Columns(1).Cells
 myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
Next cel

End Sub

//14.  Caps and Lower Case (proper)
=PROPER(A1)

//15.  SQL query for category layout in opencart
INSERT INTO oc_category_to_layout(category_id, store_id, layout_id)
select category_id, store_id, product_count
from oc_category_to_store
where not exists (select * from oc_category_to_layout where oc_category.category_id = oc_category_to_layout.category_id);

update oc_category_to_layout layout_id = 13;

//16.  Remove all capital alpha
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D211," ",""),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z",""),"-","")

//17.  Find OpenCart stock status differences
SELECT p.model, p.stock_status_id
FROM oc_product p
JOIN obsolete_temp o
ON p.model = o.model
AND p.stock_status_id <> o.stock_status;

//18.  Update OpenCart stock status based on imported table
update oc_product op
join obsolete_temp ot
set op.stock_status_id = ot.stock_status
where op.model = ot.model
and op.stock_status_id <> ot.stock_status;