Can anyone possibly suggest a solution to this small problem.
I'm sure it's very easy, but I just can't think of how to do it.

I have a worksheet with the following columns

A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
2 Y 876
1 X 6556
3 Y 554
1 X 222


I would like to create a table on a separate worksheet with the same column
headings, but only have a list of the type 'X' (i.e. ignore everything apart
from X)
Like this, without any blank rows between:
A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
1 X 6656
1 X 222

However, there are one or two restrictions...
Also, I cannot modify the original table (I cannot sort the data)
Occasionally, the columns are in a different order when I recieve the data
(therefore I name the columns are 'ranges' [in case it helps])

Hopefully I have explained my problem clearly.

Thank you in advance.

Hi

Use Advanced Filter, but be sure to start the filter from your Destination
worksheet.
Full details can be found at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html

--
Regards
Roger Govier

"Alvarez" wrote in message
...
Can anyone possibly suggest a solution to this small problem.
I'm sure it's very easy, but I just can't think of how to do it.

I have a worksheet with the following columns

A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
2 Y 876
1 X 6556
3 Y 554
1 X 222


I would like to create a table on a separate worksheet with the same
column
headings, but only have a list of the type 'X' (i.e. ignore everything
apart
from X)
Like this, without any blank rows between:
A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
1 X 6656
1 X 222

However, there are one or two restrictions...
Also, I cannot modify the original table (I cannot sort the data)
Occasionally, the columns are in a different order when I recieve the data
(therefore I name the columns are 'ranges' [in case it helps])

Hopefully I have explained my problem clearly.

Thank you in advance.

Use Datafilterautofilterfilter x on col Bcopy to the other sheet. Record
a macro if desired and clean it up.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Alvarez" wrote in message
...
Can anyone possibly suggest a solution to this small problem.
I'm sure it's very easy, but I just can't think of how to do it.

I have a worksheet with the following columns

A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
2 Y 876
1 X 6556
3 Y 554
1 X 222


I would like to create a table on a separate worksheet with the same
column
headings, but only have a list of the type 'X' (i.e. ignore everything
apart
from X)
Like this, without any blank rows between:
A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
1 X 6656
1 X 222

However, there are one or two restrictions...
Also, I cannot modify the original table (I cannot sort the data)
Occasionally, the columns are in a different order when I recieve the data
(therefore I name the columns are 'ranges' [in case it helps])

Hopefully I have explained my problem clearly.

Thank you in advance.

Easiest would be to record a macro to filter on X, copy and paste to the new
sheet.

Otherwise you need to build a formula (using MATCH and INDIRECT is one way)
which will find the row nos of X and pull values from there.

"Alvarez" wrote:

Can anyone possibly suggest a solution to this small problem.
I'm sure it's very easy, but I just can't think of how to do it.

I have a worksheet with the following columns

A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
2 Y 876
1 X 6556
3 Y 554
1 X 222


I would like to create a table on a separate worksheet with the same column
headings, but only have a list of the type 'X' (i.e. ignore everything apart
from X)
Like this, without any blank rows between:
A B C
Quantity Type Distance(mm)
3 X 1243
1 X 2256
1 X 6656
1 X 222

However, there are one or two restrictions...
Also, I cannot modify the original table (I cannot sort the data)
Occasionally, the columns are in a different order when I recieve the data
(therefore I name the columns are 'ranges' [in case it helps])

Hopefully I have explained my problem clearly.

Thank you in advance.

Thanks for the suggestions folks.
Much appreciated.

A macro looks the only way forward as it's a template I'm creating and I'll
want to limit my input and data manipulation to as little as possible.

I was really hoping someone would suggest a function that I'd missed and
that it would be an easy solution.
:-(

Thanks again
:-)