| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stripe out duplicate data
The following data gives an explanation of what I want the formula in D to
return. A3:A14 are entered values A3 = apples A4 = apples A5 = pears A6 = oranges A7 = apples A8 = grapefruit A9 = grapes A10 = lemons A11 = bananas A12 = bananas A13 = kiwi A14 = plums B3:B14 are the result of a formula B3 = no record B4 = no record B5 = B6 = B7 = no record B8 = B9 = no record B10 = no record B11 = no record B12 = no record B13 = B14 = no record C3:C14 are the result of a formula C3 = 3 C4 = 4 C5 = C6 = C7 = 7 C8 = C9 = 9 C10 = 10 C11 = 11 C12 = 12 C13 = C14 = 14 D3 contains the formula; =IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"") D3:D14 contains the result of the above formula relative to each cell D3 = D4 = apples D5 = apples D6 = D7 = lemons D8 = bananas D9 = bananas D10 = D11 = D12 = #NUM! D13 = #NUM! D14 = What the formula should have returned is as follows: D3 = apples D4 = grapes D5 = lemons D6 = bananas D7 = plums D8 = D9 = D10 = D11 = D12 = D13 = D14 = Hope someone can tell me what is wrong with the formula in D Thank you |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripe out duplicate data
How about
=IF(ISERROR(SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))),"",INDEX($A$3:$ A$14,SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))-MIN(ROW($B$3:$B$14))+1)) --- HTH Bob Phillips "Gotroots" wrote in message ... The following data gives an explanation of what I want the formula in "D" to return. A3:A14 are entered values A3 = apples A4 = apples A5 = pears A6 = oranges A7 = apples A8 = grapefruit A9 = grapes A10 = lemons A11 = bananas A12 = bananas A13 = kiwi A14 = plums B3:B14 are the result of a formula B3 = no record B4 = no record B5 = B6 = B7 = no record B8 = B9 = no record B10 = no record B11 = no record B12 = no record B13 = B14 = no record C3:C14 are the result of a formula C3 = 3 C4 = 4 C5 = C6 = C7 = 7 C8 = C9 = 9 C10 = 10 C11 = 11 C12 = 12 C13 = C14 = 14 D3 contains the formula; =IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"") D3:D14 contains the result of the above formula relative to each cell D3 = D4 = apples D5 = apples D6 = D7 = lemons D8 = bananas D9 = bananas D10 = D11 = D12 = #NUM! D13 = #NUM! D14 = What the formula should have returned is as follows: D3 = apples D4 = grapes D5 = lemons D6 = bananas D7 = plums D8 = D9 = D10 = D11 = D12 = D13 = D14 = Hope someone can tell me what is wrong with the formula in "D" Thank you |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripe out duplicate data
I am afraid no results were returned. I am just thinking colB is confusing
matters here and should not be included in the formula. There was no reference made to colC in your formula "C" is a key driver to what records should be returned. So please ignore "B" and concentrate on "A" and "C" instead. "Bob Phillips" wrote: How about =IF(ISERROR(SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))),"",INDEX($A$3:$ A$14,SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))-MIN(ROW($B$3:$B$14))+1)) --- HTH Bob Phillips "Gotroots" wrote in message ... The following data gives an explanation of what I want the formula in "D" to return. A3:A14 are entered values A3 = apples A4 = apples A5 = pears A6 = oranges A7 = apples A8 = grapefruit A9 = grapes A10 = lemons A11 = bananas A12 = bananas A13 = kiwi A14 = plums B3:B14 are the result of a formula B3 = no record B4 = no record B5 = B6 = B7 = no record B8 = B9 = no record B10 = no record B11 = no record B12 = no record B13 = B14 = no record C3:C14 are the result of a formula C3 = 3 C4 = 4 C5 = C6 = C7 = 7 C8 = C9 = 9 C10 = 10 C11 = 11 C12 = 12 C13 = C14 = 14 D3 contains the formula; =IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"") D3:D14 contains the result of the above formula relative to each cell D3 = D4 = apples D5 = apples D6 = D7 = lemons D8 = bananas D9 = bananas D10 = D11 = D12 = #NUM! D13 = #NUM! D14 = What the formula should have returned is as follows: D3 = apples D4 = grapes D5 = lemons D6 = bananas D7 = plums D8 = D9 = D10 = D11 = D12 = D13 = D14 = Hope someone can tell me what is wrong with the formula in "D" Thank you . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripe out duplicate data
It is an array formula, as your original was, so you need to CSE it
--- HTH Bob Phillips "Gotroots" wrote in message ... I am afraid no results were returned. I am just thinking colB is confusing matters here and should not be included in the formula. There was no reference made to colC in your formula "C" is a key driver to what records should be returned. So please ignore "B" and concentrate on "A" and "C" instead. "Bob Phillips" wrote: How about =IF(ISERROR(SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))),"",INDEX($A$3:$ A$14,SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))-MIN(ROW($B$3:$B$14))+1)) --- HTH Bob Phillips "Gotroots" wrote in message ... The following data gives an explanation of what I want the formula in "D" to return. A3:A14 are entered values A3 = apples A4 = apples A5 = pears A6 = oranges A7 = apples A8 = grapefruit A9 = grapes A10 = lemons A11 = bananas A12 = bananas A13 = kiwi A14 = plums B3:B14 are the result of a formula B3 = no record B4 = no record B5 = B6 = B7 = no record B8 = B9 = no record B10 = no record B11 = no record B12 = no record B13 = B14 = no record C3:C14 are the result of a formula C3 = 3 C4 = 4 C5 = C6 = C7 = 7 C8 = C9 = 9 C10 = 10 C11 = 11 C12 = 12 C13 = C14 = 14 D3 contains the formula; =IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"") D3:D14 contains the result of the above formula relative to each cell D3 = D4 = apples D5 = apples D6 = D7 = lemons D8 = bananas D9 = bananas D10 = D11 = D12 = #NUM! D13 = #NUM! D14 = What the formula should have returned is as follows: D3 = apples D4 = grapes D5 = lemons D6 = bananas D7 = plums D8 = D9 = D10 = D11 = D12 = D13 = D14 = Hope someone can tell me what is wrong with the formula in "D" Thank you . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripe out duplicate data
Even when array entered no results are coming back.
"Bob Phillips" wrote: It is an array formula, as your original was, so you need to CSE it --- HTH Bob Phillips "Gotroots" wrote in message ... I am afraid no results were returned. I am just thinking colB is confusing matters here and should not be included in the formula. There was no reference made to colC in your formula "C" is a key driver to what records should be returned. So please ignore "B" and concentrate on "A" and "C" instead. "Bob Phillips" wrote: How about =IF(ISERROR(SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))),"",INDEX($A$3:$ A$14,SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))-MIN(ROW($B$3:$B$14))+1)) --- HTH Bob Phillips "Gotroots" wrote in message ... The following data gives an explanation of what I want the formula in "D" to return. A3:A14 are entered values A3 = apples A4 = apples A5 = pears A6 = oranges A7 = apples A8 = grapefruit A9 = grapes A10 = lemons A11 = bananas A12 = bananas A13 = kiwi A14 = plums B3:B14 are the result of a formula B3 = no record B4 = no record B5 = B6 = B7 = no record B8 = B9 = no record B10 = no record B11 = no record B12 = no record B13 = B14 = no record C3:C14 are the result of a formula C3 = 3 C4 = 4 C5 = C6 = C7 = 7 C8 = C9 = 9 C10 = 10 C11 = 11 C12 = 12 C13 = C14 = 14 D3 contains the formula; =IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"") D3:D14 contains the result of the above formula relative to each cell D3 = D4 = apples D5 = apples D6 = D7 = lemons D8 = bananas D9 = bananas D10 = D11 = D12 = #NUM! D13 = #NUM! D14 = What the formula should have returned is as follows: D3 = apples D4 = grapes D5 = lemons D6 = bananas D7 = plums D8 = D9 = D10 = D11 = D12 = D13 = D14 = Hope someone can tell me what is wrong with the formula in "D" Thank you . . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripe out duplicate data
Well, it worked for me in my tests. Not much more I can do without the data.
--- HTH Bob Phillips "Gotroots" wrote in message ... Even when array entered no results are coming back. "Bob Phillips" wrote: It is an array formula, as your original was, so you need to CSE it --- HTH Bob Phillips "Gotroots" wrote in message ... I am afraid no results were returned. I am just thinking colB is confusing matters here and should not be included in the formula. There was no reference made to colC in your formula "C" is a key driver to what records should be returned. So please ignore "B" and concentrate on "A" and "C" instead. "Bob Phillips" wrote: How about =IF(ISERROR(SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))),"",INDEX($A$3:$ A$14,SMALL(IF($B$3:$B$14="no record",ROW($B$3:$B$14)),ROW(B1))-MIN(ROW($B$3:$B$14))+1)) --- HTH Bob Phillips "Gotroots" wrote in message ... The following data gives an explanation of what I want the formula in "D" to return. A3:A14 are entered values A3 = apples A4 = apples A5 = pears A6 = oranges A7 = apples A8 = grapefruit A9 = grapes A10 = lemons A11 = bananas A12 = bananas A13 = kiwi A14 = plums B3:B14 are the result of a formula B3 = no record B4 = no record B5 = B6 = B7 = no record B8 = B9 = no record B10 = no record B11 = no record B12 = no record B13 = B14 = no record C3:C14 are the result of a formula C3 = 3 C4 = 4 C5 = C6 = C7 = 7 C8 = C9 = 9 C10 = 10 C11 = 11 C12 = 12 C13 = C14 = 14 D3 contains the formula; =IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"") D3:D14 contains the result of the above formula relative to each cell D3 = D4 = apples D5 = apples D6 = D7 = lemons D8 = bananas D9 = bananas D10 = D11 = D12 = #NUM! D13 = #NUM! D14 = What the formula should have returned is as follows: D3 = apples D4 = grapes D5 = lemons D6 = bananas D7 = plums D8 = D9 = D10 = D11 = D12 = D13 = D14 = Hope someone can tell me what is wrong with the formula in "D" Thank you . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What happened to the stripe fills for data series in Excel 2007? | Charts and Charting in Excel | |||
| identify duplicate data upon entry of that data | Excel Discussion (Misc queries) | |||
| Import data and keep duplicate rows of data | Excel Discussion (Misc queries) | |||
| comparing lists of data to remove duplicate data | Excel Discussion (Misc queries) | |||
| how can i locate duplicate data in an excel data table? | Excel Worksheet Functions | |||
Hybrid Mode
