none
fetch data from sheet with uniqu name RRS feed

  • Question

  • hi to all

    we have a sheet with a data table include some information :

    cellA         cellB       celC           celD    ....

    Name       Code      weight      Paid      ...

    now, we wanna have a userform with a listbox and a button with clicking button list in listbox rows with unique Name(as Cell A).

    for example if we have 4 rows with Name: BOB just show one of them and its will be excellent if that will be  first row.

    your respond will be appreciated.

    Nima Iranian

    Saturday, November 17, 2012 10:42 AM

All replies

  • You should use collection and bubble sorting.

    Import to Listbox columsA names:

    code:

    Private Sub CommandButton1_Click()
        Dim NoDupes As New Collection
        Dim i As Integer, j As Integer
        Dim Swap1, Swap2, item, max_row&, x&
    
    Application.StatusBar = "Importing..."
        With ActiveSheet
            max_row = .Cells(.Rows.Count, "a").End(xlUp).Row
        
        For x = 2 To max_row
        On Error Resume Next
            NoDupes.Add Cells(x, "a"), CStr(Cells(x, "a"))
        Next
        End With
        On Error GoTo 0
    
        For i = 1 To NoDupes.Count - 1
        DoEvents
        Application.StatusBar = "Sorting ... " & Format(i / NoDupes.Count, "00%")
            For j = i + 1 To NoDupes.Count
                If NoDupes(i) > NoDupes(j) Then
                    Swap1 = NoDupes(i)
                    Swap2 = NoDupes(j)
                    NoDupes.Add Swap1, before:=j
                    NoDupes.Add Swap2, before:=i
                    NoDupes.Remove i + 1
                    NoDupes.Remove j + 1
                End If
            Next j
        Next i
        With ListBox1
            .Clear
            For Each item In NoDupes
                .AddItem item
            Next item
            .ListIndex = 0
        End With
    Application.StatusBar = False
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Saturday, November 17, 2012 1:28 PM
    Answerer
  • thanks for your good answer but your code just shows unique names, but  i want to show more columns with unique name(each row with unique name should be shown in listbox) in listbox :

    name   code   weight   paid  ,...

    pls advise ur idea.

    thanks.

    Saturday, November 17, 2012 1:58 PM
  • Show me more data with effect you want to have.

    Can be a picture, or attached link to file.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by Nima Iranian Saturday, November 17, 2012 2:24 PM
    • Unmarked as answer by Nima Iranian Saturday, November 17, 2012 2:24 PM
    Saturday, November 17, 2012 2:09 PM
    Answerer
  • please see the attached file i explain in ;

    http://s1.picofile.com/file/7558326983/Excel_table_List_box.xlsm.html

    Saturday, November 17, 2012 2:26 PM
  • You have no values in F column - that could be a clue? I ques, because you do not sum data in C and D column.

    If thats true you can use simple if to add this data to listbox:

    Private Sub UserForm_Initialize()
        Dim max_row&, x&, y&
        ListBox1.ColumnCount = 6
        ListBox1.ColumnWidths = "20, 40, 40, 30, 50"
        
        With ActiveSheet
            max_row = .Cells(.Rows.Count, "a").End(xlUp).Row
            For x = 6 To max_row 'yor data start from 6 row in that sample.
                If Len(.Cells(x, "f")) > 0 Then
                ListBox1.AddItem ""
                    For y = 1 To 6 'columns
                        ListBox1.Column(y - 1, ListBox1.ListCount - 1) = .Cells(x, y)
                    Next y
                End If
            Next x
        End With
    End Sub
    

    form:


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Saturday, November 17, 2012 2:43 PM
    Answerer
  • pls see attached file there are some rows  with duplicated name in listbox when we extend our data :(

    http://s1.picofile.com/file/7558351612/Excel_table_List_box.xlsm.html

    Saturday, November 17, 2012 3:04 PM
  • ok little modification and dada

    Private Sub UserForm_Initialize()
    Dim max_row&, x&, y&, el, jest As Boolean
    ListBox1.ColumnCount = 6
    'ListBox1.ColumnWidths = "20, 40, 40, 30, 50"
    
    With ActiveSheet
        max_row = .Cells(.Rows.Count, "a").End(xlUp).Row
        For x = 2 To max_row 'yor data start from 6 row in that sample.
            If Len(.Cells(x, "f")) > 0 Then
                For Each el In ListBox1.List
                    If el = .Cells(x, "b") Then jest = True: Exit For
                Next el
                If jest = False Then
                ListBox1.AddItem ""
                    For y = 1 To 6 'columns
                        ListBox1.Column(y - 1, ListBox1.ListCount - 1) = .Cells(x, y)
                    Next y
                End If
            End If
        Next x
    End With
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Saturday, November 17, 2012 3:23 PM
    Answerer
  • i think it should be work but unfortunately it doesn't work and doesn't cover all my data i add some new rows but it doesn't work properly.

    attached new :

    http://s1.picofile.com/file/7558386341/Excel_table_List_box.xlsm.html

    Saturday, November 17, 2012 3:46 PM
  • ok - take look on this:

    Private Sub UserForm_Initialize()
    Dim max_row&, x&, y&, el, jest As Boolean
    Const col% = 6 'columns
    ListBox1.ColumnCount = col
    ListBox1.ColumnWidths = "20, 40, 40, 30, 50"
    
    With ActiveSheet
        max_row = .Cells(.Rows.Count, "a").End(xlUp).Row
        For x = 2 To max_row
            jest = False
            If Len(.Cells(x, "f")) > 0 Then
                If ListBox1.ListCount > 0 Then
                    For Each el In ListBox1.List
                        If el = .Cells(x, "b") Then jest = True: Exit For
                    Next el
                End If
                If jest = False Then
                ListBox1.AddItem ""
                    For y = 1 To col
                        If IsDate(Cells(x, y)) Then
                            ListBox1.Column(y - 1, ListBox1.ListCount - 1) _
                                = Format(.Cells(x, y), "Short date")
                        ElseIf IsNumeric(.Cells(x, y)) Then
                            ListBox1.Column(y - 1, ListBox1.ListCount - 1) _
                                = Val(.Cells(x, y))
                        Else
                            ListBox1.Column(y - 1, ListBox1.ListCount - 1) _
                                = .Cells(x, y)
                        End If
                    Next y
                End If
            End If
        Next x
    End With
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by Nima Iranian Monday, November 19, 2012 5:05 AM
    • Unmarked as answer by Nima Iranian Tuesday, December 25, 2012 6:31 AM
    Saturday, November 17, 2012 6:13 PM
    Answerer
  • thanks for above code, now if we wanna have sum of one column (for eg column D (CBM)) for each unique  name what should we do?


    Sunday, November 18, 2012 5:34 AM
  • This part responsible for collection is loop For:

    For y = 1 To col

    You should treat the variable Y as separate column, so you're interested D = 4

    On the beginning add new variable sum_D or whatever and In this loop you should adding the values from column D:

    Dim sum_d as double '<- that new declaration
    '...
    'loop for...
    '...
       End If
       if y = 4 then sum_d = sum_d + .cells(x,y) '<- this line
    Next y
    '...



    Now you can use sum_d and assign to object.value or whatever you want.

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Sunday, November 18, 2012 12:29 PM
    Answerer
  • hi again

    with use of this i faced with a new problem:

    how we could do above based on a condition for example if we have a column with date value our condition be date.

    waiting for your advise...

    Tuesday, December 25, 2012 6:22 AM
  • try this way

    if isdate(your_value) = true then '...


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, December 26, 2012 7:48 PM
    Answerer
  • thanks so much think about below:

    we have some data which wanna show in our user form just unique value (as your before given cods work completely), now we wanna show those data that are after date(ex: 2012/11/12) our date column for ex is in F .

    wait for your advise...

    Thursday, December 27, 2012 5:09 AM