Posts by ginobuonpanino

Re: Extract unique values from a range in excel

Yessss! Now it works as I want. Thank you so much!!!

Re: Extract unique values from a range in excel

VBA says that myArray in Next myArray it's a not defined variable.

Quote from pike;751479

try.. this one for speed

Code

option explicit
Sub test5()
    Dim myList As New collection
    Dim myCell, myColl, MyObject, myItem
    Dim myKey As String


    For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
        If Not myCell.Offset(0, 1).Value = vbNullString Then
            myKey = myCell.Value & "~" & myCell.Offset(0, 1).Value
            Set myItem = New collection
            For Each MyObject In Array(myCell.Value, myCell.Offset(0, 1).Value)
                myItem.Add Item:=MyObject
            Next MyObject
            On Error Resume Next
            myList.Add item:=myItem, Key:=myKey
            If Err.Number = 457 Then
                On Error GoTo 0
                myList(myKey).Add item:=myItem
            End If
        End If
    Next myCell


    For Each myColl In myList
        Cells(Rows.Count, "U").End(xlUp).Offset(1, 0).Resize(, 3) = Array(myColl(1), myColl(2), myColl.Count - 1)
    Next myArray
    
    Range("U1").Resize(, 3) = Array("Table", "Name", "Seats")


End Sub

Display More

Re: Extract unique values from a range in excel

Ok, I made some changes, because I don't need to sort results, that's what I'm using now:

Option Explicit
Sub test3()
    Dim myList As New Collection
    Dim myCell, myArray, myObject, Swap1, Swap2
    Dim myKey As String, i As Long, j As Long, s As Long
    Range("F1").Resize(, 3) = Array("Table", "Name", "Seats")
    For Each myCell In Range("A3", Cells(Rows.count, 1).End(xlUp))
        If Not myCell.Offset(0, 1).Value = vbNullString Then
            myKey = myCell.Value & "~" & myCell.Offset(0, 1).Value
            myArray = Array(myCell.Value, myCell.Offset(0, 1).Value, 1)
            On Error Resume Next
            myList.Add Item:=myArray, Key:=myKey
            If Err.Number = 457 Then
                On Error GoTo 0
                myObject = myList.Item(myKey)
                myObject(2) = myObject(2) + 1
                myList.Remove myKey
                myList.Add Item:=Array(myObject(0), myObject(1), myObject(2)), Key:=myKey
            End If
        End If
    Next myCell
         
        Range(Choose(s + 1, "F", "I", "L") & "1").Resize(, 3) = Array("Name", "Table", "Seats")
        For Each myArray In myList
            Cells(Rows.count, Choose(s + 1, "F", "I", "L")).End(xlUp).Offset(1, 0).Resize(, 3) = myArray
        Next myArray
 
     
End Sub

And that's what I found with this code. As You can see It works perfectly, but I can't understand why it continues to write even a blank cell at table 280, I check this cells and they are the same of the others. Every cells in A column contains this formula, in A3, for example, I have put =IF(Name!C5<>0;Name!C5;""), to avoid zero value, I don't if it's because of this formula.

[TABLE="width: 500"]

Name

Table

Seats

Jake

1

8

Jake

1

1

Mark

2

2

Peter

2

3

280

8

[/TABLE]

Re: Extract unique values from a range in excel

OK, Pike's code, seems to work better, just a 2 last question. As you can see, in the new table the title of the third row is 1, can I write seats instead of 1? and why it is considered the last table (280), even if there is nothing on his cells? I give you a screen of the new table generated by the code of Pike

Thanks again to everyone for the help..

Re: Extract unique values from a range in excel

Great! The VBA code was perfect, and works great! :wowee: Thanks a lot!

I have one more question. I know thats it's quite difficult but I want to try. Now I have this situation.

Col A (table) Col B (name)
1 Jack
1 Jack
1 Jack
1 Jack
2 Jack
2
2
2 Ben
3 Ben
3 Ben
3
3
... ...

Col D (table) Col E (name)
1 Jack
2 Jack
2 Ben
3 Ben
... ...

Is there a way to create even a third column where there are the number of times that the Name appears in each group of table (every table has 4 seats), I want to obtain something like this

Col D (table) Col E (name) Col F (Seats)
1 Jack 4
2 Jack 1
2 Ben 1
3 Ben 2
... ... ...

Hi to everyone, I need your help. I have two columns like this:

Col A (table) Col B (name)
1 Jack
1 Jack
1 Jack
1 Jack
2
2
2
2 Ben
3 Ben
3
3
3
... ...

I need to create a new list in which I resume the data like this:

Col A (table) Col B (name)
1 Jack
2 Ben
3 Ben
... ...

So I need to delete duplicates and write 2 new columns in which there are the name of the person and his table. Is it possible? Maybe with a macro?

Thanks for the help