find a data in a list that have a few hit
 

how to list out a data on a new column that have more that 3 hit. i.e
A B C
D
002-1931 C RJS,GRIP,RIGHT F RONCELLI PLASTICS INC
002-1955 G RJS,BASE,HANDLE F FRONTIER TECHNOLOGIES
002-1956 J RJS,OPTICAL HEAD F LAMB ENGINEERING INC
002-1958 B RJS,TEST SYMBOL F SYMBOLOGY INC
002-1958 B RJS,TEST SYMBOL F FIRESTREAM WORLDWIDE
002-1960 E RJS,TUBE,FAN F BARROT CORPORATION
002-1960 E RJS,TUBE,FAN F FRONTIER TECHNOLOGIES
002-2047 K RJS,LOWER BAS F RONCELLI PLASTICS INC

I WANT TO FIND ALL "002-1958" AND LIST UP COLUMN D IN COLUMN F. VLOOPUP ONLY
LIST 1 HIT. ANY HELP IS GREATLY APPRECIATED.

thks and regards. ssa
--
it''s not the eye that is blind, blind is the eye within the bossom

find a data in a list that have a few hit
 

Try the below; which will return the data in ColD for a mathching entry
'002-1958' in Col A. Apply this formula and copy down as required

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=IF(COUNTIF($A$1:$A$1000,"002-1958")<ROW(A1),"",INDEX(D$1:D$1000,
SMALL(IF($A$1:$A$1000="002-1958",ROW($A$1:$A$1000)),ROW(A1))))

If this post helps click Yes
---------------
Jacob Skaria


"ssa" wrote:

how to list out a data on a new column that have more that 3 hit. i.e
A B C
D
002-1931 C RJS,GRIP,RIGHT F RONCELLI PLASTICS INC
002-1955 G RJS,BASE,HANDLE F FRONTIER TECHNOLOGIES
002-1956 J RJS,OPTICAL HEAD F LAMB ENGINEERING INC
002-1958 B RJS,TEST SYMBOL F SYMBOLOGY INC
002-1958 B RJS,TEST SYMBOL F FIRESTREAM WORLDWIDE
002-1960 E RJS,TUBE,FAN F BARROT CORPORATION
002-1960 E RJS,TUBE,FAN F FRONTIER TECHNOLOGIES
002-2047 K RJS,LOWER BAS F RONCELLI PLASTICS INC

I WANT TO FIND ALL "002-1958" AND LIST UP COLUMN D IN COLUMN F. VLOOPUP ONLY
LIST 1 HIT. ANY HELP IS GREATLY APPRECIATED.

thks and regards. ssa
--
it''s not the eye that is blind, blind is the eye within the bossom