| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy selected lines
I have two sheets.
sheet 1 has a list of invoices. on sheet 2 I just want to list certain invoices. so sheet 1 may be like this: 001 rmb $25.00 1356 002 ght $35.02 4568 003 ght $45.23 5689 004 rmb $12.25 4568 005 bnj $1.25 4568 006 sdr $12.54 4568 007 rmb $65.25 4568 009 bnj $56.54 4568 On sheet 2 I need it to pull out all the rmb lines. 001 rmb $25.00 1356 004 rmb $12.25 4568 007 rmb $65.25 4568 I could make sheet 2 in to a list and sort it that way. but what I need to do is pull the information into sheet 2, it's no good being able to sort it on sheet 1. Any Ideas? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
in Sheet2!A1
=MATCH("rmb",Sheet1!B$1:B$500,0) in Sheet2!A2 =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(Sheet 1!B$1:B$500)A1),0),0) Copy A2 down as far as you need to in Sheet2!B1 =IF(ISNA($A1),"",INDEX(Sheet1!A$1:A$500,$A1)) copy down and across as far as you need to go tips 1. of course you can type "rmb" in a seperate cell and point to it. 2. If you adjust the "$1" in column A, make same adjustment to the formulae in the rest of the columns. "WTG" wrote in message ... I have two sheets. sheet 1 has a list of invoices. on sheet 2 I just want to list certain invoices. so sheet 1 may be like this: 001 rmb $25.00 1356 002 ght $35.02 4568 003 ght $45.23 5689 004 rmb $12.25 4568 005 bnj $1.25 4568 006 sdr $12.54 4568 007 rmb $65.25 4568 009 bnj $56.54 4568 On sheet 2 I need it to pull out all the rmb lines. 001 rmb $25.00 1356 004 rmb $12.25 4568 007 rmb $65.25 4568 I could make sheet 2 in to a list and sort it that way. but what I need to do is pull the information into sheet 2, it's no good being able to sort it on sheet 1. Any Ideas? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
Bob,
Thank You very much . I'll give it a try right away. If this does what I need, you've saved me so much time and trouble. Thanks again. Wally On Fri, 10 Feb 2006 17:38:35 -0500, "Bob Tarburton" wrote: in Sheet2!A1 =MATCH("rmb",Sheet1!B$1:B$500,0) in Sheet2!A2 =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(Shee t1!B$1:B$500)A1),0),0) Copy A2 down as far as you need to in Sheet2!B1 =IF(ISNA($A1),"",INDEX(Sheet1!A$1:A$500,$A1)) copy down and across as far as you need to go tips 1. of course you can type "rmb" in a seperate cell and point to it. 2. If you adjust the "$1" in column A, make same adjustment to the formulae in the rest of the columns. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
Bob,
Thank You very much . I'll give it a try right away. If this does what I need, you've saved me so much time and trouble. Thanks again. Wally On Fri, 10 Feb 2006 17:38:35 -0500, "Bob Tarburton" wrote: in Sheet2!A1 =MATCH("rmb",Sheet1!B$1:B$500,0) in Sheet2!A2 =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(Shee t1!B$1:B$500)A1),0),0) Copy A2 down as far as you need to in Sheet2!B1 =IF(ISNA($A1),"",INDEX(Sheet1!A$1:A$500,$A1)) copy down and across as far as you need to go tips 1. of course you can type "rmb" in a seperate cell and point to it. 2. If you adjust the "$1" in column A, make same adjustment to the formulae in the rest of the columns. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
Bob This worked great except for the second formula keeps giving me
the same value as the first. Can you see my error. And I took your advice about pointing to the customer id. =MATCH(C3,Invoices!C$7:C$5000,0) =MATCH("rmb",Sheet1!B$1:B$500,0) =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)*( ROW(Invoices!C$7:C$5000)A6),0),0) =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(Shee t1!B$1:B$500)A1),0),0) Thanks again for the help. Wally On Fri, 10 Feb 2006 17:38:35 -0500, "Bob Tarburton" wrote: in Sheet2!A1 =MATCH("rmb",Sheet1!B$1:B$500,0) in Sheet2!A2 =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(Shee t1!B$1:B$500)A1),0),0) Copy A2 down as far as you need to in Sheet2!B1 =IF(ISNA($A1),"",INDEX(Sheet1!A$1:A$500,$A1)) copy down and across as far as you need to go tips 1. of course you can type "rmb" in a seperate cell and point to it. 2. If you adjust the "$1" in column A, make same adjustment to the formulae in the rest of the columns. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
Can I expand on this formula to include more then one match value?
can I match to customer number between date1 and date2. so out of my invoice list I can call up all the invoices for one certain customer from feb. 15 to march 21. Thanks again for all the help. Wally On Tue, 14 Feb 2006 10:15:26 -0500, WTG wrote: Bob This worked great except for the second formula keeps giving me the same value as the first. Can you see my error. And I took your advice about pointing to the customer id. =MATCH(C3,Invoices!C$7:C$5000,0) =MATCH("rmb",Sheet1!B$1:B$500,0) =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (ROW(Invoices!C$7:C$5000)A6),0),0) =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(She et1!B$1:B$500)A1),0),0) Thanks again for the help. Wally On Fri, 10 Feb 2006 17:38:35 -0500, "Bob Tarburton" wrote: in Sheet2!A1 =MATCH("rmb",Sheet1!B$1:B$500,0) in Sheet2!A2 =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(She et1!B$1:B$500)A1),0),0) Copy A2 down as far as you need to in Sheet2!B1 =IF(ISNA($A1),"",INDEX(Sheet1!A$1:A$500,$A1)) copy down and across as far as you need to go tips 1. of course you can type "rmb" in a seperate cell and point to it. 2. If you adjust the "$1" in column A, make same adjustment to the formulae in the rest of the columns. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
Can I expand on this formula to include more then one match value?
can I match to customer number between date1 and date2. so out of my invoice list I can call up all the invoices for one certain customer from feb. 15 to march 21. Thanks again for all the help. Wally On Tue, 14 Feb 2006 10:15:26 -0500, WTG wrote: Bob This worked great except for the second formula keeps giving me the same value as the first. Can you see my error. And I took your advice about pointing to the customer id. =MATCH(C3,Invoices!C$7:C$5000,0) =MATCH("rmb",Sheet1!B$1:B$500,0) =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (ROW(Invoices!C$7:C$5000)A6),0),0) =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(She et1!B$1:B$500)A1),0),0) Thanks again for the help. Wally On Fri, 10 Feb 2006 17:38:35 -0500, "Bob Tarburton" wrote: in Sheet2!A1 =MATCH("rmb",Sheet1!B$1:B$500,0) in Sheet2!A2 =MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(She et1!B$1:B$500)A1),0),0) Copy A2 down as far as you need to in Sheet2!B1 =IF(ISNA($A1),"",INDEX(Sheet1!A$1:A$500,$A1)) copy down and across as far as you need to go tips 1. of course you can type "rmb" in a seperate cell and point to it. 2. If you adjust the "$1" in column A, make same adjustment to the formulae in the rest of the columns. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
Your first formula
=MATCH(C3,Invoices!C$7:C$5000,0) starts at row 7, so returns a 1 if the first instance is in row 7. In the second formula, you are testing the row of otherwise acceptable records against A6. If if the first instance is in row 7, then the row (7) is greater thatn A6 (1). Adust your second formula so that "A6" takes into account the 6 rows worth of headers, such as "A6+6" or "A6+ROW(Invoices!$C$7)-1" to follow the first row of data if you move it later. =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)*( ROW(Invoices!C$7:C$5000)A6+ROW(Invoices!$C$7)-1),0),0) To add additional criteria, you need to use a formula like the second in place of the first such as =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)*( Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7:E$ 5000<=Sheet1!$E$3),0),0) Where Invoices column E holds the invoice date and Sheet1!D3 and Sheet1!E3 hold your start and end dates (note I used = and <= which INCLUDE the start date and end date). The second formula (and down) is the same as the first except you add back the condition that the row of the next return is greater than the row of the previous return. =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)*( Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7:E$ 5000<=Sheet1!$E$3)*(ROW(Invoices!C$7:C$5000)A6+RO W(Invoices!$C$7)-1),0),0) I tested this only against as much data as you showed in your original post. Let me know if you encounter any additional problems (and make sure your dates are not stored as text). "WTG" wrote in message ... Can I expand on this formula to include more then one match value? can I match to customer number between date1 and date2. so out of my invoice list I can call up all the invoices for one certain customer from feb. 15 to march 21. Bob This worked great except for the second formula keeps giving me the same value as the first. Can you see my error. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
Thanks Bob, this worked great.
Can I bother you for another question? If I make sheet 4 a customer form. after I enter all the customer information, how do I insert it in to the first empty row in my customer list? so if I have 25 customers in my customer list on sheet 10. when I finish filling in the customer in my form on sheet 4 How would I (with a macro I'm guessing) copy the information into the next empty row ( row 26 ) and so on for the next customer and so on and so on.... Thanks for all the help. I turn to the news groups when I need help, but I'm not overly experianced with them. so if I did the wrong thing in asking another question without starting a new thread (I think that's the right term) I'm sorry.. Thanks again Wally On Tue, 14 Feb 2006 12:17:49 -0500, "Bob Tarburton" wrote: Your first formula =MATCH(C3,Invoices!C$7:C$5000,0) starts at row 7, so returns a 1 if the first instance is in row 7. In the second formula, you are testing the row of otherwise acceptable records against A6. If if the first instance is in row 7, then the row (7) is greater thatn A6 (1). Adust your second formula so that "A6" takes into account the 6 rows worth of headers, such as "A6+6" or "A6+ROW(Invoices!$C$7)-1" to follow the first row of data if you move it later. =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (ROW(Invoices!C$7:C$5000)A6+ROW(Invoices!$C$7)-1),0),0) To add additional criteria, you need to use a formula like the second in place of the first such as =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7:E $5000<=Sheet1!$E$3),0),0) Where Invoices column E holds the invoice date and Sheet1!D3 and Sheet1!E3 hold your start and end dates (note I used = and <= which INCLUDE the start date and end date). The second formula (and down) is the same as the first except you add back the condition that the row of the next return is greater than the row of the previous return. =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7:E $5000<=Sheet1!$E$3)*(ROW(Invoices!C$7:C$5000)A6+R OW(Invoices!$C$7)-1),0),0) I tested this only against as much data as you showed in your original post. Let me know if you encounter any additional problems (and make sure your dates are not stored as text). |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy selected lines
No problem asking another question.
After filing out the form, I would try recording a new macro without trying to figure out the VBA code (Go to tools/Macro/Record New Macro) When recording try to use keystrokes rather than mouse. You might have use Cotrol+Arrow down or something like that to get to bottom row of sheet 10 (VBA might try to always send you to the same row when you run it again) and you might want to use GoTo comands (Ctrl+G) to navigate sheet 4. Just copy-Paste, Copy Paste, etc. Once you've done it once, you can add a button from the forms toolbar adn the macro to it. You might want to try 1 copy-paste first and see what happens next time you try it. If you can't get VBA to go to the next available row, then restate your question in the excel.programming newsgroup. If your specific, which cell on sheet 4 goes to which column on sheet 10, someone will probably post the complete code for you. Good luck On Thu, 16 Feb 2006 14:20:21 -0500, WTG wrote: Thanks Bob, this worked great. Can I bother you for another question? If I make sheet 4 a customer form. after I enter all the customer information, how do I insert it in to the first empty row in my customer list? so if I have 25 customers in my customer list on sheet 10. when I finish filling in the customer in my form on sheet 4 How would I (with a macro I'm guessing) copy the information into the next empty row ( row 26 ) and so on for the next customer and so on and so on.... Thanks for all the help. I turn to the news groups when I need help, but I'm not overly experianced with them. so if I did the wrong thing in asking another question without starting a new thread (I think that's the right term) I'm sorry.. Thanks again Wally On Tue, 14 Feb 2006 12:17:49 -0500, "Bob Tarburton" wrote: Your first formula =MATCH(C3,Invoices!C$7:C$5000,0) starts at row 7, so returns a 1 if the first instance is in row 7. In the second formula, you are testing the row of otherwise acceptable records against A6. If if the first instance is in row 7, then the row (7) is greater thatn A6 (1). Adust your second formula so that "A6" takes into account the 6 rows worth of headers, such as "A6+6" or "A6+ROW(Invoices!$C$7)-1" to follow the first row of data if you move it later. =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3) *(ROW(Invoices!C$7:C$5000)A6+ROW(Invoices!$C$7)-1),0),0) To add additional criteria, you need to use a formula like the second in place of the first such as =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3) *(Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7: E$5000<=Sheet1!$E$3),0),0) Where Invoices column E holds the invoice date and Sheet1!D3 and Sheet1!E3 hold your start and end dates (note I used = and <= which INCLUDE the start date and end date). The second formula (and down) is the same as the first except you add back the condition that the row of the next return is greater than the row of the previous return. =MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3) *(Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7: E$5000<=Sheet1!$E$3)*(ROW(Invoices!C$7:C$5000)A6+ ROW(Invoices!$C$7)-1),0),0) I tested this only against as much data as you showed in your original post. Let me know if you encounter any additional problems (and make sure your dates are not stored as text). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| grid lines - off in selected areas | Excel Discussion (Misc queries) | |||
| Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
| How can I copy cell formats in functions? | Excel Worksheet Functions | |||
| Copy without Hidden Cols - How | Excel Discussion (Misc queries) | |||
| Copy and Paste and keep format the same | Excel Discussion (Misc queries) | |||
Linear Mode
