need copy data according to other value in other cell

[mazan2010]

Dear All
i would like to copy data for only name present in column J that represent for column I
the data
A23:g35 copy from A7:A20 but remove the absent from this in represented date

[Special-K]

in A24
=IFERROR(INDEX($A$8:$G$20,SMALL(IF(($J$8:$J$20="Present"),ROW($A$1:$A$13)),ROW(A1)),COLUMN()),"")
Array formula, use Ctrl-Shift-Enter
copy down and across to G35

[mazan2010]

Dear Special-K
its working good if look only one date but if the date change for example in my new file it's not working
i need look to absent and present in the represent date

[JohnTopley]

in A24

=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$J$7),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

copy across and down

Date check added

[mazan2010]

its not working please my attached file

[Special-K]

Dear Special-K
its working good if look only one date but if the date change for example in my new file it's not working
i need look to absent and present in the represent date

You've added a date column that wasnt there in the original
I dont have time to come up with a solution for each little amendment you decide to make.
Perhaps someone else can solve this.

[mazan2010]

thanks for your attention and reply

[JohnTopley]

See post #4

[mazan2010]

Dear JohnTopley
its not working if i change the date
can see my attached file
i found this post in other site but can't modify it to fit my needs

https://stackoverflow.com/questions/...-results-sheet

[mazan2010]

did you see if this formula can be work or not please

See post #4

[JohnTopley]

If you use the right date reference .....

=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")

OR

=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$K$7),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")

[mazan2010]

dear JohnToply
i put last your formula but still not working
i think you didn't understand me exactly
i have employ at work , using attending (absent or present ) that generate data for employ in case present only
this attending data in column I:M column
hope now you what i need is clear for you

If you use the right date reference .....

=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")

OR

=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$K$7),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")

[JohnTopley]

Did you enter it as an array formula ??????

And you need to change the reference form J to K.

I'll look at INDEX replacement for this.

[mazan2010]

Did you enter it as an array formula ??????

please see my attached file
you will find in row 29 and 31 AAA7 and AAA9 is have data although they are not present from represent Date in column K

[JohnTopley]

Try

=IFERROR(INDEX(A$8:A$20,SMALL(IF((INDEX($J$8:$Z$20,,MATCH($B$2,$J$7:$Z$7,0))="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Formula corrected ....

[mazan2010]

thanks it working very well now
many thanks Dear JohnTolpey
really you all the time help me in my post

[JohnTopley]

Corrected formula in #15

[mazan2010]

yes its work good

[JohnTopley]

I changed K7 to B2 reference in last formula: make sure your formula has this change.

[mazan2010]

not understand exactly what you mean

I changed K7 to B2 reference in last formula: make sure your formula has this change.

[JohnTopley]

=IFERROR(INDEX(A$8:A$20,SMALL(IF((INDEX($J$8:$Z$20,,MATCH($B$2,$J$7:$Z$7,0))="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")

[mazan2010]

two is the same and its work good many thanks

=IFERROR(INDEX(A$8:A$20,SMALL(IF((INDEX($J$8:$Z$20,,MATCH($B$2,$J$7:$Z$7,0))="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")