Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXTRACTING UNIQUE RECORD BASED ON CONDITION
Hello everyone!
I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition. The data is as follows: A B MI 70056542 MI 70056543 AP PATRICK CUDAHY INCORPORATED AP PATRICK CUDAHY INCORPORATED AP SUGAR CREEK PACKING CO AP SUGAR CREEK PACKING CO AP VICTORYS KITCHEN AP VICTORYS KITCHEN AP BRIGHT CHEESE HOUSE AP BRIGHT CHEESE HOUSE AP CAPPOLA FOODS INC The final result should look like this: C PATRICK CUDAHY INCORPORATED SUGAR CREEK PACKING CO VICTORYS KITCHEN BRIGHT CHEESE HOUSE CAPPOLA FOODS INC Thanks SJ |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
EXTRACTING UNIQUE RECORD BASED ON CONDITION
I'd do that with a Pivot Table.
This should help http://www.cpearson.com/excel/pivots.htm "SSJ" wrote: Hello everyone! I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition. The data is as follows: A B MI 70056542 MI 70056543 AP PATRICK CUDAHY INCORPORATED AP PATRICK CUDAHY INCORPORATED AP SUGAR CREEK PACKING CO AP SUGAR CREEK PACKING CO AP VICTORYS KITCHEN AP VICTORYS KITCHEN AP BRIGHT CHEESE HOUSE AP BRIGHT CHEESE HOUSE AP CAPPOLA FOODS INC The final result should look like this: C PATRICK CUDAHY INCORPORATED SUGAR CREEK PACKING CO VICTORYS KITCHEN BRIGHT CHEESE HOUSE CAPPOLA FOODS INC Thanks SJ |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
EXTRACTING UNIQUE RECORD BASED ON CONDITION
You could use Excel's advanced filter functionality. Follow the menu path
Data / Filter / Advanced filter. Fill out the appropriate information and ensure to check the unique records only box. Look up Advanced Filter on Excel help. Hope that gives you a start. "SSJ" wrote: Hello everyone! I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition. The data is as follows: A B MI 70056542 MI 70056543 AP PATRICK CUDAHY INCORPORATED AP PATRICK CUDAHY INCORPORATED AP SUGAR CREEK PACKING CO AP SUGAR CREEK PACKING CO AP VICTORYS KITCHEN AP VICTORYS KITCHEN AP BRIGHT CHEESE HOUSE AP BRIGHT CHEESE HOUSE AP CAPPOLA FOODS INC The final result should look like this: C PATRICK CUDAHY INCORPORATED SUGAR CREEK PACKING CO VICTORYS KITCHEN BRIGHT CHEESE HOUSE CAPPOLA FOODS INC Thanks SJ |
#4
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXTRACTING UNIQUE RECORD BASED ON CONDITION
Here's a way doing it with advanced filter, assume the data starts in A4
with a header, in for instance G2 put =AND(A5="AP",COUNTIF($B$5:B5,B5)=1) leave G1 blank, then apply the filter, select $G$1:$G$2 as the criteria range and select copy to another location -- Regards, Peo Sjoblom "SSJ" wrote in message ... Hello everyone! I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition. The data is as follows: A B MI 70056542 MI 70056543 AP PATRICK CUDAHY INCORPORATED AP PATRICK CUDAHY INCORPORATED AP SUGAR CREEK PACKING CO AP SUGAR CREEK PACKING CO AP VICTORYS KITCHEN AP VICTORYS KITCHEN AP BRIGHT CHEESE HOUSE AP BRIGHT CHEESE HOUSE AP CAPPOLA FOODS INC The final result should look like this: C PATRICK CUDAHY INCORPORATED SUGAR CREEK PACKING CO VICTORYS KITCHEN BRIGHT CHEESE HOUSE CAPPOLA FOODS INC Thanks SJ |
#5
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXTRACTING UNIQUE RECORD BASED ON CONDITION
Try this:
Assume the range of data is A2:B21 Enter this array** formula in C2 and copy down until you get #NUM! errors meaning all uniques have been extracted: =INDEX(B$2:B$21,SMALL(IF(A$2:A$21="AP",IF(B$2:B$21 <"",IF(MATCH(A$2:A$21&B$2:B$21,A$2:A$21&B$2:B$21, 0)=ROW(B$2:B$21)-ROW(B$2)+1,ROW(B$2:B$21)-ROW(B$2)+1))),ROWS($1:1))) ** array formulas *MUST* be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "SSJ" wrote in message ... Hello everyone! I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition. The data is as follows: A B MI 70056542 MI 70056543 AP PATRICK CUDAHY INCORPORATED AP PATRICK CUDAHY INCORPORATED AP SUGAR CREEK PACKING CO AP SUGAR CREEK PACKING CO AP VICTORYS KITCHEN AP VICTORYS KITCHEN AP BRIGHT CHEESE HOUSE AP BRIGHT CHEESE HOUSE AP CAPPOLA FOODS INC The final result should look like this: C PATRICK CUDAHY INCORPORATED SUGAR CREEK PACKING CO VICTORYS KITCHEN BRIGHT CHEESE HOUSE CAPPOLA FOODS INC Thanks SJ |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
EXTRACTING UNIQUE RECORD BASED ON CONDITION
Try this:
=IF(ISERR(SMALL(IF(FREQUENCY(IF($A$2:$A$12="AP",MA TCH($B$2:$B$12,$B$2:$B$12,0)),MATCH($B$2:$B$12,$B$ 2:$B$12,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$12)))), ROWS($1:1))),"",INDEX($B$2:$B$12,SMALL(IF(FREQUENC Y(IF($A$2:$A$12="AP",MATCH($B$2:$B$12,$B$2:$B$12,0 )),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW(INDIRECT("1 :"&ROWS($B$2:$B$12)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Drag the Fill Handle to copy as far as needed "SSJ" wrote: Hello everyone! I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition. The data is as follows: A B MI 70056542 MI 70056543 AP PATRICK CUDAHY INCORPORATED AP PATRICK CUDAHY INCORPORATED AP SUGAR CREEK PACKING CO AP SUGAR CREEK PACKING CO AP VICTORYS KITCHEN AP VICTORYS KITCHEN AP BRIGHT CHEESE HOUSE AP BRIGHT CHEESE HOUSE AP CAPPOLA FOODS INC The final result should look like this: C PATRICK CUDAHY INCORPORATED SUGAR CREEK PACKING CO VICTORYS KITCHEN BRIGHT CHEESE HOUSE CAPPOLA FOODS INC Thanks SJ |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
EXTRACTING UNIQUE RECORD BASED ON CONDITION
Yuck!
You'll be doing yourself (and others) a great benefit by dropping your use of INDIRECT so much. It isn't efficient at all and it's not necessary. With a formula like that it's better to not use an error trap and just use conditional formatting to hide them. Or, use a helper cell to get the count of uniques that meet the criteria and then refer to that cell like this: =IF(ROWS($1:1)<=A$1,INDEX(........................ ..),"") It's more efficient and makes the formula smaller. Using your formula as is, is nearly 7.5 times slower (on average) on a recalculation (when the workbook calculates) Biff "Teethless mama" wrote in message ... Try this: =IF(ISERR(SMALL(IF(FREQUENCY(IF($A$2:$A$12="AP",MA TCH($B$2:$B$12,$B$2:$B$12,0)),MATCH($B$2:$B$12,$B$ 2:$B$12,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$12)))), ROWS($1:1))),"",INDEX($B$2:$B$12,SMALL(IF(FREQUENC Y(IF($A$2:$A$12="AP",MATCH($B$2:$B$12,$B$2:$B$12,0 )),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW(INDIRECT("1 :"&ROWS($B$2:$B$12)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Drag the Fill Handle to copy as far as needed "SSJ" wrote: Hello everyone! I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition. The data is as follows: A B MI 70056542 MI 70056543 AP PATRICK CUDAHY INCORPORATED AP PATRICK CUDAHY INCORPORATED AP SUGAR CREEK PACKING CO AP SUGAR CREEK PACKING CO AP VICTORYS KITCHEN AP VICTORYS KITCHEN AP BRIGHT CHEESE HOUSE AP BRIGHT CHEESE HOUSE AP CAPPOLA FOODS INC The final result should look like this: C PATRICK CUDAHY INCORPORATED SUGAR CREEK PACKING CO VICTORYS KITCHEN BRIGHT CHEESE HOUSE CAPPOLA FOODS INC Thanks SJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a listbox to show every unique record in a range | Excel Worksheet Functions | |||
Counting unique entries based on given condition | Excel Discussion (Misc queries) | |||
Display unique record | Excel Discussion (Misc queries) | |||
extracting unique rows | New Users to Excel | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions |