Listbox search function without duplicates

dss28 · Aug 19, 2021

I am enclosing my two sheets - first master data and second sheet showing a tabulated result which I want in a list box.
In the master sheet I have data in column A to R.
The columns F, G, R have same values for a one Patient ID (in column F). rest of the columns have different values for a given Patient ID

I want a listbox to show results of only columns F, G, R but in a single row without duplicates. (summary type)

search listbox data.xlsx

A

2

MastData

search listbox data.xlsx

J

19

ListBoxResult

can any one guide in this please.

dss28 · Aug 19, 2021

dss28 said:
I am enclosing my two sheets - first master data and second sheet showing a tabulated result which I want in a list box.
In the master sheet I have data in column A to R.
The columns F, G, R have same values for a one Patient ID (in column F). rest of the columns have different values for a given Patient ID

I want a listbox to show results of only columns F, G, R but in a single row without duplicates. (summary type)

search listbox data.xlsx
A
2
MastData

search listbox data.xlsx
J
19
ListBoxResult

can any one guide in this please.

I am using below code to search and display result in the listbox, need to remove the duplicates now and show only one result per each patient ID

VBA Code:

Private Sub cmdSearch_Click()

Dim RowNum As Long
Dim SearchRow As Long

RowNum = 2
SearchRow = 2

Worksheets("MastData").Activate

Do Until Cells(RowNum, 6).Value = ""

    If InStr(1, Cells(RowNum, 6).Value, txtKeywords.Value, vbTextCompare) > 0 Then     ' results to appear in Search sheet
        Worksheets("Search").Cells(SearchRow, 1).Value = Cells(RowNum, 6).Value    ' column F
        Worksheets("Search").Cells(SearchRow, 2).Value = Cells(RowNum, 7).Value    ' column G
        Worksheets("Search").Cells(SearchRow, 3).Value = Cells(RowNum, 18).Value     ' column R

        SearchRow = SearchRow + 1
    End If
    RowNum = RowNum + 1
Loop

If SearchRow = 2 Then
    MsgBox "No products were found that match your search criteria."
    Exit Sub
End If


Sheet3.Activate
With Sheet3

ListBox1.ColumnCount = 3
ListBox1.RowSource = "A2:C9999"
ListBox1.ColumnHeads = True
ListBox1.TextAlign = fmTextAlignLeft
ListBox1.SpecialEffect = fmSpecialEffectSunken
ListBox1.ColumnWidths = "120,120,120"
ListBox1.ListStyle = fmListStyleOption


'============================
End With


End Sub

Akuini · Aug 19, 2021

@dss28
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive? And then share the link here.
It will make it easier to test & find a solution.

dss28 · Aug 19, 2021

Akuini said:
@dss28
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive? And then share the link here.
It will make it easier to test & find a solution.

I have attached the xl2bb files of both the sheets in my first initial post. if that is not visible or downloadable, will attach once again if required

dss28 · Aug 19, 2021

search listbox data.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

2

Patient A

Cont 1

Val 1

3

Patient A

Cont 1

Val 1

4

Patient A

Cont 1

Val 1

5

Patient A

Cont 1

Val 1

6

Patient A

Cont 1

Val 1

7

Patient A

Cont 1

Val 1

8

Patient B

Cont 2

Val 22

9

Patient B

Cont 2

Val 22

10

Patient B

Cont 2

Val 22

11

Patient B

Cont 2

Val 22

12

Patient B

Cont 2

Val 22

13

Patient B

Cont 2

Val 22

14

Patient B

Cont 2

Val 22

15

Patient B

Cont 2

Val 22

16

Patient B

Cont 2

Val 22

17

Patient C

cont 3

Val 33

18

Patient C

cont 3

Val 33

19

Patient C

cont 3

Val 33

20

Patient D

cont 4

Val44

21

Patient D

cont 4

Val44

22

Patient D

cont 4

Val44

23

Patient D

cont 4

Val44

24

Patient D

cont 4

Val44

25

Patient E

cont 5

Val 55

26

Patient E

cont 5

Val 55

27

Patient E

cont 5

Val 55

28

Patient E

cont 5

Val 55

29

Patient F

cont 6

Val 666

30

Patient F

cont 6

Val 666

MastData

search listbox data.xlsx

A

B

C

1

F

G

R

2

Patient A

Cont 1

Val 1

3

Patient B

Cont 2

Val 22

4

Patient C

cont 3

Val 33

5

Patient D

cont 4

Val44

6

Patient E

cont 5

Val 55

7

Patient F

cont 6

Val 666

ListBoxResult

dss28 · Aug 19, 2021

dss28 said:
search listbox data.xlsx
A B C D E F G H I J K L M N O P Q R
1 A B C D E F G H I J K L M N O P Q R
2 Patient A Cont 1 Val 1
3 Patient A Cont 1 Val 1
4 Patient A Cont 1 Val 1
5 Patient A Cont 1 Val 1
6 Patient A Cont 1 Val 1
7 Patient A Cont 1 Val 1
8 Patient B Cont 2 Val 22
9 Patient B Cont 2 Val 22
10 Patient B Cont 2 Val 22
11 Patient B Cont 2 Val 22
12 Patient B Cont 2 Val 22
13 Patient B Cont 2 Val 22
14 Patient B Cont 2 Val 22
15 Patient B Cont 2 Val 22
16 Patient B Cont 2 Val 22
17 Patient C cont 3 Val 33
18 Patient C cont 3 Val 33
19 Patient C cont 3 Val 33
20 Patient D cont 4 Val44
21 Patient D cont 4 Val44
22 Patient D cont 4 Val44
23 Patient D cont 4 Val44
24 Patient D cont 4 Val44
25 Patient E cont 5 Val 55
26 Patient E cont 5 Val 55
27 Patient E cont 5 Val 55
28 Patient E cont 5 Val 55
29 Patient F cont 6 Val 666
30 Patient F cont 6 Val 666
MastData

