Sorting Domains in an Excel Sheet

[Major123456]

Found this forum online while looking for a solution for one of my issues. This looks and feels like home to me as I spent a ton of time working with excel everyday. I will stick around and hangout more often.

Anyways to my issue here.

I have an email lead solution that provides me with leads and comments on various pages on about 100 of my domains. It all comes down to one excel sheet which looks like

websiteA.com/someurlhere
websiteB.com/someurlhere
websiteC.com/someurlhere
websiteA.com/someurlhere
websiteB.com/someurlhere
websiteA.com/someurlhere

Now I want to run a function to be able to generate variant list based on every domain individually.

So

websiteA.com/someurlhere
websiteA.com/someurlhere
websiteA.com/someurlhere
websiteA.com/someurlhere

becomes one list

websiteB.com/someurlhere
websiteB.com/someurlhere
websiteB.com/someurlhere
websiteB.com/someurlhere

becomes another and so forth. Any help would be appreciated.

Any help would be appreciated.

[stanleydgromjr]

Major123456,

Welcome to the Excel Forum.

I assume that your raw data is in column A, beginning in cell A1.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SortSeparateURLs()
' stanleydgromjr, 04/14/2013
' http://www.excelforum.com/excel-general/914838-sorting-domains-in-an-excel-sheet.html
Dim r As Long, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lr).Sort key1:=Range("A1"), order1:=1
For r = lr To 2 Step -1
  If Cells(r, 1) <> Cells(r - 1, 1) Then Rows(r).Insert
Next r
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SortSeparateURLs macro.


If the above macro does not work correctly, then please attach your workbook:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.