Results 1 to 4 of 4

Thread: Sequential number problem

  1. #1

    Sequential number problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,
    My first post here, I hope I explain everything well.
    I have a large spread sheet, 1500 rows and 18 columns(DataSheet). On a seperate work sheet(View) in B1 and C1 I have two values, I want to extract the rows of data where B1(View) = column B(DataSheet) and C1(View) = Column G(DataSheet).
    So I have gone to AA and put in a simple If function with an AND, and copied this down, where I get a match it copies the row of data and if not it remains blank.
    I would now like to go back to the View sheet and take the relevent data there through a vlookup. To do this I would need to enter a number in column Z next to a row that only contains data in AA, if(AA1<>"",NUMBER,"").
    The problem is what to enter to get NUMBER. I want the number to be sequential, the first row with data to be 1, the second row to be 2 etc. Of course the first row might be row 172 and the second one row 844.
    This is probably a scrappy way of doing it, but with my limited knolwedge it's the best I can come up with.

    Thank you in advance to everyone who views this, it's probably hard to get your head around and I've explained it as best I can.

    Wally

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I would add a helper column in the DataSheet first.

    In a new column, say column H, in H2 enter formula like:

    =IF(AND(B2=View!$B$1,G1=View!$C$1),COUNT(H$1:H1)+1,"")

    and copy it down. This will identify and number the matches.

    Then in the View sheet, in A2 enter formula:

    =IFERROR(INDEX(DataSheet!A:A,MATCH(ROWS($A$2:$A2),DataSheet!$H:$H,0)),"")


    copied down and across to get all the info. Where DataSheet!$H:$H is the column you placed the numbering formula in.


  3. #3
    NBVC,
    Thank you so much the count part of the function is exactly what I was looking for.
    I have never used IFERROR and INDEX, so some extra info for me as well.
    May all the good karma stay with you and again THANK YOU.
    Wally.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You are welcome. Glad it works for you.

    I am sure you noticed, but I had a small typo in the COUNT formula.... I referenced G1 when I should have referenced G2...

    =IF(AND(B2=View!$B$1,G2=View!$C$1),COUNT(H$1:H1)+1,"")


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •