help copy and skip blanks using formula

[ulycalapuz]

SHEET 1
[TABLE="width: 250"]
<tbody>[TR]
[TD="align: center"]GIVEN[/TD]
[TD="align: center"]ITEMS[/TD]
[TD="align: center"]PACKING[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]APPLE[/TD]
[TD="align: center"]20 KGS[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]ORANGE[/TD]
[TD="align: center"]20 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]BANANA[/TD]
[TD="align: center"]20 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]GRAPES[/TD]
[TD="align: center"]20 KGS[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]WATERMELON[/TD]
[TD="align: center"]25 KGS[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]STRAWBERRY[/TD]
[TD="align: center"]30 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]PINEAPPLE[/TD]
[TD="align: center"]40 KGS[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]MANGO[/TD]
[TD="align: center"]10 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]POMEGRANATE[/TD]
[TD="align: center"]5 KGS[/TD]
[/TR]
</tbody>[/TABLE]


SHEET 2 - HOW I WANT IT TO BE:[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]GIVEN[/TD]
[TD="align: center"]ITEMS[/TD]
[TD="align: center"]PACKING[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]APPLE[/TD]
[TD="align: center"]20 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]BANANA[/TD]
[TD="align: center"]20 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]GRAPES[/TD]
[TD="align: center"]20 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]PINEAPPLE[/TD]
[TD="align: center"]40 KGS[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]POMEGRANATE[/TD]
[TD="align: center"]5 KGS[/TD]
[/TR]
</tbody>[/TABLE]

​

I would like to make a formula without using VBA where I can automatically copy all given row with number value and skip blanks and copy it's column b and c at the same time also.
Thank you in advance.

 

[bosco_yip]

1] In "Sheet2" A2, copied down :

=IFERROR(INDEX(Sheet1!A$2:A$100,AGGREGATE(15,6,ROW(Sheet1!A$2:A$100)-ROW(Sheet1!A$1)/(Sheet1!A$2:A$100<>""),ROWS($1:1))),"")

2] In "Sheet2" B2, copied right to C2 and all copied down :

=IF($A2="","",INDEX(Sheet1!B$2:B$100,MATCH($A2,Sheet1!$A$2:$A$100,0)))

Regards
Bosco

 

[ulycalapuz]

Hi Bosco,

Thank you for the reply.
If I have insert the same given number to row A it copies in Row B the first column that it sees.
Anyhow can you send me an e-mail @ ulyca@unicloth.net so I can explain further? Thank you!