Returning Filtered results

[Happyo]

Hi All,
i am trying to find a a function that can filter a column of supplier codes (the column will have the supplier code in it between 1-??? times) then return it on a new sheet just once along with the supplier description (adjacent cell) eg:

the source sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 379"]
<tbody>[TR]
[TD="class: xl64, width: 70"]20935[/TD]
[TD="class: xl64, width: 309"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 379"]
<tbody>[TR]
[TD="class: xl64, width: 309"]ABC People[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]21330[/TD]
[TD]AshDown[/TD]
[/TR]
[TR]
[TD][TABLE="width: 379"]
<tbody>[TR]
[TD="class: xl64, width: 70"]20935[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 379"]
<tbody>[TR]
[TD="class: xl64, width: 309"]ABC People[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 379"]
<tbody>[TR]
[TD="class: xl64, width: 70"]20935[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 379"]
<tbody>[TR]
[TD="class: xl64, width: 309"]ABC People[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl66, width: 70"]20717[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Multispares[/TD]
[/TR]
[TR]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl66, width: 70"]20717[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Multispares[/TD]
[/TR]
</tbody>[/TABLE]



and the result i want is just the supplier listed once on a new sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl66, width: 70"]20935[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ABC People[/TD]
[/TR]
[TR]
[TD]21330[/TD]
[TD]AshDown[/TD]
[/TR]
[TR]
[TD]20717[/TD]
[TD]Multispares[/TD]
[/TR]
</tbody>[/TABLE]

i hope I have made that clear enough, if not please tell me.

 

[Trebor76]

Hi Happyo,

Welcome to MrExcel!!

Try these seven steps:

1. Filter the dataset as required
2. Highlight (select) the filtered results
3. Press F5 and from the Go To dialog click the Special button
4. From the Go To Special dialog click the Visible cells only radio button and then click OK
5. Press Ctrl + C (i.e. press and hold down the Ctrl button and then press C to get the filtered results pasted onto the clipboard)
6. Right-click on the first cell you want the output to appear and from the shortcut menu select Paste Special
7. Select the desired paste option(s) from the Paste Special dialog and then click OK

Regards,

Robert

 

[Happyo]

Thanks Robert,
I am trying to find a way to automate the process, especially because i can have 1000's of suppliers on any given month and this method gives me the same supplier multiple times, I just want 1 instance of the supplier and supplier code on the next page where my If/then code add's all the costs of that supplier. so basically I am trying to take multiple purchases from X supplier over the month and condense it to X Dollars spent at X supplier.

Hi Happyo,

Welcome to MrExcel!!

Try these seven steps:

1. Filter the dataset as required
2. Highlight (select) the filtered results
3. Press F5 and from the Go To dialog click the Special button
4. From the Go To Special dialog click the Visible cells only radio button and then click OK
5. Press Ctrl + C (i.e. press and hold down the Ctrl button and then press C to get the filtered results pasted onto the clipboard)
6. Right-click on the first cell you want the output to appear and from the shortcut menu select Paste Special
7. Select the desired paste option(s) from the Paste Special dialog and then click OK

Regards,

Robert

 

[Trebor76]

This will produce an unique list into Col. A of sheet 'wsSourceSheet' from a full list of entries in Col. A of sheet 'wsOutputSheet':

Option Explicit
Sub Macro3()

    Dim lngLastRow As Long
    Dim wsSourceSheet As Worksheet
    Dim wsOutputSheet As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsSourceSheet = Sheets("Sheet1") 'Source sheet name. Change to suit.
    Set wsOutputSheet = Sheets("Sheet2") 'Output sheet name. Change to suit.
    
    With wsSourceSheet
        'Assumes the supplier codes are in A1:A[last row in Col. A] of 'wsSourceSheet'. Change to suit.
        lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A1:A" & lngLastRow).Copy Destination:=wsOutputSheet.Range("A1")
        wsOutputSheet.Range("$A$1:$A$" & lngLastRow).RemoveDuplicates Columns:=1, Header:=xlYes
    End With
    
    Set wsSourceSheet = Nothing
    Set wsOutputSheet = Nothing
    
    Application.ScreenUpdating = True

End Sub

Robert

 

[Happyo]

Um... Wow thanks Robert, I'm not sure I understand it but I will give it a go tomorrow..... just have to remember how to do Macro's now....

This will produce an unique list into Col. A of sheet 'wsSourceSheet' from a full list of entries in Col. A of sheet 'wsOutputSheet':

Option Explicit
Sub Macro3()

    Dim lngLastRow As Long
    Dim wsSourceSheet As Worksheet
    Dim wsOutputSheet As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsSourceSheet = Sheets("Sheet1") 'Source sheet name. Change to suit.
    Set wsOutputSheet = Sheets("Sheet2") 'Output sheet name. Change to suit.
    
    With wsSourceSheet
        'Assumes the supplier codes are in A1:A[last row in Col. A] of 'wsSourceSheet'. Change to suit.
        lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A1:A" & lngLastRow).Copy Destination:=wsOutputSheet.Range("A1")
        wsOutputSheet.Range("$A$1:$A$" & lngLastRow).RemoveDuplicates Columns:=1, Header:=xlYes
    End With
    
    Set wsSourceSheet = Nothing
    Set wsOutputSheet = Nothing
    
    Application.ScreenUpdating = True

End Sub

Robert

 

[Trebor76]

No problem.

You could almost record what you need as I've just coded the Remove Duplicates option from the Data Tools tab within the Data ribbon.

 

[Happyo]

Forgot to mention... I'm stuck in office 2013 hell

No problem.

You could almost record what you need as I've just coded the Remove Duplicates option from the Data Tools tab within the Data ribbon.

 

[Trebor76]

That’s good as that’s the environment I coded the solution in ?