Copy rows if greater than zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a sheet where the user selects options up to 12 times, this
automatically generates a part number for each position.
On another sheet I use a list of possible part numbers with COUNTIF
functions in adjacent cells. This works fine, it puts "0" in every adjacent
cell then counts how many times each part number is chosen. (about 40 part
numbers)

What I want to do now is pull out any part numbers that don't have a zero
next to them and put them all in another list on another sheet with the
quantities next to them?Part nos. A77:A128
Quantity (COUNTIF) B77:B128

Thanks in advance
 
Assume source data as specified is in Sheet1

In Sheet2,
Put in A1:
=IF(Sheet1!B77>0,ROW(),"")

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A$77:A$128,SMALL($A:$A,ROW())))
Copy B1 to C1. Select A1:C1, fill down to C52 to cover the extent of source
data in Sheet1. Hide away col A. Cols B and C will return the required
results all neatly bunched at the top. Results will be dynamic to changes in
Sheet1.
 
Works prefectly, Thanks.

Max said:
Assume source data as specified is in Sheet1

In Sheet2,
Put in A1:
=IF(Sheet1!B77>0,ROW(),"")

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A$77:A$128,SMALL($A:$A,ROW())))
Copy B1 to C1. Select A1:C1, fill down to C52 to cover the extent of source
data in Sheet1. Hide away col A. Cols B and C will return the required
results all neatly bunched at the top. Results will be dynamic to changes in
Sheet1.
Click to expand...
 
Back
Top