Add ListBox item if item exist just once...

[SlyGuy77]

I'm trying to add items that exist just once to a listbox. I'm not talking about only having unique items in my list, I want to exclude items that exist more than once altogether.

example:

Items from worksheet:
Item A
Item B
Item C
Item A

Desired results in Listbox:
Item B
Item C

Hope you can help.

Thanks.

 

[DaveInIowa]

Check out using WorksheetFunction.CountIf in your loop:

    For Each rgItem In rgItems
        If (Application.WorksheetFunction.CountIf(rgItems, rgItem) = 1#) Then
            ListBox1.AddItem rgItem
        End If
    Next

 

[fumei]

Are these "items" cell values? I do not understand. We need more information. Are you building a list, and then putting that list into the listbox? If so, sort the list before putting the "items" into the listbox. How are you getting this list? Here is what I would do, trying to do a similar thing in Word.

Say you have a one column table with:

123
110
123
405
66
405
123
222
678

If I understand correctly, you want the "list" going into the Listbox to :

110
66
222
678

123 and 405 are not included BECAUSE there are two of them. Yes?

So.......

Option Explicit
Function CellText(strIn As String) As String
   CellText = Left(strIn, Len(strIn) - 2)
End Function

Sub NoDups()
Dim aTable As Table
Dim aCell As Cell
Dim MyList As String
Dim MySortedList() As String
Dim TempString As String
Dim var
Set aTable = ActiveDocument.Tables(1)
For Each aCell In aTable.Range.Cells
   TempString = CellText(aCell.Range.Text)
   If InStr(1, MyList, TempString) > 0 Then
[COLOR=red]      ' it is already in string
      ' not only do not include the new one
      ' but REMOVE previous one (and comma)[/color red]
      MyList = Replace(MyList, TempString & ",", "")
   Else
         MyList = MyList & CellText(aCell.Range.Text) & ","
   End If
Next
[COLOR=red]' strip off trailing comma[/color red]
MyList = Left(MyList, Len(MyList) - 1)
[COLOR=red]' make array and add to listbox[/color red]
MySortedList = Split(MyList, ",")
For var = 0 To UBound(MySortedList())
   ListBox1.AddItem MySortedList(var)
Next
End Sub

110
66
222
678

goes into the ListBox.

No doubt it can probably be done easier, and you have to adjust to however you are getting the data in Excel. But essentially, you need to filter, somehow, your original list for duplicates, then do what you normally do to put items in a ListBox.

Gerry

 

[fumei]

No doubt it can probably be done easier

Oh for checking to see if anything has been posted.......

Or that darn delete button we keep asking for....

Gerry

 

[fumei]

Mind you, for my own dim wittedness, Word does not have a COUNTIF.


weak Gerry, very weak.

Gerry

 

[SlyGuy77]

Thanks guys.. I'll give these a try..

Thanks for the quick response..

Sly.

 

[SlyGuy77]

@fumei I gave your code a try and I don't quite understand how I can implement it. I get an error(5) at the function "CellText".. I'm using it in a "form.initialize", data source "workbooks("Data.xls").sheets(1).range("B:B"). How do I modify your code to work this way.. Sorry for the newbie vagueness..

 

[fumei]

My code is for Word, as stated. Word does not have a COUNTIF, which is a much handier thing to use.

The function CellText is a valid function, either in Excel or Word. It simply takes an input string and strips off the last two character.

You need to do this for text coming out of a table cell, as the range of a cell includes the end-of-cell marker. This does not happen in an Excel cell!. An Excel cell value is just that. A Word table cell is NOT the same thing as an Excel cell.

Please post your code. It is hard to say what is wrong at this point.

Gerry

 

[SkipVought]

What is the LOGIC for determining what values from your sheet are in your list?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}