Help with a Formula to move rows to another sheet if column has a certain value.

[rogcar]

Hi
i have a problem creating a formula to move a row of info to another sheet if a column has a certain value, I am new to excel and really don't know where to search.


Table = Wanting to take row 2 info to sheet2 Table if Column J has the name Bill, if the name in Column J is another name it will go to another sheet.
Every buyer will have their own Sheet with a table for info.
[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]X[/TD]
[TD]Age[/TD]
[TD]Type[/TD]
[TD]Brand[/TD]
[TD]Yrs
Owned[/TD]
[TD]Paid[/TD]
[TD]Sell[/TD]
[TD]Buyers Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jones[/TD]
[TD]Joe[/TD]
[TD]X[/TD]
[TD]20[/TD]
[TD]Truck[/TD]
[TD]Ford[/TD]
[TD]5[/TD]
[TD]500.00[/TD]
[TD]750.00[/TD]
[TD]Bill[/TD]
[/TR]
</tbody>[/TABLE]

Table on Sheet 2 is comprised of the following columns
A,B,C,D,E,F,G,H
The "C", "J" columns of info on sheet1, doesn't need to go on sheet 2 table.

Also i would like the info when put into sheet2 table, be on the 2nd row, and if bill is added as buyer again in another Row, then his info for that row would be on the 3rd row in sheet 2, etc., if possible.

I hope I explained it ok, thanks for any help, and your time as well.

 

[dermie_72]

I would use data/filter,
Filter by the value you want, then copy/cut the visible lines to the sheet you want.

 

[rogcar]

I am wanting the info transferred to sheet2, when this sheet is done their will be 2088 rows, Plus their will be up to 20 Buyers sheets, could be more.
I was hoping for a formula that would take the info to sheet2, and place it in the table on the row 2 like the example above.
Is their not a formula that would do this?
Thanks for responding.

 

[Michael M]

The solution suggested would be about the simplest way without VBA.
No, there isn't a formula that will do as required !

 

[rogcar]

Hi Michael

So VBA is the way to go with this action?
I was thinking maybe a INDEX formula would work but i am not up on all the formulas and such.

=IFERROR(INDEX(First,SMALL(IF((Owner="Bill")*(RIGHT(Pos,1),ROW(First)-MIN(ROW(First))+1),ROW(1:1))),"")
something similiar plus it would have to be Row related to sheet 2, if i said that right.

If VBA is the way to go, can you point me in the right direction?
Thanks for your time and response.

 

[dermie_72]

using a filter, or using any formula will return the same number of results, if they meet the criteria. Using the filter option to identify the data that you want to transfer would be the quickest way to do this.
Whether it identifies 10 ot 50,000 items, this is the quickest way without a macro.
You could record a macro of you using the filters, then if you need to re-run this step, you can run the macro with a minimum of fuss.

 

[rogcar]

Hi dermie_72
I guess i thought i would have to just filter the column J, to just names with Bill and then copy the row info, and paste in sheet 2 manually.
Is that what you are suggesting?, or i am not following you.
If that was done i would have to copy 50 rows of the name Bill in the J column, and then go to sheet 2 and paste the info in the correct rows, each time sheet 2 needed updated, which could be at least 2 or 3 times per day, for Bill, what about having to do this for 19 other Buyers at the time.
I am really stuck on this action, i guess.
Thanks for your time and response.

 

[dermie_72]

when you filter, you can highlight the visible data in one block, you don't need to select line by line.
If your formula would identify any cell in column J that has Bill in it. If so, then the result would be the same, however the copy paste function will be much easier by filter than by formula.
If you have multiple criteria, you can use them at the same time.
Set your filter, to exclude the items that don't meet your criteria, click on the row below the heading, Press control + shift + end, copy, goto sheet 2, paste in the location you want.
Repeat as required.

 

[rogcar]

Thanks dermie_72
But i would rather i think just type the name in Bill in Column J when i went in to update sheet 1, and have the formula take the info to sheet 2 Table, row 2, or 3, etc. automatically.
Plus with certain columns in sheet 1, that i don't need in sheet 2.
I guess i will have to work on a formula or a macro, like you say, I will have to research the macro idea, and see if i can do it that way, can you point me in the right direction for studying macro's?
That would be great, and thanks for your time and responses.

 

[dermie_72]

maybe a pivot table is what you're after then.
You can set that up to do exactly what you want, while only showing specific columns.

As to learning macros, depending on your exposure, there are plenty of books noted on this site that are ideal for beginners to experts.