Consolidated array

[jxbeeman]

How do I Consolidate an array? Let me explain. I’m trying to consolidate an
array based on a single column. So I have say 3 columns with data, (P/N,
Desc, In-stock) (In-stock has a value of either 1 or 0). How would I pull an
array or list with only the ones in stock (value of 1). I’m looking to do
something like a pivot table just with a formula. I know I can just do a
sort but for what I’m going to be using it for will be too manual to repeat.
So I’m looking for a formula that will pull the next value(P/N) out that has
a value of 1 in the in-stock column.
Ex.
P/N Desc In-Stock
1 "--" 1
2 "--" 0
3 "--" 0
4 "--" 1
5 "--" 1
Result i'm looking for (on another sheet)
P/N
1
4
5

Thanks in advance,
Josh

 

[T. Valko]

How many rows of data do you have? If you have 1000's of rows of data a
formula solution will be slow to calculate.

 

[jxbeeman]

I will probably have a couple thousand.

Josh

How many rows of data do you have? If you have 1000's of rows of data a
formula solution will be slow to calculate.

 

[T. Valko]

Try this...

Column A, A2:An = P/N
Column C, C2:Cn = In-Stock

Enter this formula in D2 and copy down to the end of data in column C:

=IF(C2=1,ROW(),"")

Use a formula to get the count of records that meet the criteria:

Formula in F1: =COUNT(D)

Extract the P/N's that are in stock. Enter this formula in F2 and copy down
until you get blanks:

=IF(ROWS(F$2:F2)<=F$1,INDEX(A:A,MATCH(SMALL(D,ROWS(F$2:F2)),D)),"")