Returning all unique results on filtered data

[123rickfear]

Hi,

not sure the title makes complete sense, but wasn't sure how to word it, so here goes...

I have a spreadsheet with something in the region of 85000 rows, and 35 columns.

I want to pull through all of the the possible results for a column, when filters have been put into place on other columns.

An example, if column A is "Product 1", column B is "Part 1", column C is "Purchase Order", then I want something like this "Supplier 1, Supplier 3, Supplier 5, Supplier 12".

Not worried if the result is a list horizontally or vertically, but it must be within the same cell.

In the example above each of the suppliers should only appear once.

I have tried using Lookup with Countif, but can't seem to get anything other than an error.

I will then need to add in how many times each supplier is there, so the final outcome would be "Supplier 1 x 2, Supplier 3 x 5, Supplier 5 x 1, Supplier 12 x 2". I'm hoping if I can get help with the first part i can have a try myself get to this final solution, but may come back for help...

Thanks

Rick

 

[Fluff]

What column contains the Supplier & what row is the header row on?

 

[123rickfear]

the actual columns are need to use are B, AQ, and X to replace columns A, B and C in my example. The Suppliers are in column AF, and the headers are in row 1.

 

[Fluff]

Ok, how about

Sub rickfear()
   Dim Cl As Range
   Dim i As Long
   Dim Txt As String
   
   With CreateObject("Scripting.dictionary")
      For Each Cl In Range("AF2", Range("AF" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         .Item(Cl.Value) = .Item(Cl.Value) + 1
      Next Cl
      For i = 0 To .Count - 2
         Txt = Txt & vbLf & .Keys()(i) & " x " & .items()(i)
      Next i
   End With
   Range("AH10") = Txt
End Sub

Change AH10 to wherever you want the result

 

[123rickfear]

I'll give this a try, but I don't see where it is specifying only where column B is "Product 1", column AQ is "Part 1", column X is "Purchase Order" ?

 

[Fluff]

When you said

I want to pull through all of the the possible results for a column, when filters have been put into place on other columns.

I was under the impression you had already filtered the data

 

[123rickfear]

OK sorry, that's my poorly worded description. I will need to repeat this process a large number of times, so want to avoid having to put filters on each time if possible.

 

[Fluff]

Ok how about

Function rickfear(CritB As String, CritX As String, CritAQ As String) As String
   Dim Ary As Variant
   Dim i As Long
   Dim Txt As String
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Resize(, 43)).Value2
   
   With CreateObject("Scripting.dictionary")
      .CompareMode = 1
      For i = 2 To UBound(Ary)
         If Ary(i, 2) = CritB And Ary(i, 24) = CritX And Ary(i, 43) = CritAQ Then
            .Item(Ary(i, 32)) = .Item(Ary(i, 32)) + 1
         End If
      Next i
      For i = 0 To .Count - 2
         Txt = Txt & .Keys()(i) & " x " & .items()(i) & Chr(10)
      Next i
      rickfear = Txt & .Keys()(i) & " x " & .items()(i)
   End With
End Function

Used in the sheet like
=rickfear(AS1,AS2,AS3)