Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with multiple results, without duplicates
My data looks like this:
Num1 Num2 56 5 100 7 46 7 46 7 88 7 100 7 75 8 What I need as a result, looking up the value 7 in Num2: 100 46 88 (separate cells in a row) I found the quoted material (see below) in another post, but that returns: 100 46 46 88 100 Is there anyway to eliminate the duplicates? Assumptions: A1:B5 contains your source data First row contains your headers/labels Formula: E2, copied across: =IF(COLUMNS($E1:E1)<=COUNTIF($A$2:$A$5,$D2),INDEX( $B$2:$B$5,SMALL(IF($A$2 :$A$5=$D2,ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($E1:E1))),"") ....where D2 contains the program name of interest. The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, if you need to, you can enter your list of program names in Column D, starting at D2, enter the formula in E2, copy across and down. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with multiple results, without duplicates
Rothman Wrote: My data looks like this: Num1 Num2 56 5 100 7 46 7 46 7 88 7 100 7 75 8 What I need as a result, looking up the value 7 in Num2: 100 46 88 (separate cells in a row) I found the quoted material (see below) in another post, but that returns: 100 46 46 88 100 Is there anyway to eliminate the duplicates? Assumptions: A1:B5 contains your source data First row contains your headers/labels Formula: E2, copied across: =IF(COLUMNS($E1:E1)<=COUNTIF($A$2:$A$5,$D2),INDEX( $B$2:$B$5,SMALL(IF($A$2 :$A$5=$D2,ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($E1:E1))),"") ....where D2 contains the program name of interest. The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, if you need to, you can enter your list of program names in Column D, starting at D2, enter the formula in E2, copy across and down. You also can try the formula below: Suppose in Column A you have your NUM1, column B - NUM2 In column C you enter formula: =IF(COUNTIF(B$1:B$7,B1)1,IF(COUNTIF(A1:A7,A1)=1,A 1,""),"") (not just ENTER but CTRL+SHIFT+ENTER) then Select so many cell as you need and press CTRL+D) -- Irina ------------------------------------------------------------------------ Irina's Profile: http://www.excelforum.com/member.php...fo&userid=8685 View this thread: http://www.excelforum.com/showthread...hreadid=521207 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with multiple results, without duplicates
Or using Advanced Filter
Num1 Num2 56 5 100 7 46 7 46 7 88 7 100 7 75 8 Criteria Test FALSE Num1 100 46 88 I assume you already applied the names Num1, Num2 Select Num1 and its data cells (8 cells) and give it the name Database. Select Test and False (2 cells) and give it the name Criteria. In the cell that says FALSE, enter this formula: =OFFSET(Num1,,1,1)=7 Data Filter Advanced Filter Click on Copy to another location Click on Unique records only Fill in Copy to List range and Criteria range have been filled automatically. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with multiple results, without duplicates
Assuming that A2:B8 contains your data, try the following...
In D2, enter: 7 In E2, enter: =COUNT(1/FREQUENCY(IF(B2:B8=D2,A2:A8),IF(B2:B8=D2,A2:A8))) ....confirmed with CONTROL+SHIFT+ENTER. In F2, leave empty In G2, enter and copy across: =IF(COLUMNS($G2:G2)<=$E2,INDEX($A$2:$A$8,MATCH(0,I F($B$2:$B$8=$D2,COUNTIF ($F2:F2,$A$2:$A$8)),0)),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Rothman wrote: My data looks like this: Num1 Num2 56 5 100 7 46 7 46 7 88 7 100 7 75 8 What I need as a result, looking up the value 7 in Num2: 100 46 88 (separate cells in a row) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup and show all results | Excel Worksheet Functions | |||
Multiple criteria LOOKUP | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Multiple lookup value's | Excel Worksheet Functions | |||
Multiple Criteria Lookup Question | Excel Discussion (Misc queries) |