Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I find data in one list and add it to another sheet? | Excel Worksheet Functions | |||
How do I find data excluded from a list in excel? | Excel Discussion (Misc queries) | |||
Excel 2002: How to find only the required data in a list ? | Excel Discussion (Misc queries) | |||
formula to find the data of a next cell in a list | Excel Discussion (Misc queries) | |||
How do I find data from a list (or table) and insert it in a row? | Excel Discussion (Misc queries) |