Copy selected lines

[WTG]

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

 

[Bob Tarburton]

in Sheet2!A1
=MATCH("rmb",Sheet1!B$1:B$500,0)
in Sheet2!A2
=MATCH(1,INDEX((Sheet1!B$1:B$500="rmb")*(ROW(Sheet1!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]

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

 

[WTG]

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

 

[WTG]

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(Sheet1!B$1:B$500)>A1),0),0)

Thanks again for the help.

Wally

 

[WTG]

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

 

[WTG]

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

 

[Bob Tarburton]

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).

 

[WTG]

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

 

[hightide]

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