Filter all the data for multiple IDs on separate tab Excel 2013

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,538
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Below is raw data

Dunkin New Import - Copy.xlsx
ABCDEFGHIJKLM
1Profit CenterInvoice NumberInvoice DateVendorDescriptionVendor Invoice NumberInvoice AmountDue DateInvoice BalancePayment StatusPayment DateRecipient EmailHold Status
231029612198342/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-310296$1.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
335831712203352/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-358317$1.002/24/2024$0.00Paid2/27/2024ms@goldendonut.netNo
435791312206352/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-357913$1.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
535452512215872/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-354525$1.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
630788112180192/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-307881$2.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
731027712198272/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-310277$2.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
834993012208352/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-349930$3.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
935924412209992/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-359244$4.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
1035924212209982/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-359242$4.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
1136305412203482/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-363054$7.002/24/2024$0.00Paid2/27/2024Hk@goldendonut.netNo
1233201112192982/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-332011$8.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
1334688012199112/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-346880$12.002/24/2024$0.00Paid2/27/2024Ms@goldendonut.netNo
1435973912177242/9/2024DUNKIN' BRANDS BILLINGDD Pint-Size Charitable ContributionBRCHARITY-09FEB24-359739$17.002/19/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
15359242118877292/5/2024NCR CORPORATIONDD NCR Regular300409021$20.242/15/2024$0.00Paid2/15/2024Ms@goldendonut.netNo
16345213118878032/5/2024NCR CORPORATIONDD NCR Regular300408624$48.022/15/2024$0.00Paid2/15/2024Ms@goldendonut.netNo
17307881118840772/2/2024COATES US INCDD COATES QTR24118451$78.002/12/2024$0.00Paid2/13/2024Ms@goldendonut.netNo
18358317118860552/2/2024COATES US INCDD COATES QTR24119554$78.002/12/2024$0.00Paid2/13/2024ms@goldendonut.netNo
19359739118847432/2/2024COATES US INCDD COATES QTR24118661$78.002/12/2024$0.00Paid2/13/2024Ms@goldendonut.netNo
20357913118851912/2/2024COATES US INCDD COATES QTR24119329$78.002/12/2024$0.00Paid2/13/2024Ms@goldendonut.netNo
21359244118859232/2/2024COATES US INCDD COATES QTR24119544$78.002/12/2024$0.00Paid2/13/2024Ms@goldendonut.netNo
22359242118859112/2/2024COATES US INCDD COATES QTR24119519$78.002/12/2024$0.00Paid2/13/2024Ms@goldendonut.netNo
23363054118854682/2/2024COATES US INCDD COATES QTR24119376$78.002/12/2024$0.00Paid2/13/2024Hk@goldendonut.netNo
24349930118834242/2/2024COATES US INCDD COATES QTR24120834$82.682/12/2024$0.00Paid2/13/2024Ms@goldendonut.netNo
25346880119056802/26/2024NCR CORPORATIONDD NCR Regular100676335$118.523/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
26358317118889802/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-358317$159.002/16/2024$0.00Paid2/20/2024ms@goldendonut.netNo
27331152118920222/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-331152$207.462/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
28335411118922062/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-335411$207.462/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
29345213118924732/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-345213$207.462/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
30349930118931732/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-349930$207.462/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
31354525118887942/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-354525$207.462/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
32346880118927202/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-346880$208.182/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
33306008118889832/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-306008$208.282/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
34330704118933342/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-330704$208.652/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
35310277118932412/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-310277$208.652/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
36310092118890842/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-310092$208.652/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
37363054118887112/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-363054$223.462/16/2024$0.00Paid2/20/2024Hk@goldendonut.netNo
38330524118933322/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-330524$232.572/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
39310092119088332/26/2024NCR CORPORATIONDD NCR Quarterly201053568$233.083/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
40363054119097052/26/2024NCR CORPORATIONDD NCR Quarterly201052759$248.893/7/2024$0.00Paid3/7/2024Hk@goldendonut.netNo
41310296118932432/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-310296$251.242/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
42332011118921112/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-332011$251.242/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
43307881118930622/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-307881$254.742/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
44359244118892052/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-359244$255.522/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
45359739118886292/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-359739$256.862/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
46359242118892042/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-359242$256.862/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
47330704119046052/26/2024NCR CORPORATIONDD NCR Quarterly201051805$269.613/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
48363625118856942/2/2024COATES US INCDD COATES QTR24120977$276.062/12/2024$0.00Paid2/13/2024billing@goldendonut.netNo
49357913118889372/6/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-357913$281.872/16/2024$0.00Paid2/20/2024Ms@goldendonut.netNo
50310277119083212/26/2024NCR CORPORATIONDD NCR Quarterly201053975$381.803/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
51331152119068782/26/2024NCR CORPORATIONDD NCR Quarterly201056980$396.933/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
52358317119087632/26/2024NCR CORPORATIONDD NCR Quarterly201054441$401.703/7/2024$0.00Paid3/7/2024ms@goldendonut.netNo
53359242119124672/26/2024NCR CORPORATIONDD NCR Quarterly201059679$404.343/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
54345213119068792/26/2024NCR CORPORATIONDD NCR Quarterly201056981$421.003/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
55357913119105452/26/2024NCR CORPORATIONDD NCR Quarterly201055773$430.423/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
56346880119055122/26/2024NCR CORPORATIONDD NCR Quarterly201054101$446.863/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
57349930119042282/26/2024NCR CORPORATIONDD NCR Quarterly201060827$447.983/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
58306008119118402/26/2024NCR CORPORATIONDD NCR Quarterly201059035$466.063/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
59354525119107552/26/2024NCR CORPORATIONDD NCR Quarterly201056425$475.263/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
60335411119052062/26/2024NCR CORPORATIONDD NCR Quarterly201053216$487.513/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
61332011119052072/26/2024NCR CORPORATIONDD NCR Quarterly201053217$493.513/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
62359244119099932/26/2024NCR CORPORATIONDD NCR Quarterly201056288$500.623/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
63310296119052082/26/2024NCR CORPORATIONDD NCR Quarterly201053218$518.263/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
64359739119125032/26/2024NCR CORPORATIONDD NCR Quarterly201060847$547.613/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
65330524119052092/26/2024NCR CORPORATIONDD NCR Quarterly201053219$562.683/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
66307881119068762/26/2024NCR CORPORATIONDD NCR Quarterly201056978$592.863/7/2024$0.00Paid3/7/2024Ms@goldendonut.netNo
67346880118879872/5/2024NCR CORPORATIONDD NCR Regular100672607$7,160.962/15/2024$0.00Paid2/15/2024Ms@goldendonut.netNo
68331152119023442/23/2024COATES US INCDD COATES REG24123700$22,600.253/4/2024$0.00Paid3/5/2024Ms@goldendonut.netNo
Raw Data



Criteria which meets ID number in column A of raw data, I want all below IDs data to a separate tab using Excel 2013

Dunkin New Import - Copy.xlsx
O
2359739
3359739
4363054
5357913
6306008
7354525
8359242
9310092
10358317
11359244
12363625
Raw Data
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Please try the following on a copy of your workbook. I've assumed you'll always have some IDs in column O to use as filters, starting in O2. I've called the destination worksheet "Destination" - change that to suit.

VBA Code:
Option Explicit
Sub CopyIDs()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Raw Data")
    Set ws2 = Worksheets("Destination") '<--- *** change destination worksheet name to suit ***
    
    If ws1.AutoFilterMode Then ws1.AutoFilter.ShowAllData
    Dim ar, i As Long
    ar = ws1.Range("O2", ws1.Cells(Rows.Count, "O").End(xlUp))
    ar = Application.Transpose(Application.Index(ar, 0, 1))
    For i = LBound(ar) To UBound(ar)
        ar(i) = CStr(ar(i))
    Next i
    
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 1, Array(ar), 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            ws2.Cells.ClearContents
            .Copy ws2.Range("A1")
        Else
            MsgBox "No matching IDs found"
        End If
        .AutoFilter
    End With
End Sub

Using the IDs you listed, you end up with this on the 'Destination' worksheet:

