Sequential number problem

Wally

New member
Joined
May 7, 2013
Messages
2
Reaction score
0
Points
0
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

Wally

New member
Joined
May 7, 2013
Messages
2
Reaction score
0
Points
0
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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,"")
 
Top