Consolidated array
 

How do I Consolidate an array? Let me explain. Im 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). Im looking to do
something like a pivot table just with a formula. I know I can just do a
sort but for what Im going to be using it for will be too manual to repeat.
So Im 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

Consolidated array
 

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.

--
Biff
Microsoft Excel MVP


"jxbeeman" wrote in message
...
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

Consolidated array
 

I will probably have a couple thousand.

Josh

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"jxbeeman" wrote in message
...
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

Consolidated array
 

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: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:D,RO WS(F$2:F2)),D:D)),"")

--
Biff
Microsoft Excel MVP


"jxbeeman" wrote in message
...
I will probably have a couple thousand.

Josh

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"jxbeeman" wrote in message
...
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