If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Matching
a1..e711 contains data in this format:
Part-nbr Date Amount 008101815-8 03/12/2001 751.72 008101816-9 03/12/2001 514.50 008101817-0 03/12/2001 542.67 008101819-2 03/12/2001 440.61 008102268-7 02/75/2001 1888.05 008102442-7 01/76/2001 1621.50 008102560-0 12/47/2000 3880.07 ================================================== ======== e1..k3074 contains data in this format: Part-nbr Ref-nbr year type status tax pen 008101816-9 008101816-9 1997 68-5402 Unpaid 4.88 .48 008101816-9 008101816-9 1997 *TOTAL* Unpaid 29.12 2.89 008101817-0 008101817-0 1993 083-025 Unpaid 21.27 2.12 008101817-0 008101817-0 1993 68-5305 Unpaid 3.39 .33 008101817-0 008101817-0 1993 68-5402 Unpaid 4.88 .48 008101819-2 008101819-2 1999 083-025 paid 16.04 .00 008101819-2 008101819-2 1999 68-5305 paid 1.83 .00 008101819-2 008101819-2 1999 68-5402 paid 2.64 .00 008101819-2 008101819-2 1999 *TOTAL* paid 20.51 .00 008102268-7 008102268-7 1991 005-018 Unpaid 153.25 15.33 008102268-7 008102268-7 1991 68-2357 Unpaid 1.97 .19 ================================================== ========= How do I extract the rows in e1..k3074 whose "Part-nbr" matches the "Part-nbr" in a1..e711? |
#2
|
|||
|
|||
Matching (CORRECTION)
The first block is a1..c711 (not a1..e711).
|
#3
|
|||
|
|||
Matching
gary wrote:
a1..c711 contains data in this format: Part-nbr Date Amount 008101815-8 03/12/2001 751.72 008101816-9 03/12/2001 514.50 008101817-0 03/12/2001 542.67 008101819-2 03/12/2001 440.61 008102268-7 02/75/2001 1888.05 008102442-7 01/76/2001 1621.50 008102560-0 12/47/2000 3880.07 ================================================== ======== e1..k3074 contains data in this format: Part-nbr Ref-nbr year type status tax pen 008101816-9 008101816-9 1997 68-5402 Unpaid 4.88 .48 008101816-9 008101816-9 1997 *TOTAL* Unpaid 29.12 2.89 008101817-0 008101817-0 1993 083-025 Unpaid 21.27 2.12 008101817-0 008101817-0 1993 68-5305 Unpaid 3.39 .33 008101817-0 008101817-0 1993 68-5402 Unpaid 4.88 .48 008101819-2 008101819-2 1999 083-025 paid 16.04 .00 008101819-2 008101819-2 1999 68-5305 paid 1.83 .00 008101819-2 008101819-2 1999 68-5402 paid 2.64 .00 008101819-2 008101819-2 1999 *TOTAL* paid 20.51 .00 008102268-7 008102268-7 1991 005-018 Unpaid 153.25 15.33 008102268-7 008102268-7 1991 68-2357 Unpaid 1.97 .19 ================================================== ========= How do I extract the rows in e1..k3074 whose "Part-nbr" matches the "Part-nbr" in a1..e711? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your worksbook, here are a couple of ways, to be array entered into a range of cells with enough rows to accommodate the maximum number of occurrences of any of the Column A Part-nbr's. Using the Part-nbr from Cell A4 for illustration purposes: =ArrayRowFilter1(E1:K3074,1,A4) or =VLookups(A4,E1:K3074,{1,2,3,4,5,6,7}) Alan Beban |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Matching query | Lori | Running & Setting Up Queries | 1 | June 17th, 2004 12:13 AM |
Join with 2 matching fields | Andrew Black (delete obvious bit) | Running & Setting Up Queries | 2 | June 9th, 2004 08:37 PM |
"Matching" column A's values in column B | Chris.F | Worksheet Functions | 0 | January 12th, 2004 02:24 PM |
Matching data to return value | Craig | Worksheet Functions | 3 | December 31st, 2003 05:37 PM |