Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can CELL Return Multiple Data Locations?
I have a list of data containing names (30,000+) that I need to look through.
Can Excel tell me which rows a name appears using a formula using something like CELL(not using find or filter)? I need to leave them in their mixed-up order. My boss wants the exact row numbers of each name and looking up 2,500 names manually will take a really long time. If Excel can do this, how do I do it? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can CELL Return Multiple Data Locations?
=MATCH(A:A;B1)
sill return the first row number in Column A where the contents of cell B1 have been found HTH -- AP "Val" a écrit dans le message de news: ... I have a list of data containing names (30,000+) that I need to look through. Can Excel tell me which rows a name appears using a formula using something like CELL(not using find or filter)? I need to leave them in their mixed-up order. My boss wants the exact row numbers of each name and looking up 2,500 names manually will take a really long time. If Excel can do this, how do I do it? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can CELL Return Multiple Data Locations?
If you want all of the rows a particular name appears in and assuming your
source data of names is in Sheet1!A1:A7, Sheet2 cell A1 contains the name for which you are searching, you could enter this formula in cell B1 (of Sheet2) and copy across until the formula returns blanks. The formula is an array formula, you must confirm it with Control+Shift+Enter. Modify the ranges and cell references as needed. IF(SUM(--(Sheet1!$A$1:$A$7=$A1))<COLUMN()-COLUMN($B1)+1,"",SMALL(IF(Sheet1!$A$1:$A$7=$A1,ROW (INDIRECT(ROW(Sheet1!$A$1)&":"&ROWS(Sheet1!$A$1:$A $7))),""),COLUMN()-COLUMN($B1)+1)) "Val" wrote: I have a list of data containing names (30,000+) that I need to look through. Can Excel tell me which rows a name appears using a formula using something like CELL(not using find or filter)? I need to leave them in their mixed-up order. My boss wants the exact row numbers of each name and looking up 2,500 names manually will take a really long time. If Excel can do this, how do I do it? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can CELL Return Multiple Data Locations?
Or, you could use a UDF. Using the previous assumptions you would enter it
in B1 =findrows(Sheet1!$A$1:$A$7,A1) Function FindRows(rngData As Range, strCriteria As String) As String Dim rngCell As Range For Each rngCell In rngData.Cells If rngCell.Value = strCriteria Then _ FindRows = FindRows & " " & CStr(rngCell.Row) Next rngCell If FindRows < "" Then _ FindRows = Replace(LTrim(FindRows), _ " ", ", ", 1, -1, vbTextCompare) End Function "JMB" wrote: If you want all of the rows a particular name appears in and assuming your source data of names is in Sheet1!A1:A7, Sheet2 cell A1 contains the name for which you are searching, you could enter this formula in cell B1 (of Sheet2) and copy across until the formula returns blanks. The formula is an array formula, you must confirm it with Control+Shift+Enter. Modify the ranges and cell references as needed. IF(SUM(--(Sheet1!$A$1:$A$7=$A1))<COLUMN()-COLUMN($B1)+1,"",SMALL(IF(Sheet1!$A$1:$A$7=$A1,ROW (INDIRECT(ROW(Sheet1!$A$1)&":"&ROWS(Sheet1!$A$1:$A $7))),""),COLUMN()-COLUMN($B1)+1)) "Val" wrote: I have a list of data containing names (30,000+) that I need to look through. Can Excel tell me which rows a name appears using a formula using something like CELL(not using find or filter)? I need to leave them in their mixed-up order. My boss wants the exact row numbers of each name and looking up 2,500 names manually will take a really long time. If Excel can do this, how do I do it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting cell data from numerous files in multiple folders???? | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Compare data - one cell to multiple cells | Excel Discussion (Misc queries) | |||
Return data from multiple cells | Excel Worksheet Functions |