Trouble creating a formula or a script, for moving rows of info from universe to sheet2 when conditions are met in a certain column.

[rogcar]

Ok here is what I want to do, if anyone can help:

On sheet 1 I have a player universe.
On sheet 2 I have 2 tables setup
1 for position players
1 for pitchers.

I am wanting to take the info from rows 5 and 8 to the position players table, thru Column I.
Also would like the rows of info from sheet 1 to be placed on starting row 4, then the next players info placed on row 5.
(And the condition would be if that position player is owned by a certain owner in column J, the examples i have used for this test is rog in column J as a owner for these 2 position players.)

I am wanting to take the info from rows 7 and 11 to the Pitchers table, thru Column I.
Also would like the rows of info from sheet 1 to be placed on starting row 4, then the next players info placed on row 5.
(And the condition would be if that pitcher player is owned by a certain owner in column J, the examples i have used for this test is rog in column J as a owner for these 2 pitcher players.)

I know this can be done, but i have tried different formulas, with no prevail, I am new to Excel and formulas and also VBA, i do have a VBA script i am using on the Universe page for a external site, also i think maybe this action would be better served with a VBA Script, but i will leave that to the experts.

I have tried a formula for this action, but its not working either, here it is:
=IFERROR(INDEX(Last,SMALL(IF((Owner="rog")*(Roster="MLB")*(RIGHT(Pos,1)<>"P"),ROW(First)-MIN(ROW(First))+1),ROW(1:1))),"")
When i run this formula i get nothing from it, i have also used the CSA to get the brackets around the formula.
I guess i can't leave a attachment, so any help would be greatly appreciated.
I hope i explained the problem good enough.
Thanks for any help.
Rog

 

[rogcar]

Any help is greatly appreciated.

 

[rogcar]

Ok here is a table like the spreadsheet just a sample size though:

