Find the row number of name occurence
 

Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil

Find the row number of name occurence
 

With values in AI, as you described, I tried this:
=MATCH(AI1,$AI$1:$AI$18,0)

Seemed to work.
What do you think?

Regards,
Ryan--

--
RyGuy


" wrote:

Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil

Find the row number of name occurence
 

Wait a second, you may have to changes those references a bit. Try this in
the next column to the right:
=MATCH($AI$1,AJ1:AJ18,0)

--
RyGuy


" wrote:

Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil

Find the row number of name occurence
 

You can produce a unique reference for each record by putting this
formula in a helper column somewhe

=IF(AI1="","-",AI1&"_"&COUNTIF(AI$1:AI1,AI1))

If you copy this down you will get:

Scott Scott_1
Billy Billy_1
Leigh Leigh_1
Seb Seb_1
Billy Billy_2
Scott Scott_2

i.e. a sequential number after each name. Use this as your reference
for the other part of your sheet, with a formula like the following in
cell AM1:

=IF(ISNA(MATCH($AL1&"_"&COLUMN(A1),$AI$1:$AI
$60,0)),"",MATCH($AL1&"_"&COLUMN(A1),$AI$1:$AI$60, 0))

Copy this across and down as required.

Hope this helps.

Pete



On Oct 2, 1:08*am, "
wrote:
Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil

Find the row number of name occurence
 

Hi,

I think this is what you want:

=LARGE(($AI$1:$AI$7=$AL1)*(ROW($AI$1:$AI$7)),COLUM N(A1))

It needs to be array entered, and you will need to increase the AI row. The
first formula goes into AM1 and to array enter it you press Shift+Ctrl+Enter
not Enter. You can copy it down and to the right as far as necessary.

It will return the last row the name is found on first and then the
penultimate and so on. If it does not find an entry it will return 0. You
can hide 0's by choosing Tools, Option, View and unchecking Zero values or
you can enhance the formula to read:

=IF(LARGE(($AI$1:$AI$7=$AL1)*(ROW($AI$1:$AI$7)),CO LUMN(A1))=0,"",LARGE(($AI$1:$AI$7=$AL1)*(ROW($AI$1 :$AI$7)),COLUMN(A1)))

--
Thanks,
Shane Devenshire


" wrote:

Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil

Find the row number of name occurence
 

Another play to tinker with ..
Data assumed in AI2 down
List the unique names across in AL1:AO1, eg: Scott, Billy, Leigh, Seb
Put in AL2: =IF($AI2=AL$1,ROW(),"")
Copy across to AO2, fill down to cover the max expected extent of data in
col AI
Then
In AQ2: =INDEX($AL$1:$AO$1,ROWS($1:1))
In AR2: =SMALL(OFFSET($AL:$AL,,ROWS($1:1)-1),COLUMNS($A:A))
Copy AR2 across by 7 cols to AX2 (that's the max you mentioned). Select
AQ2:AX2, fill down to AQ5 to return the desired results. You'd get #NUM!
errors within AR2:AX5 once the name occurences are exhausted.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400, Files:361, Subscribers:58
xdemechanik
---
" wrote in message
...
Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil

Find the row number of name occurence
 

On 2 Oct, 05:57, ShaneDevenshire
wrote:
Hi,

I think this is what you want:

=LARGE(($AI$1:$AI$7=$AL1)*(ROW($AI$1:$AI$7)),COLUM N(A1))

It needs to be array entered, and you will need to increase the AI row. *The
first formula goes into AM1 and to array enter it you press Shift+Ctrl+Enter
not Enter. *You can copy it down and to the right as far as necessary.

It will return the last row the name is found on first and then the
penultimate and so on. *If it does not find an entry it will return 0. *You
can hide 0's by choosing Tools, Option, View and unchecking Zero values or
you can enhance the formula to read:

=IF(LARGE(($AI$1:$AI$7=$AL1)*(ROW($AI$1:$AI$7)),CO LUMN(A1))=0,"",LARGE(($AI*$1:$AI$7=$AL1)*(ROW($AI$ 1:$AI$7)),COLUMN(A1)))

--
Thanks,
Shane Devenshire



" wrote:
Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil- Hide quoted text -

- Show quoted text -

Shane

This is 'almost' what I want, except it seems to work it all out in
reverse!
IE for 'Scott' there are 4 occurences out os a possible 7 in the 60
rows, and his results come out 49, 37, 21, 1, 0, 0, 0, whereas I would
want 1, 21, 37, 49, 0, 0, 0

I don't understand what you mean by 'you will need to increase the AI
row' unless you mean changing it to $AI$1:$AI$60 to suit my data?

Thanks
Neil

Find the row number of name occurence
 

"Name" is a defined name range (no quotes) in column AI

In AM:
=IF(ISERR(SMALL(IF(Name=$AL1,ROW(INDIRECT("1:"&ROW S(Name)))),COLUMNS($A:A))),"",SMALL(IF(Name=$AL1,R OW(INDIRECT("1:"&ROWS(Name)))),COLUMNS($A:A)))

ctrl+shift+enter, not just enter
copy across and down as far as needed


" wrote:

Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil

Find the row number of name occurence
 

Use Excel 2007 PivotTable.
Simple formulas.
Automatically expands if reps 7.
Automatically updates when list expands.
Table can be sorted/filtered various ways.
Various built-in formats . See:
http://www.savefile.com/files/1817635

Find the row number of name occurence
 

On 2 Oct, 14:41, Teethless mama
wrote:
"Name" is a defined name range (no quotes) in column AI

In AM:
=IF(ISERR(SMALL(IF(Name=$AL1,ROW(INDIRECT("1:"&ROW S(Name)))),COLUMNS($A:A))*),"",SMALL(IF(Name=$AL1, ROW(INDIRECT("1:"&ROWS(Name)))),COLUMNS($A:A)))

ctrl+shift+enter, not just enter
copy across and down as far as needed



" wrote:
Hi

I have a list of names in Column AI that can recur up to 7 times per
name (each name)
EG
Scott
Billy
Leigh
Seb
Billy
Scott

In Column AL I list these people once
EG
Scott
Billy
Leigh
Seb
etc
etc
etc

In Columns AM to AS I wish to return the row numbers that these names
occur in (each name will occur a maximim of 7 times, but I don't mind
the formula returning #N/A if they don't occur 7 times) and the data
will go from row 1 through to 60
So, Scott is in AL1 and the value 1 would be in AM1 and 6 in AN1 etc
etc
Billy in AL2 with value 2 in AM2, and 5 in AN2 etc etc

How would I achieve this?
I can use MATCH in colum AM to get first occurences, but this doesn't
work on subsequent columns.

Any help with this would be most appreciated. (I hope I explained this
clearly)
Thanks
Neil- Hide quoted text -

- Show quoted text -

Thanks to everyone for their repsonses. I now have just what I need to
proceed.
Pivot table wasn't right for what I need to do

Neil