VIN Number sorting

[HRassist]

I have over 12000 VIN numbers on a worksheet. What I would like to do is find
an equation that would pull all of the VIN numbers that started with a
certain sequence of numbers & Letters -
Ie all VIN numbers that begin with RVS1T3. I've tried using the V-Lookup
and I can't seem to get the equation to pull correctly.
Please help!

Thank you

 

[bj]

check out <data><filter> either auto filter or advanced filter could
potencially do what you want

 

[HRassist]

With the advanced filter function I couldn't find a place to indicate the
beginning sequence I was looking for (I.E.the first 6 digits of the VIN
numbers)--- also--- Would a count function work with the sort filter?
Because-- after I have the list of VIN numbers- I will need to count how many
there are?

Thanks BJ!

 

[HRassist]

I got the filter to work (I tried it before and I got all of the vin
numbers...- but I didn't know about the advanced selection)-- Now-- how can I
do a Count on a filtered column with out it counting all of the rows?

Thank you

 

[HRassist]

With the advanced filter function I couldn't find a place to indicate the
beginning sequence I was looking for (I.E.the first 6 digits of the VIN
numbers)--- also--- Would a count function work with the sort filter?
Because-- after I have the list of VIN numbers- I will need to count how many
there are?

Thanks BJ!

 

[HRassist]

I got the filter to work!!!! -- Now.. one more thing.... how can I use a
Count function on a filtered column with out it counting all of the rows that
are hidden? (in order to count how many vins were pulled?)

THANKS BJ!!

 

[Teethless mama]

=SUBTOTAL(3,your range)

I got the filter to work!!!! -- Now.. one more thing.... how can I use a
Count function on a filtered column with out it counting all of the rows that
are hidden? (in order to count how many vins were pulled?)

THANKS BJ!!

 

[bj]

you don't need the filtered colimn to do the count
you could use several other functions such as
=sumproduct(--(left(Vin_range,6)="RVS1T3"))
or
=countif(Vin_range,"RVS1T3*")
or if you have the Rv... in another cell (D3?)
=countif(Vin_range,D3&"*")

 

[bj]

good catch, I forgot subtotal

=SUBTOTAL(3,your range)

 

[HRassist]

THANK YOU EVERYONE!!! IT WORKS PERFECTLY!!!

you don't need the filtered colimn to do the count
you could use several other functions such as
=sumproduct(--(left(Vin_range,6)="RVS1T3"))
or
=countif(Vin_range,"RVS1T3*")
or if you have the Rv... in another cell (D3?)
=countif(Vin_range,D3&"*")

 

[Gord Dibben]

=SUBTOTAL(2,A2:A1405)

See help on the SUBTOTAL Function for more on filtered rows.


Gord Dibben MS Excel MVP