Pulling Data

[Amanda C]

Hi All!

I have a sheet that is set up differently from the normal excel table I use. I am trying to pull only the row with 50 pack in the row (I need the information from the title, QTY and Item ID). I have attached a sample. The one I am working with has 1000s of rows. I know there are 601 rows with this information I am looking for. I used the Find feature to find this information. However, I would like to pull only the rows with the information from above and put them into their own table.

Thank you for all your assistance and please let me know if I wasn't specific enough.

 

[eibi]

Amanda,

See attached.

I've got an array formula something like this:

=INDEX(Sheet1!$B$1:$B$100,SMALL((Sheet1!$C$1:$C$100="50 Pack")*ROW(Sheet1!$C$1:$C$100),ROWS(Sheet1!$C$1:$C$100)-COUNTIF(Sheet1!$C$1:$C$100,"50 Pack")+ROW(A1)))

I admit to hating the last part of the SMALL(....,ROWS(Sheet1!$C$1:$C$100)-COUNTIF(Sheet1!$C$1:$C$100,"50 Pack")+ROW(A1)) -- but I can't remember a slicker way to do it off the top of my head, and if you're like me, you may be looking for a fast solution rather than a smooth one...Give it a try -- see if it does what you need.

All best.

 

[Nebu]

Hi:

You can use the following non-array formulas as well

ID:=IFERROR(INDEX(Sheet1!$B:$B,AGGREGATE(15,6,ROW(Sheet1!$C:$C)-MIN(ROW(Sheet1!$C:$C))+1/(Sheet1!$C:$C="50 Pack"),ROW(A1))),"")

Title:=IFERROR(INDEX(Sheet1!$C:$C,AGGREGATE(15,6,ROW(Sheet1!$C:$C)-MIN(ROW(Sheet1!$C:$C))+1/(Sheet1!$C:$C="50 Pack"),ROW(B1))),"")

QTY:=IFERROR(INDEX(Sheet1!$J:$J,AGGREGATE(15,6,ROW(Sheet1!$C:$C)-MIN(ROW(Sheet1!$C:$C))+1/(Sheet1!$C:$C="50 Pack"),ROW(C1))),"")

Thanks

 

[bosco_yip]

Another option in non-array formula :

1] Sheet2 A1:C1 Column Heading, enter : Item ID, Title and Qty

2] Sheet2 A2, enter formula copy across and down :

=INDEX(Sheet1!$B$1:$L$20000,SMALL(INDEX((Sheet1!$C$1:$C$2000<>"50 Pack")/1%%+ROW($1:$2000),0),ROWS($1:1)),MATCH(A$1,Sheet1!$B$15:$L$15,0))&""

3] see attached file .

Regards
Bosco

 

[eibi]

For the sake of the cause (and for my own future reference): another array solution with simpler construction than my first proposal.

=INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!$C$1:$C$100="50 Pack",ROW(Sheet1!$C$1:$C$100)),ROWS(A$2:A2)))

Attached.