Solved

excel fow row ID

Posted on 2013-12-16
20
272 Views
Last Modified: 2014-01-29
I have the following function to get a max number if a row = 1 .. How could i use this to get the row id????

=LARGE(IF(Result!N2:N1001=1,Result!O2:O1001,""),2)
0
Comment
Question by:runnerjp2005
  • 7
  • 6
  • 5
  • +1
20 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 39722464
Your formula is getting the second largest number. What do you mean "row id" - do you want the row number where that value occurs? If so try this formula

=MATCH(1,(Result!O1:O1001=LARGE(IF(Result!N1:N1001=1,Result!O1:O1001,""),2))*(Result!N1:N1001=1),0)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39722465
Do you mean the row number?

Try:

=LARGE(IF(Result!N2:N1001=1,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),2)

confirmed with CTRL+SHIFT+ENTER
0
 

Author Comment

by:runnerjp2005
ID: 39722582
i have tried =LARGE(IF(Result!N2:N1001=A2,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),1) and it seems to cound down 10/9/8/7/6 if i try doing the top 9 like this

=LARGE(IF(Result!N2:N1001=A2,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),1)
=LARGE(IF(Result!N2:N1001=A2,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),2)
=LARGE(IF(Result!N2:N1001=A2,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),3)
=LARGE(IF(Result!N2:N1001=A2,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),4)
=LARGE(IF(Result!N2:N1001=A2,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),5)


note that A2 is the race number so 1
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 23

Expert Comment

by:NBVC
ID: 39722619
With the LARGE() function you should get the largest row number first... if you want to retrieve from smallest row number you need SMALL()

=SMALL(IF(Result!N2:N1001=A2,ROW(Result!N2:N1001)-ROW(Result!N2)+1,""),2)

etc...
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39722624
Also, if you enter the formula with Absolute referencing and replace the K factor with ROWS(), you can enter the formula once and copy down to get next smallest/largest row number



=SMALL(IF(Result!$N$2:$N$1001=$A$2,ROW(Result!$N$2:$N$1001)-ROW(Result!$N$2)+1,""),ROWS($A$1:$A1))
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39722653
Can you confirm what you want to do? I thought you were finding the largest (or second largest) number in column O where column N = 1...and that you wanted to find the row where that value resides. That's what my suggested formula does.

NB_VC is finding the the nth row where column N = 1, is that what you need - isn't column O relevant?

regards, barry
0
 

Author Comment

by:runnerjp2005
ID: 39722664
hi ok please find attached my spreadhseet.

Column E on sheet Stats is basicly putting the top 5 from sheet "Result" where its (j+l)/2 ..... i want to get the row its from so i can get horses name jockey ect...
J.P.B.S.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39722679
That's more along the lines I suggested - you can get the relevant horse name in row 2 with this formula

=INDEX(Result!D$2:D$1000,MATCH(1,INDEX((Result!O$2:O$1000=E2)*(Result!N$2:N$1000=1),0),0))

change the first range to whatever column you want for other info like jockey

regards, barry
0
 

Author Comment

by:runnerjp2005
ID: 39722696
do i just chnage e2 to e3...e4 ect as it does not seem to select the correct horse...

Its attached
J.P.B.S.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39722702
I don't see how that works - E2 = 150 and the only horse with a total of 150 is Kilgefin Star (IRE)? why is it Racing Pulse?

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39722748
Looking again I realise that the formula needs to change to take account of duplicates, so that goes back to a combination of my suggestion and NB_VC's, i.e this formula in row 2

=IF(E2="","",INDEX(Result!D$2:D$500,SMALL(IF(Result!O$2:O$500=E2,IF(Result!N$2:N$500=A2,ROW(Result!D$2:D$500)-ROW(Result!D$2)+1)),COUNTIFS(A$2:A2,A2,E$2:E2,E2))))

confirmed with CTRL+SHIFT+ENTER and copied down the column

That still gives the same horses, though......

I populated the numbers in column A for every row, otherwise it will be more complex (and I also had to remove a #VALUE! error from column O in Results) - see attached

regards, barry
Racing.xlsx
0
 

Author Comment

by:runnerjp2005
ID: 39723450
Ah yes sorry my mistake... what would i need to chnage in  
=IF(E2="","",INDEX(Result!D$2:D$500,SMALL(IF(Result!O$2:O$500=E2,IF(Result!N$2:N$500=A2,ROW(Result!D$2:D$500)-ROW(Result!D$2)+1)),COUNTIFS(A$2:A2,A2,E$2:E2,E2))))

Open in new window

if i wanted to chnage my selection for form(B) or Odds (K)??
0
 

Author Comment

by:runnerjp2005
ID: 39723564
I have seemed to find an error where when i run the tidy up macro the results on the sts page say #ref and dne update :S

I have attached the spreadsheet with the new information in and the old information currently on the stats page.

What you do is on the INFO sheet on the top right you will see a button that says "Run TidyUp". Click on that and it should provide the top 5 horses ect on the stats page.... (It updates the Result page and lets you stats page pull the information)

Upon doing this the Results page is updated but the Stats page is full of #REF
J.P.B.S.xlsm
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39723773
Once you delete the "Result" sheet in your macro, the Info sheet formulas no longer find the Result sheet, and automatically changes the sheet names to the REF! error.  Adding the Result sheet back wont fix it.

The things you can do....

1.  Don't delete the Result sheet.  Instead clear everything, then instead of "adding" a Result sheet in the macro, just use the existing one.

2.  Convert all the formula in the Result sheet to include the INDIRECT() function.  This is less recommended as the INDIRECT formula adds volatility to the workbook, i.e. force repeated recalculations....

for example the formula in B2 would be:

=IF(E2="","",INDEX(INDIRECT("Result!D$2:D$500"),SMALL(IF(INDIRECT("Result!O$2:O$500")=E2,IF(INDIRECT("Result!N$2:N$500")=A2,ROW(INDIRECT("Result!D$2:D$500"))-ROW(INDIRECT("Result!D$2"))+1)),COUNTIFS(A$2:A2,A2,E$2:E2,E2))))

so everywhere you reference the Result sheet, you will need to wrap an INDIRECT() function around it.  This will not delete the sheetname from the formula if you delete the RESULT sheet.
0
 

Author Comment

by:runnerjp2005
ID: 39724039
would i do it like this: =MAX(IF(INDIRECT(Result!$N$2:$N$1000=A68,Result!$O$2:$O$1000,"")))
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39724097
Like this:

=MAX(IF(INDIRECT("Result!$N$2:$N$1000")=A68,INDIRECT("Result!$O$2:$O$1000"),""))
0
 

Author Comment

by:runnerjp2005
ID: 39726087
Hey,

I tried it and did the results for today but still get a #Value! error....

Not sure why as formulars have stayed the same this time

Please see attached
J.P.B.S2.xlsm
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39726598
You have a #VALUE! error in Results!O360 which causes that error in all your formulas (as I said in my last post). You need to remove that error from that cell then your formulas should work OK

regards, barry
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39817227
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 76
Getting error in connectionstring with Excel. 30 31
populate Excel dropdown via ADO and VBA 6 17
Merging spreadsheets 8 36
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question