Unique List excluding "-"

[reighzaigne]

Hi all,
I am working on a database of suppliers which basically looks like this:

[TABLE="class: grid, width: 418"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Client[/TD]
[TD]HMUA[/TD]
[TD]Gown[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]D[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]D[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]A[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]D[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]B[/TD]
[TD]W[/TD]
[/TR]
</tbody>[/TABLE]


So basically, in another sheet, I wanted to get the unique names from "HMUA" and then "Gowns" on separate columns. The original file will have "-" characters and a lot of blank cells.

I would also want to exclude the "-" from the unique list if possible.

If it's also possible to have a formula that extracts the list alphabetically? but it's not required.

Please share formulas only as I don't know how to do VBA.

 

[hiker95]

reighzaigne,

I know you asked for a formula solution, but, none of the formula gurus have responded.


1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider.

I assume that both worksheets Suppliers, and, Uniques, already exist, and, worksheet Uniques has titles in row 1.

You can change the worksheet names in the macro.

Sample worksheets:

Excel 2007
	A	B	C
1	Client	HMUA	Gowns
2	1	A	W
3	2	A	R
4	3	D	S
5	4	C	Q
6	5	B	W
7	6	-	-
8	7	D
9	8
10	9	C	Q
11	10	-	-
12	11	D	S
13	12		W
14	13	A	R
15	14	D	R
16	15	B	W
17
Suppliers

Excel 2007
	A	B
1	HMUA	Gowns
2
3
4
5
6
Uniques

And, after the macro:

Excel 2007
	A	B
1	HMUA	Gowns
2	A	Q
3	B	R
4	C	S
5	D	W
6
Uniques

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetUniques()
' hiker95, 02/11/2016, ME920689
Dim ws As Worksheet, wu As Worksheet
Dim r As Range, h As Range, g As Range, lru As Long, nra As Long, nrb As Long
Application.ScreenUpdating = False
Set ws = Sheets("Suppliers")   '<-- you can change the sheet name here
Set wu = Sheets("Uniques")    '<-- you can change the sheet name here
nra = 1: nrb = 1
With wu
  lru = .Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  If lru > 1 Then .Range("A2:B" & lru).ClearContents
End With
With ws
  For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp))
    If Not r.Offset(, 1).Value = vbEmpty And Not r.Offset(, 1).Value = "-" Then
      Set h = wu.Columns(1).Find(r.Offset(, 1).Value, LookAt:=xlWhole)
      If h Is Nothing Then
        nra = nra + 1
        wu.Cells(nra, 1).Value = r.Offset(, 1).Value
      End If
    End If
    If Not r.Offset(, 2).Value = vbEmpty And Not r.Offset(, 2).Value = "-" Then
      Set g = wu.Columns(2).Find(r.Offset(, 2).Value, LookAt:=xlWhole)
      If g Is Nothing Then
        nrb = nrb + 1
        wu.Cells(nrb, 2).Value = r.Offset(, 2).Value
      End If
    End If
  Next r
End With
With wu
  .Range(.Cells(2, 1), .Cells(nra, 1)).Sort key1:=.Range("A2"), order1:=1
  .Range(.Cells(2, 2), .Cells(nrb, 2)).Sort key1:=.Range("B2"), order1:=1
  .Columns(1).Resize(, 2).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the GetUniques macro.

 

[FDibbins]

I put the answers on sheet6 (and they are sorted)...
Sheet5...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Client[/td][td]HMUA[/td][td]Gown[/td][/tr]

[tr][td]

2​

[/td][td]1[/td][td]A[/td][td]W[/td][/tr]

[tr][td]

3​

[/td][td]2[/td][td]A[/td][td]R[/td][/tr]

[tr][td]

4​

[/td][td]3[/td][td]D[/td][td]S[/td][/tr]

[tr][td]

5​

[/td][td]4[/td][td]C[/td][td]Q[/td][/tr]

[tr][td]

6​

[/td][td]5[/td][td]B[/td][td]W[/td][/tr]

[tr][td]

7​

[/td][td]6[/td][td]-[/td][td]-[/td][/tr]

[tr][td]

8​

[/td][td]7[/td][td]D[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]8[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]9[/td][td]C[/td][td]Q[/td][/tr]

[tr][td]

11​

[/td][td]10[/td][td]-[/td][td]-[/td][/tr]

[tr][td]

12​

[/td][td]11[/td][td]D[/td][td]S[/td][/tr]

[tr][td]

13​

[/td][td]12[/td][td][/td][td]W[/td][/tr]

[tr][td]

14​

[/td][td]13[/td][td]A[/td][td]R[/td][/tr]

[tr][td]

15​

[/td][td]14[/td][td]D[/td][td]R[/td][/tr]

[tr][td]

16​

[/td][td]15[/td][td]B[/td][td]W[/td][/tr]
[/table]
Shhet...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]A[/td][td]Q[/td][/tr]

[tr][td]

3​

[/td][td]B[/td][td]R[/td][/tr]

[tr][td]

4​

[/td][td]C[/td][td]S[/td][/tr]

[tr][td]

5​

[/td][td]D[/td][td]W[/td][/tr]
[/table]
A2=IFERROR(INDEX(Sheet5!B$2:B$16,MATCH(0,COUNTIFS(Sheet5!B$2:B$16,"<>-",Sheet5!B$2:B$16,"<"&Sheet5!B$2:B$16)-SUM(COUNTIF(Sheet5!B$2:B$16,A$1:A1)),0)),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
Then copied down and across as needed

(adjust ranges as needed)