Create Unique List

[Delta Star]

I have a workbook with 15 sheets, each sheet contains in excess of 35000 lines of data. The layout of the sheets is identical and customer code is in column B which can appear numerous times on each of the sheets.

How can I quickly create a unique list of customer codes. I had tried using advanced filter and appending to the bottom of a seperate list but these seems a slow way.

Any help would be appreciated.

 

[USCguy09]

Are you trying to return unique list of codes from all 15 sheets or from within each sheet?

When you say "slow" are you trying to get away from manually filtering the data out? IMO, when you have 35,000 lines of data, it prob be quicker just to manually advanced filter it rather than have equations calculate 35,000 times.

I have a workbook with 15 sheets, each sheet contains in excess of 35000 lines of data. The layout of the sheets is identical and customer code is in column B which can appear numerous times on each of the sheets.

How can I quickly create a unique list of customer codes. I had tried using advanced filter and appending to the bottom of a seperate list but these seems a slow way.

Any help would be appreciated.

 

[hiker95]

Delta Star,


If you are using Excel 2003 or earlier, you may run out of rows in worksheet Unique.



Sample raw data in one of the 4 worksheets with a title in cell B1, and the last row is 39,201 with the displayed pattern copied down to the last row:

Excel Workbook
	B
1	customer codes
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	A1
13	A2
14	A3
15	A4
16	A5
17	A6
18	A7
19	A8
20	A9
21	A10
Sheet1

After the macro in a new worksheet Unique:

Excel Workbook
	A
1	Customer Codes
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	A1
13	A2
14	A3
15	A4
16	A5
17	A6
18	A7
19	A8
20	A9
21	A10
22
Unique

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 CreateUniqueList()
' hiker95, 05/04/2011
' http://www.mrexcel.com/forum/showthread.php?t=547861
Dim ws As Worksheet, wU As Worksheet
Dim LR As Long, NR As Long, a As Long, LC As Long, NC As Long, CN As String
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Unique!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Unique"
Set wU = Worksheets("Unique")
wU.UsedRange.Clear
NC = 0
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Unique" Then
    NC = NC + 1
    ws.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wU.Columns(NC), Unique:=True
  End If
Next ws
LC = wU.Cells(1, wU.Columns.Count).End(xlToLeft).Column
wU.Cells(1, LC + 2) = "Customer Codes"
For a = 1 To LC Step 1
  NR = wU.Cells(Rows.Count, LC + 2).End(xlUp).Row + 1
  LR = wU.Cells(Rows.Count, a).End(xlUp).Row
  wU.Cells(NR, LC + 2).Resize(LR - 1).Value = wU.Cells(2, a).Resize(LR - 1).Value
Next a
wU.Columns(LC + 2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wU.Columns(LC + 3), Unique:=True
CN = Replace(Cells(1, LC + 2).Address(0, 0), 1, "")
wU.Columns("A:" & CN).Delete
wU.Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the CreateUniqueList macro.

 

[brettdj]

Another option is my Duplicate Master addin, http://www.experts-exchange.com/A_2123.html which
will give you a unique list across sheets
- true uniques that only occur once, or
- each unique value that occurs

It works on individual cells, or multi-column selections and has options to cater for case insensitive matching/whitespacing ignoring/CLEAN options

Cheers
Dave

 

[jbeaucaire]

I'm not sure how much different this is from Hiker95's, I see his is Adv Filter based, too.

For mine, just add a sheet called LIST to your workbook, then this will make unique list of customer codes in column A on the new LIST sheet.

Option Explicit

Sub UniqueCodes()
Dim wsList As Worksheet, ws As Worksheet
Dim NR As Long, LR As Long

Application.ScreenUpdating = False

Set wsList = Sheets("List")
wsList.Range("A:B").Clear
NR = 1

For Each ws In Worksheets
    If ws.Name <> wsList.Name Then
        LR = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
        ws.Range("B2:B" & LR).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=wsList.Range("B" & NR), Unique:=True
        NR = wsList.Range("B" & wsList.Rows.Count).End(xlUp).Row + 1
    End If
Next ws

With wsList
    .Range("B1:B" & NR).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=wsList.Range("A1"), Unique:=True
    .Range("A:A").Sort .Range("A2"), xlAscending
    .Range("B:B").Clear
    .[A1] = "List"
End With

Application.ScreenUpdating = True
End Sub