1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

OT Excel help

Discussion in 'CycleChat Cafe' started by lifeson, 20 Jul 2007.

  1. lifeson

    lifeson New Member

    Does anybody know what formula to use in excel to return the row number of a row that contains specific data in column A

    eg
    Column "A" contains unique IDs (Pay numbers)

    I want a formula that tellls me which row contains pay no 1234:confused:
     
  2. And

    And Too gobby by half

    'Ctrl'+F will find the cell - is that any use?
     
  3. radger

    radger Über Member

    Location:
    Bristol
    i would use the search function rather than writing a formula

    this may be no use to you
     
  4. bonj2

    bonj2 Guest

    You want VLOOKUP.
    It can only return the corresponding value in another column, not the actual number of the row. But you can just create another column with just the number of the row.
    Be aware though that your pay numbers will havew to be in alphabetical order for it to work.
     
  5. simon_adams_uk

    simon_adams_uk Über Member

    Location:
    SW London
    Add a new column to the right of the column which contains the unique pay number IDs. This new column should be numbered from 1 to n.

    For this example they're columns A (Pay number IDs) and B (row number) and you've got 100 records (ie 100 rows).

    If you enter a formula along the lines of
    =VLOOKUP(1234,A1:B100,2,FALSE)
    will give you the row number for ID 1234

    Instead of typing '1234' you can softcode this to another cell value.

    I disagree with Bonj that the pay number IDs have to be in order - as long as they are unique it shouldn't matter.

    HTH,
    S
     
  6. bonj2

    bonj2 Guest

    hmmm... maybe they don't. I vaguely remember reading it though, maybe it was something else.
     
  7. lifeson

    lifeson New Member

    I did it this way in the end:

    in VBA

    Dim iRow As Long
    iRow = Application.Match(Val(txtPayNo.Text), .Columns(1), 0)

    Then used the row ID and the column reference to find the cell I wanted

    e.g.

    With Worksheets("Data")
    .Range("K" & iRow).Value = Me.txtAddress1.Text
    End With

    Thats took me all day :tongue:
     
  8. Keith Oates

    Keith Oates Janner

    Location:
    Penarth, Wales
    Yes, but you will remember it now. I always feel good when I've finally cracked something like that!!!!!!!!!!!!!!
     
  9. bonj2

    bonj2 Guest

    if you need any more simple things like that doing feel free to pm me, you can email me your workbook it would almost certainly take me far less than all day.
     
  10. lifeson

    lifeson New Member

    You may live to regret that offer :tongue: :?: