JoeDelcambre
New Member
- Joined
- Feb 20, 2022
- Messages
- 16
- Office Version
- 365
- Platform
- Windows
I am trying to write two macros to find two sets of keywords on Sheet 3 and paste entire rows to either Sheets 4, 5, or 6, depending on the results.
First, I'll create Sheets 4, 5, and 6 for the results of the macro.
First query/macro:
Search Sheet 3 only.
Sheet 4 can only have terms from "Set 1" (listed below). If the query finds a row where there are words from only Set 1, results should be pasted on Sheet 4.
If the query finds a row with words from both Sets 1 and 2, those lines should pasted into Sheet 6.
The keywords are:
Set 1
"abc"
"def"
"xyz"
Second query/macro:
Search Sheet 3 only.
Sheet 5 can only have terms from "Set 2". If results from this query finds a row with only words from Set 2, results should be pasted on Sheet 5. If the query finds a row with words from both Sets 1 and 2, those lines should pasted into Sheet 6.
Set 2
"apple"
"banana"
"orange"
Sheet 6 can only have lines with at least one term from both Sets 1 and 2.
Any and all help is greatly appreciated.
First, I'll create Sheets 4, 5, and 6 for the results of the macro.
First query/macro:
Search Sheet 3 only.
Sheet 4 can only have terms from "Set 1" (listed below). If the query finds a row where there are words from only Set 1, results should be pasted on Sheet 4.
If the query finds a row with words from both Sets 1 and 2, those lines should pasted into Sheet 6.
The keywords are:
Set 1
"abc"
"def"
"xyz"
Second query/macro:
Search Sheet 3 only.
Sheet 5 can only have terms from "Set 2". If results from this query finds a row with only words from Set 2, results should be pasted on Sheet 5. If the query finds a row with words from both Sets 1 and 2, those lines should pasted into Sheet 6.
Set 2
"apple"
"banana"
"orange"
Sheet 6 can only have lines with at least one term from both Sets 1 and 2.
Any and all help is greatly appreciated.
| macro tests2.xlsm | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | ID | Must_Pay | Funded | Agency | Directorate_ID | Division_ID | Acq_Package | OS | Amount_Paid | Amount_Owed | Date_Updated | Start_Date | End_Date | Updated_By | ||
| 2 | ABC568 | yes | yes | USMC | J6 | IOD | USMC-Package 789 | vista | $ 4,000.00 | $ 10,000.00 | 12/25/2021 | 2/5/2020 | 2/1/2022 | John Smith | ||
| 3 | SDJ4478 | yes | no | USMC | J6 | CSD | USMC-Package 789 | XP | $ 2,000.00 | $ 12,000.00 | 11/15/2021 | 2/5/2020 | 2/2/2022 | Jane Doe | ||
| 4 | LIU9898 | no | yes | USA | J3 | CSD | USA-Package 3398J | TTP4 Windows 7 HWLM | $ 3,500.00 | $ 6,500.00 | 3/2/2021 | 3/1/2021 | 2/3/2022 | John Doe | ||
| 5 | HBR5454 | yes | no | USA | J3 | SDD | USA-Package 3398J | Win 10 | $ 2,500.00 | $ 7,500.00 | 6/15/2021 | 4/1/2021 | 2/4/2022 | Jane Smith | ||
| 6 | NHRI2347 | yes | yes | USN | J2 | EOD | USN-Package 923 | Windows 10 | $ 1,200.00 | $ 10,000.00 | 1/22/2022 | 5/5/2020 | 2/5/2022 | Jane Smith | ||
| 7 | NCUR2458 | no | yes | USCG | J6 | CIO | USCG-Package 3287 | Vista | $ 6,500.00 | $ 8,000.00 | 1/6/2022 | 9/4/2020 | 2/6/2022 | Jane Smith | ||
| 8 | LKJ2147 | no | yes | USAF | J3 | CSD | USAF-Package 776534 | Win XP | $ 2,100.00 | $ 3,500.00 | 7/25/2021 | 7/5/2021 | 2/7/2022 | Jane Smith | ||
| 9 | POI6698 | yes | no | USA | J4 | IOD | USA-Package 3398J | win xp | $ 9,800.00 | $ 12,000.00 | 9/2/2021 | 4/16/2020 | 2/8/2022 | Jane Smith | ||
| 10 | ABCS987 | no | no | USAF | J1 | SDD | USAF-Package 776534 | TTP4 Windows 7 HWLM | $ 7,400.00 | $ 8,000.00 | 6/6/2021 | 3/1/2021 | 2/9/2022 | Joe Doe | ||
| 11 | SMN3298 | no | yes | USSF | S4 | CSD | USSF-Package 2312 | Windows 7 | $ 6,500.00 | $ 9,500.00 | 9/22/2021 | 7/5/2021 | 2/10/2022 | Joe Doe | ||
| 12 | LED8852 | no | no | USSF | S4 | IOD | USSF-Package 2312 | Windows 10 | $ 3,500.00 | $ 3,500.00 | 11/2/2021 | 6/7/2020 | 2/11/2022 | John Smith | ||
| 13 | LMN326 | yes | yes | USCG | S2 | CSD | USCG-Package 3287 | Windows XP | $ 2,100.00 | $ 2,500.00 | 7/9/2021 | 3/1/2021 | 2/12/2022 | John Doe | ||
| 14 | WDC589 | no | yes | USN | S6 | CIO | USN-Package 923 | Win 7 | $ 1,750.00 | $ 8,700.00 | 2/1/2022 | 11/9/2020 | 2/13/2022 | Jane Doe | ||
| 15 | VFD365 | yes | yes | USN | S6 | CIO | USN-Package 923 | Win xp | $ 630.00 | $ 6,500.00 | 3/6/2021 | 3/25/2020 | 2/14/2022 | Jane Smith | ||
| 16 | CVF127 | yes | yes | USMC | S3 | CIO | USMC-Package 789 | 74BBN win 7 TTP77 | $ 2,500.00 | $ 3,500.00 | 12/1/2021 | 1/6/2021 | 2/15/2022 | Jane Smith | ||
| 17 | CVRF9863 | yes | no | USN | J2 | SDD | USN-Package 923 | windows 7 | $ 7,800.00 | $ 7,800.00 | 2/2/2022 | 9/7/2020 | 2/16/2022 | Jane Smith | ||
| 18 | MMLK145 | no | no | USMC | J4 | IOD | USMC-Package 789 | windows 10 | $ 9,800.00 | $ 9,800.00 | 3/8/2021 | 3/19/2021 | 2/17/2022 | John Doe | ||
| 19 | EWD441 | yes | yes | USAF | S4 | CSD | USAF-Package 776534 | windows XP | $ 1,200.00 | $ 4,500.00 | 8/5/2021 | 7/7/2020 | 2/18/2022 | John Doe | ||
| 20 | RRB2285 | no | no | USAF | S5 | CSD | USAF-Package 776534 | Windows 7 | $ 3,200.00 | $ 4,500.00 | 9/5/2021 | 9/3/2020 | 2/19/2022 | John Doe | ||
| 21 | ABC568 | yes | orange | abc | J6 | xyz | USMC-Package 789 | vista | $ 4,000.00 | $ 10,000.00 | 12/25/2021 | 2/5/2020 | 2/1/2022 | John Smith | ||
| 22 | SDJ4478 | yes | orange | USMC | J6 | CSD | USMC-Package 789 | XP | $ 2,000.00 | $ 12,000.00 | 11/15/2021 | 2/5/2020 | 2/2/2022 | Jane Doe | ||
| 23 | LIU9898 | no | yes | USA | J3 | xyz | USA-Package 3398J | Windows 7 | $ 3,500.00 | $ 6,500.00 | 3/2/2021 | 3/1/2021 | 2/3/2022 | John Doe | ||
| 24 | HBR5454 | yes | no | abc | J3 | CSD | USA-Package 3398J | Win 10 | $ 2,500.00 | $ 7,500.00 | 6/15/2021 | 4/1/2021 | 2/4/2022 | Jane Smith | ||
| 25 | NHRI2347 | yes | banana | USN | J2 | EOD | USN-Package 923 | Windows 10 | $ 1,200.00 | $ 10,000.00 | 1/22/2022 | 5/5/2020 | 2/5/2022 | Jane Smith | ||
| 26 | NCUR2458 | no | yes | abc | J6 | xyz | USCG-Package 3287 | Vista | $ 6,500.00 | $ 8,000.00 | 1/6/2022 | 9/4/2020 | 2/6/2022 | Jane Smith | ||
| 27 | LKJ2147 | no | yes | USAF | J3 | CSD | USAF-Package 776534 | Win XP | $ 2,100.00 | $ 3,500.00 | 7/25/2021 | 7/5/2021 | 2/7/2022 | Jane Smith | ||
| 28 | POI6698 | yes | apple | USA | J4 | IOD | USA-Package 3398J | win xp | $ 9,800.00 | $ 12,000.00 | 9/2/2021 | 4/16/2020 | 2/8/2022 | Jane Smith | ||
| 29 | ABCS987 | no | no | abc | J1 | SDD | USAF-Package 776534 | Windows 7 | $ 7,400.00 | $ 8,000.00 | 6/6/2021 | 3/1/2021 | 2/9/2022 | Joe Doe | ||
| 30 | SMN3298 | no | yes | USSF | S4 | CSD | USSF-Package 2312 | Windows 7 | $ 6,500.00 | $ 9,500.00 | 9/22/2021 | 7/5/2021 | 2/10/2022 | Joe Doe | ||
| 31 | LED8852 | no | no | def | S4 | xyz | USSF-Package 2312 | Windows 10 | $ 3,500.00 | $ 3,500.00 | 11/2/2021 | 6/7/2020 | 2/11/2022 | John Smith | ||
| 32 | LMN326 | yes | apple | abc | S2 | CSD | USCG-Package 3287 | Windows XP | $ 2,100.00 | $ 2,500.00 | 7/9/2021 | 3/1/2021 | 2/12/2022 | John Doe | ||
| 33 | WDC589 | no | USN | S6 | def | USN-Package 923 | 74BBN Win 7 TTP77 | $ 1,750.00 | $ 8,700.00 | 2/1/2022 | 11/9/2020 | 2/13/2022 | Jane Doe | |||
| 34 | VFD365 | yes | yes | USN | S6 | xyz | USN-Package 923 | Win xp | $ 630.00 | $ 6,500.00 | 3/6/2021 | 3/25/2020 | 2/14/2022 | Jane Smith | ||
| 35 | CVF127 | yes | apple | abc | S3 | xyz | USMC-Package 789 | win 7 | $ 2,500.00 | $ 3,500.00 | 12/1/2021 | 1/6/2021 | 2/15/2022 | Jane Smith | ||
| 36 | CVRF9863 | yes | no | USN | J2 | SDD | USN-Package 923 | windows 7 | $ 7,800.00 | $ 7,800.00 | 2/2/2022 | 9/7/2020 | 2/16/2022 | Jane Smith | ||
| 37 | MMLK145 | no | no | abc | J4 | IOD | USMC-Package 789 | windows 10 | $ 9,800.00 | $ 9,800.00 | 3/8/2021 | 3/19/2021 | 2/17/2022 | John Doe | ||
| 38 | EWD441 | yes | yes | USAF | S4 | CSD | USAF-Package 776534 | windows XP | $ 1,200.00 | $ 4,500.00 | 8/5/2021 | 7/7/2020 | 2/18/2022 | John Doe | ||
| 39 | RRB2285 | no | no | def | S5 | CSD | USAF-Package 776534 | Windows 7 | $ 3,200.00 | $ 4,500.00 | 9/5/2021 | 9/3/2020 | 2/19/2022 | John Doe | ||
Sheet1 | ||||||||||||||||
