Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidated Pivot table | Excel Discussion (Misc queries) | |||
Consolidated Workbook | Excel Discussion (Misc queries) | |||
Consolidated Sheets | Excel Worksheet Functions | |||
Creating Consolidated Spreadsheet... | Excel Discussion (Misc queries) | |||
Consolidated Pivot Tables | Excel Discussion (Misc queries) |