Book1
ABCDEFGHIJKLM
1Profit CenterInvoice NumberInvoice DateVendorDescriptionVendor Invoice NumberInvoice AmountDue DateInvoice BalancePayment StatusPayment DateRecipient EmailHold Status
235831712203352/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-35831712/24/20240Paid2/27/2024ms@goldendonut.netNo
335791312206352/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-35791312/24/20240Paid2/27/2024Ms@goldendonut.netNo
435452512215872/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-35452512/24/20240Paid2/27/2024Ms@goldendonut.netNo
535924412209992/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-35924442/24/20240Paid2/27/2024Ms@goldendonut.netNo
635924212209982/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-35924242/24/20240Paid2/27/2024Ms@goldendonut.netNo
736305412203482/14/2024DUNKIN' BRANDS BILLINGDD JICF PAPERICONDDCHARITY-14FEB24-36305472/24/20240Paid2/27/2024Hk@goldendonut.netNo
835973912177242/09/2024DUNKIN' BRANDS BILLINGDD Pint-Size Charitable ContributionBRCHARITY-09FEB24-359739172/19/20240Paid2/20/2024Ms@goldendonut.netNo
9359242118877292/05/2024NCR CORPORATIONDD NCR Regular30040902120.242/15/20240Paid2/15/2024Ms@goldendonut.netNo
10358317118860552/02/2024COATES US INCDD COATES QTR24119554782/12/20240Paid2/13/2024ms@goldendonut.netNo
11359739118847432/02/2024COATES US INCDD COATES QTR24118661782/12/20240Paid2/13/2024Ms@goldendonut.netNo
12357913118851912/02/2024COATES US INCDD COATES QTR24119329782/12/20240Paid2/13/2024Ms@goldendonut.netNo
13359244118859232/02/2024COATES US INCDD COATES QTR24119544782/12/20240Paid2/13/2024Ms@goldendonut.netNo
14359242118859112/02/2024COATES US INCDD COATES QTR24119519782/12/20240Paid2/13/2024Ms@goldendonut.netNo
15363054118854682/02/2024COATES US INCDD COATES QTR24119376782/12/20240Paid2/13/2024Hk@goldendonut.netNo
16358317118889802/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-3583171592/16/20240Paid2/20/2024ms@goldendonut.netNo
17354525118887942/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-354525207.462/16/20240Paid2/20/2024Ms@goldendonut.netNo
18306008118889832/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-306008208.282/16/20240Paid2/20/2024Ms@goldendonut.netNo
19310092118890842/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-310092208.652/16/20240Paid2/20/2024Ms@goldendonut.netNo
20363054118887112/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-363054223.462/16/20240Paid2/20/2024Hk@goldendonut.netNo
21310092119088332/26/2024NCR CORPORATIONDD NCR Quarterly201053568233.083/07/20240Paid3/07/2024Ms@goldendonut.netNo
22363054119097052/26/2024NCR CORPORATIONDD NCR Quarterly201052759248.893/07/20240Paid3/07/2024Hk@goldendonut.netNo
23359244118892052/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-359244255.522/16/20240Paid2/20/2024Ms@goldendonut.netNo
24359739118886292/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-359739256.862/16/20240Paid2/20/2024Ms@goldendonut.netNo
25359242118892042/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-359242256.862/16/20240Paid2/20/2024Ms@goldendonut.netNo
26363625118856942/02/2024COATES US INCDD COATES QTR24120977276.062/12/20240Paid2/13/2024billing@goldendonut.netNo
27357913118889372/06/2024COMCAST HOLDINGS CORPORATIONStore Network Comcast Monthly193415170-357913281.872/16/20240Paid2/20/2024Ms@goldendonut.netNo
28358317119087632/26/2024NCR CORPORATIONDD NCR Quarterly201054441401.73/07/20240Paid3/07/2024ms@goldendonut.netNo
29359242119124672/26/2024NCR CORPORATIONDD NCR Quarterly201059679404.343/07/20240Paid3/07/2024Ms@goldendonut.netNo
30357913119105452/26/2024NCR CORPORATIONDD NCR Quarterly201055773430.423/07/20240Paid3/07/2024Ms@goldendonut.netNo
31306008119118402/26/2024NCR CORPORATIONDD NCR Quarterly201059035466.063/07/20240Paid3/07/2024Ms@goldendonut.netNo
32354525119107552/26/2024NCR CORPORATIONDD NCR Quarterly201056425475.263/07/20240Paid3/07/2024Ms@goldendonut.netNo
33359244119099932/26/2024NCR CORPORATIONDD NCR Quarterly201056288500.623/07/20240Paid3/07/2024Ms@goldendonut.netNo
34359739119125032/26/2024NCR CORPORATIONDD NCR Quarterly201060847547.613/07/20240Paid3/07/2024Ms@goldendonut.netNo
35
Destination
 
