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?

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?

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?

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?