getting right info

  • Thread starter Thread starter Getting right info
  • Start date Start date
G

Getting right info

I have a large list of stock and description and qty on sheet1, and i would
like to extract just stock with qty value >0 to sheet2, which i managed with
=IF(Sheet1!$D$1:$D$20>0,Sheet1!$A$1:$A$24,""), however the distracted info is
scatered across the sheet2 according to fields they been stored in sheet1.
Is there a formula, which would help me to transfer necesary info to sheet2,
as soon qty>0 anywere on sheet1, but so the transfered info would go nicly
one by one in column on sheet2 with no gaps.

I hope you gus understand what i ma trying to say here :)
thanks for help
 
Assume source data in Sheet1, cols A to D,
data from row2 down where the key col is col D (qty)

In Sheet2,
In A2: =IF(Sheet1!D2>0,ROW(),"")
Leave A1 empty. This is your criteria col.

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to E2. Select A2:E2, copy down to cover the max expected extent of
source data, say down to E100. Minimize/hide col A. Cols B to E will return
the required results, with all lines neatly packed at the top as desired (no
gaps).

voila? punch the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Thanks,
it worked :)

Max said:
Assume source data in Sheet1, cols A to D,
data from row2 down where the key col is col D (qty)

In Sheet2,
In A2: =IF(Sheet1!D2>0,ROW(),"")
Leave A1 empty. This is your criteria col.

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to E2. Select A2:E2, copy down to cover the max expected extent of
source data, say down to E100. Minimize/hide col A. Cols B to E will return
the required results, with all lines neatly packed at the top as desired (no
gaps).

voila? punch the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
Click to expand...
 
Back
Top