Universe sheet​

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 950"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Last
Name[/TD]
[TD]First
Name[/TD]
[TD]DOB[/TD]
[TD]Age[/TD]
[TD]Pos[/TD]
[TD]Team[/TD]
[TD]Length[/TD]
[TD]Salary[/TD]
[TD]Player
Value[/TD]
[TD]Owner[/TD]
[TD]Roster[/TD]
[/TR]
[TR]
[TD]Abad[/TD]
[TD]Fernando[/TD]
[TD]12/17/1985[/TD]
[TD]28[/TD]
[TD]RP[/TD]
[TD]Oak[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$2.57[/TD]
[TD]UFA[/TD]
[TD]UFA[/TD]
[/TR]
[TR]
[TD]Abreu[/TD]
[TD]Jose[/TD]
[TD]1/29/1987[/TD]
[TD]27[/TD]
[TD]1B[/TD]
[TD]CWS[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$10.62[/TD]
[TD]rog[/TD]
[TD]MLB[/TD]
[/TR]
[TR]
[TD]Abreu[/TD]
[TD]Juan[/TD]
[TD]4/8/1985[/TD]
[TD]29[/TD]
[TD]RP [/TD]
[TD]LAD[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.00[/TD]
[TD]UFA[/TD]
[TD]UFA[/TD]
[/TR]
[TR]
[TD]Aceves[/TD]
[TD]Alfredo[/TD]
[TD]12/8/1982[/TD]
[TD]31[/TD]
[TD]RP[/TD]
[TD]Bos[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.00[/TD]
[TD]rog[/TD]
[TD]MLB[/TD]
[/TR]
[TR]
[TD]Ackley[/TD]
[TD]Dustin[/TD]
[TD]2/26/1988[/TD]
[TD]26[/TD]
[TD]2B/LF/CF[/TD]
[TD]Sea[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$3.36[/TD]
[TD]rog[/TD]
[TD]MLB[/TD]
[/TR]
[TR]
[TD]Acosta[/TD]
[TD]Manny[/TD]
[TD]5/1/1981[/TD]
[TD]33[/TD]
[TD]RP[/TD]
[TD]NYM[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.00[/TD]
[TD]UFA[/TD]
[TD]UFA[/TD]
[/TR]
[TR]
[TD]Acta[/TD]
[TD]Manny[/TD]
[TD]1/11/1969[/TD]
[TD]45[/TD]
[TD]Mgr[/TD]
[TD]FA[/TD]
[TD]na[/TD]
[TD]na[/TD]
[TD]$0.00[/TD]
[TD]UFA[/TD]
[TD]Mgr[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Jason[/TD]
[TD]8/4/1991[/TD]
[TD]23[/TD]
[TD]SP[/TD]
[TD]KC[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.00[/TD]
[TD]rog[/TD]
[TD]MLB[/TD]
[/TR]
[TR]
[TD]Adames[/TD]
[TD]Willy[/TD]
[TD]9/2/1995[/TD]
[TD]18[/TD]
[TD]SS[/TD]
[TD]TB[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.00[/TD]
[TD]UFA[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]Adams[/TD]
[TD]Matt[/TD]
[TD]8/31/1988[/TD]
[TD]25[/TD]
[TD]1B[/TD]
[TD]Stl[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$5.03[/TD]
[TD]UFA[/TD]
[TD]MLB[/TD]
[/TR]
[TR]
[TD]Adams[/TD]
[TD]Ryan[/TD]
[TD]4/21/1987[/TD]
[TD]27[/TD]
[TD]2B[/TD]
[TD]Bal[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.00[/TD]
[TD]UFA[/TD]
[TD]UFA[/TD]
[/TR]
[TR]
[TD]Adams[/TD]
[TD]Mike[/TD]
[TD]7/29/1978[/TD]
[TD]36[/TD]
[TD]RP[/TD]
[TD]Phi[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]($0.55)[/TD]
[TD]UFA[/TD]
[TD]MLB[/TD]
[/TR]
[TR]
[TD]Adrianza[/TD]
[TD]Ehire[/TD]
[TD]8/21/1989[/TD]
[TD]25[/TD]
[TD]SS[/TD]
[TD]SF[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.00[/TD]
[TD]UFA[/TD]
[TD]UFA[/TD]
[/TR]
[TR]
[TD]Affeldt[/TD]
[TD]Jeremy[/TD]
[TD]6/6/1979[/TD]
[TD]35[/TD]
[TD]RP[/TD]
[TD]SF[/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD]$0.65[/TD]
[TD]UFA[/TD]
[TD]AAA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sheet 2 = Position Players Table(Position Players only in this table)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 250"]
<tbody>[TR]
[TD][TABLE="width: 552"]
<tbody>[TR]
[TD="class: xl65, width: 70"]Last
Name[/TD]
[TD="class: xl65, width: 69"]First
Name[/TD]
[TD="class: xl66, width: 64"]Age[/TD]
[TD="class: xl66, width: 64"]Pos[/TD]
[TD="class: xl66, width: 68"]Team[/TD]
[TD="class: xl66, width: 75"]Length[/TD]
[TD="class: xl67, width: 73"]Salary[/TD]
[TD="class: xl68, width: 69"]Player
Value[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 950"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2 = Pitchers Table(Pitchers only in the table)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 250"]
<tbody>[TR]
[TD][TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Last
Name[/TD]
[TD="class: xl66, width: 64"]First
Name[/TD]
[TD="class: xl67, width: 64"]Age[/TD]
[TD="class: xl67, width: 64"]Pos[/TD]
[TD="class: xl67, width: 64"]Team[/TD]
[TD="class: xl67, width: 64"]Length[/TD]
[TD="class: xl68, width: 64"]Salary[/TD]
[TD="class: xl69, width: 64"]Player
Value[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]If a row of info has got the name in column Owner, then it needs to be transfered to sheet 2 and the table it should go in depends on if the player is a pitcher or a position player, like in the example in universe table the name (rog) is in owner column in rows 5, 7, 8, 11.
So i need the position players rows (5 AND 8) in sheet 2 in Position Players table, at the starting row in that table ROW(A4).[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Same way with the other 2 rows in universe sheet (7 and 11) go to the Pitchers Table.

Note: only columns DOB AND ROSTER don't need to be added in sheet 2.

Hopefully this helps if i put these tables correctly.
Thanks Rog[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[rogcar]

Am I doing something wrong here, if so please let me know.
Thanks

 

[rogcar]

Ok i am getting nowhere i have looked thru several threads and posts.

All i want to do is get this info in sheet1 A2:I2 into Sheet2 Position Players Table A2:I2 = if "column J" has the name "rog" in it.
Here is the info:
[TABLE="width: 500"]
<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]1[/TD]
[TD]Last name[/TD]
[TD]First name[/TD]
[TD]DOB[/TD]
[TD]AGE[/TD]
[TD]POS.[/TD]
[TD]TEAM[/TD]
[TD]YRS[/TD]
[TD]SALARY[/TD]
[TD]Player Value[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Abreu[/TD]
[TD]Jose[/TD]
[TD]2/2/2[/TD]
[TD]25[/TD]
[TD]RF[/TD]
[TD]CWS[/TD]
[TD]2[/TD]
[TD]$2.00[/TD]
[TD]$2.50[/TD]
[TD]Rog[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried this
Code

=IFERROR(INDEX(Last,SMALL(IF((Owner="rog")*(Roster="MLB")*(RIGHT(Pos,1)<>"P"),ROW(First)-MIN(ROW(First))+1),ROW(1:1))),"")

By the way when i run this formula, i get nothing no errors.

Any Help would be Great and appreciated.
Thanks Rog

 

[rogcar]

Can i get someone to point me in the right direction?
The formula i posted in the above thread should work i think, but i am getting nothing, but i am not that experienced at all in excel or vba , so that is why i am asking.
Thanks

 

[rogcar]

Bump it for Info.