Upvote 1
Hi Kevin

Thank you very much, this is working perfect.

Can we add an option to select destination to paste the result, instead of changing destination sheet name becuase I my colleagues don't have enough knowledge that they go to code and change sheet name and sheet name will very based on different projects.
 
Upvote 0
Will it be pasted to the same cell location on the chosen sheet?
 
Upvote 0
Yes it will be cell A1
But I want if it is possible to choose destination including cell reference, if not then cell A1 is fine.
 
Upvote 0
Another potential option, if you add a sheet named "Criteria" and put he criteria in it as below:
Book1
A
1Profit Center
2359739
3359739
4363054
5357913
6306008
7354525
8359242
9310092
10358317
11359244
12363625
Criteria


Then you could give the below a try:
VBA Code:
Sub test()
    Dim wsRD As Worksheet, wsCr As Worksheet, rngRD As Range, rngCr As Range, rngDest As Range
   
    Set wsRD = Sheets("Raw Data")
    Set wsCr = Sheets("Criteria")
    Set rngRD = wsRD.UsedRange
    Set rngCr = wsCr.UsedRange

    rngRD.AdvancedFilter xlFilterInPlace, rngCr
    Set rngDest = Application.InputBox("Select destination cell", "Select cell", Type:=8)
    rngRD.SpecialCells(xlCellTypeVisible).Copy rngDest
    wsRD.ShowAllData
End Sub
 
Upvote 0
But I want if it is possible to choose destination including cell reference
How exactly do you want this to happen? By input box (can't recommend that method) or by drop down choice? It's difficult to provide any useful advice when we don't know what your exact data layout is (how do you pick the criteria & where is it actually located) how many sheets, what are their names etc. I think post #6 answered your original question
Thank you very much, this is working perfect.
so IMO I feel you may want to break down your additional requirements in new thread(s).
 
Upvote 0
How exactly do you want this to happen? By input box (can't recommend that method) or by drop down choice? It's difficult to provide any useful advice when we don't know what your exact data layout is (how do you pick the criteria & where is it actually located) how many sheets, what are their names etc. I think post #6 answered your original question

so IMO I feel you may want to break down your additional requirements in new thread(s).

I want same like when we select data and insert Pivot Table then it asks for selecting a destination cell where we want to create Pivot Table.

1710499505985.png


But if that is too much critical and needs code that will slow down Excel then I don't need that, I will manage with codes provided above.
 
Upvote 0
That's a bit beyond me, but hopefully someone will step forward and provide your ideal solution.
 
Upvote 0

Forum statistics

Threads
1,215,111
Messages
6,123,155
Members
449,098
Latest member
Doanvanhieu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top