| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Last Occurence in a Range with VBA | Excel Discussion (Misc queries) | |||
| Find first occurence of a number in an array 7 cols wide | Excel Worksheet Functions | |||
| Finding first occurence of a number | Excel Worksheet Functions | |||
| Trying to FIND lowercase or uppercase of target occurence | Excel Worksheet Functions | |||
| Find first occurence in a list that's greater than a specific num | Excel Worksheet Functions | |||
Hybrid Mode