search listbox data.xlsx
A B C
1 F G R
2 Patient A Cont 1 Val 1
3 Patient B Cont 2 Val 22
4 Patient C cont 3 Val 33
5 Patient D cont 4 Val44
6 Patient E cont 5 Val 55
7 Patient F cont 6 Val 666
ListBoxResult

after running the macro i get the filtered data in another sheet called "Search" like this

search listbox data.xlsm

A

B

C

1

F

G

R

2

Patient A

Cont 1

Val 1

3

Patient A

Cont 1

Val 1

4

Patient A

Cont 1

Val 1

5

Patient A

Cont 1

Val 1

6

Patient A

Cont 1

Val 1

7

Patient A

Cont 1

Val 1

8

Patient B

Cont 2

Val 22

9

Patient B

Cont 2

Val 22

10

Patient B

Cont 2

Val 22

11

Patient B

Cont 2

Val 22

12

Patient B

Cont 2

Val 22

13

Patient B

Cont 2

Val 22

14

Patient B

Cont 2

Val 22

15

Patient B

Cont 2

Val 22

16

Patient B

Cont 2

Val 22

17

Patient C

cont 3

Val 33

18

Patient C

cont 3

Val 33

19

Patient C

cont 3

Val 33

20

Patient D

cont 4

Val44

21

Patient D

cont 4

Val44

22

Patient D

cont 4

Val44

23

Patient D

cont 4

Val44

24

Patient D

cont 4

Val44

25

Patient E

cont 5

Val 55

26

Patient E

cont 5

Val 55

27

Patient E

cont 5

Val 55

28

Patient E

cont 5

Val 55

29

Patient F

cont 6

Val 666

30

Patient F

cont 6

Val 666

Search

the end result in the listbox of the userform should be similar to the data presented in sheet "ListBoxResult"

Akuini · Aug 19, 2021

dss28 said:
the end result in the listbox of the userform should be similar to the data presented in sheet "ListBoxResult"

1. So the listbox is in a userform not in the sheet?
2. You want the result in sheet "ListBoxResult" to be transferred to the Listbox.

See if this work.
1. In the userform module, use this code:

VBA Code:

Private Sub UserForm_Initialize()
With ListBox1
    .ColumnCount = 3
    .RowSource = ""
    .ColumnHeads = True
    .TextAlign = fmTextAlignLeft
    .SpecialEffect = fmSpecialEffectSunken
    .ColumnWidths = "120,120,120"
    .ListStyle = fmListStyleOption
End With

With Sheets("ListBoxResult")
    ListBox1.List = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
End With

End Sub

2. Remove the code for sheet3

VBA Code:

With Sheet3

ListBox1.ColumnCount = 3
ListBox1.RowSource = "A2:C9999"
ListBox1.ColumnHeads = True
ListBox1.TextAlign = fmTextAlignLeft
ListBox1.SpecialEffect = fmSpecialEffectSunken
ListBox1.ColumnWidths = "120,120,120"
ListBox1.ListStyle = fmListStyleOption


'============================
End With

If that doesn't work then I need a sample workbook.

dss28 · Aug 19, 2021

Akuini said:
1. So the listbox is in a userform not in the sheet?
2. You want the result in sheet "ListBoxResult" to be transferred to the Listbox.

See if this work.
1. In the userform module, use this code:

VBA Code:

Private Sub UserForm_Initialize() With ListBox1 .ColumnCount = 3 .RowSource = "" .ColumnHeads = True .TextAlign = fmTextAlignLeft .SpecialEffect = fmSpecialEffectSunken .ColumnWidths = "120,120,120" .ListStyle = fmListStyleOption End With With Sheets("ListBoxResult") ListBox1.List = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value End With End Sub

2. Remove the code for sheet3

VBA Code:

With Sheet3 ListBox1.ColumnCount = 3 ListBox1.RowSource = "A2:C9999" ListBox1.ColumnHeads = True ListBox1.TextAlign = fmTextAlignLeft ListBox1.SpecialEffect = fmSpecialEffectSunken ListBox1.ColumnWidths = "120,120,120" ListBox1.ListStyle = fmListStyleOption '============================ End With

If that doesn't work then I need a sample workbook.

thanks for your valuable time
to be clear in what I want:
1. My workbook has sheets - "MastData" where all data is stored, sheet "Search" where filtered data after running macro is stored temporarily.
2. I have a userform with a textbox to search a particular text in the sheet "MastData" from column F and the results are displayed in the sheet "Search" and the data is captured in the listbox in the userform.
3. I have included this sheet "Search" just to show the data in the listbox. The data in the listbox has many duplicate rows as captured from the "MastData".
3. however I do not want this data as such in the listbox. I need the duplicate rows to be deleted and the final data in the listbox should look like the data presented in sheet "ListBoxSearch" . this sheet "ListBoxSearch" does not exist in my workbook but the tabulated data is just shown here to add clarity how the data in the list box should look like.

Akuini · Aug 19, 2021

Well, I still need a sample workbook.

dss28 · Aug 19, 2021

will share shortly

Listbox search function without duplicates

dss28

Board Regular

Excel Facts

dss28

Board Regular

Akuini

Well-known Member

dss28

Board Regular

dss28

Board Regular

dss28

Board Regular

Akuini

Well-known Member

dss28

Board Regular

Akuini

Well-known Member

dss28

Board Regular

Similar threads

Forum statistics

Share this page

Listbox search function without duplicates

Board Regular

Excel Facts

Board Regular

Well-known Member

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock