Stripe out duplicate data

G

Gotroots

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)),"")

D314 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

B

Bob Phillips

=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))

G

Gotroots

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.

B

Bob Phillips

It is an array formula, as your original was, so you need to CSE it

G

B

Bob Phillips

Well, it worked for me in my tests. Not much more I can do without the data.