Ignore duplicates

  • Thread starter Thread starter Gotroots
  • Start date Start date
G

Gotroots

The following formula is in “K†and will return the value in “A†when the
conditions are met.

=INDEX(INDIRECT("$A$2:$A$5000"),MATCH(SMALL($H$2:$H$5000,ROWS($IV$1:IV3)),$H$2:$H$5000,0))

Because “A†contains duplicate values these are returned to “Kâ€
I want “K†to only return the same value once

What is the best way to achieve this?
 
=IF(COUNTIF($A$2:$A3,A3)=1,INDEX(INDIRECT("$A$2:$A$5000"),MATCH(SMALL($H$2:$H$5000,ROWS($IV$1:IV3)),$H$2:$H$5000,0)),"")
 
Hi Bernie Deitrick

Your solution returned intermittent results, Records that should been
returned did not return.
 
Post a 4 row example of your data - say, rows two to five - including one
set of duplicates, and indicate what you are currently getting with your
formula and what you expect the formula to return.

Bernie
 
Here is a breakdown of the results I am getting

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 above formula fill down

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 =
 
Back
Top