I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all the
matches. How do I do this?

Well, you could either filter Sheet 1 using autofilter, custom, look for
"803*" and then copy and paste
Or, if you don't mind gaps, in sheet 2
=IF(LEFT('Sheet1'!A1,3)="803",A1,"")
and copy down.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chad F" wrote:

I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all the
matches. How do I do this?

Thanks for the reply!

I know that I can do the IF statement that you said, but I do not want the
gaps. Also, I do not want to filter because I will have to do this on
several sheets with several different item number ranges. Do you know how to
eliminate these "gaps" so that it does not matter what row they are located
in on Sheet1, it will just list the 50 or so matche in A1 thru A50 on sheet2.

"Luke M" wrote:

Well, you could either filter Sheet 1 using autofilter, custom, look for
"803*" and then copy and paste
Or, if you don't mind gaps, in sheet 2
=IF(LEFT('Sheet1'!A1,3)="803",A1,"")
and copy down.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chad F" wrote:

I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all the
matches. How do I do this?

Do you know how to eliminate these "gaps"
so that it does not matter what row they are located
in on Sheet1, it will just list the 50 or so matched in A1 thru A50 on sheet2.

You can achieve the automation sought w/o the gaps easily in this manner

In Sheet2
In A1:
=IF(Sheet1!A1="","",IF(LEFT(Sheet1!A1,3)="803",ROW (),""))

In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW())))
Copy A1:B1 down to cover the max expected extent of source data in Sheet1's
col A, say down to B100? Minimize/hide col A. Col B returns the desired
results, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

And if you additionally, would like the auto-extracts in Sheet2 to be in
sorted ascending order, use this set of formulas

In Sheet2
In A1:
=IF(Sheet1!A1="","",IF(LEFT(Sheet1!A1,3)="803",She et1!A1+ROW()/10^10,""))

In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0)))
Copy A1:B1 down to cover the max expected extent of source data in Sheet1's
col A, say down to B100? Minimize/hide col A. Col B returns the desired
results in sorted ascending order, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Max,

This worked great! Thank you!

Now, another question. What if I want to pull in more than just the items
that start with 803? For instance, I need to pull in on another tab all
items between the range 103000 thru 705999. Or I might need to pull in all
items that start with 804 AND all items that start with 805. Can you help
with these two scenarios?

"Max" wrote:

And if you additionally, would like the auto-extracts in Sheet2 to be in
sorted ascending order, use this set of formulas

In Sheet2
In A1:
=IF(Sheet1!A1="","",IF(LEFT(Sheet1!A1,3)="803",She et1!A1+ROW()/10^10,""))

In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0)))
Copy A1:B1 down to cover the max expected extent of source data in Sheet1's
col A, say down to B100? Minimize/hide col A. Col B returns the desired
results in sorted ascending order, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

And for the auto-extracts in Sheet2 to be in sorted descending order,
use this set of formulas

In Sheet2
In A1:
=IF(Sheet1!A1="","",IF(LEFT(Sheet1!A1,3)="803",She et1!A1-ROW()/10^10,""))

In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,MATCH(LAR GE(A:A,ROW()),A:A,0)))
Copy A1:B1 down to cover the max expected extent of source data in Sheet1's
col A, say down to B100? Minimize/hide col A. Col B returns the desired
results in sorted ascending order, all neatly bunched at the top.

Formulas above are similar to those for ascending sort,
except that LARGE replaces SMALL &
Sheet1!A1-ROW()/10^10
is used in the criteria formula instead of:
Sheet1!A1+ROW()/10^10
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

You could use Advance Filter, from the Main Menu Bar, select:
Data- Advanced Filter
On the Action Window Select: Copy to Another Location, and check the unique
records box; then on the Copy to Box select your destination sheet and range.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Chad F" wrote:

I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all the
matches. How do I do this?

Thanks for the reply, but I am trying to do this as an automatic function. I
am importing data from Another system into Sheet1 and want my formulas to
extract different sets of item numbers out to several worksheets without
having to manually copy them. Do you know how to do this?

"Michael" wrote:

You could use Advance Filter, from the Main Menu Bar, select:
Data- Advanced Filter
On the Action Window Select: Copy to Another Location, and check the unique
records box; then on the Copy to Box select your destination sheet and range.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Chad F" wrote:

I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all the
matches. How do I do this?

Hi,

1. Select you column of data and choose Data, Filter, AutoFilter
2. Open the filter and choose Custom
3. From the 1st drop down pick "is greater than or equal to" in the second
box enter 803000
4. Click And
5. From the 2nd drop down pick "is less than or equal to" and in the next
box enter 803999
6. Click OK, select all the data and copy it. Move to the other sheet and
paste it.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Chad F" <Chad wrote in message
...
I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all
the
matches. How do I do this?

Thanks for the reply, but I am trying to do this as an automatic function. I
am importing data from Another system into Sheet1 and want my formulas to
extract different sets of item numbers out to several worksheets without
having to manually copy them. Do you know how to do this?


"Shane Devenshire" wrote:

Hi,

1. Select you column of data and choose Data, Filter, AutoFilter
2. Open the filter and choose Custom
3. From the 1st drop down pick "is greater than or equal to" in the second
box enter 803000
4. Click And
5. From the 2nd drop down pick "is less than or equal to" and in the next
box enter 803999
6. Click OK, select all the data and copy it. Move to the other sheet and
paste it.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Chad F" <Chad wrote in message
...
I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all
the
matches. How do I do this?

Is your data sorted or grouped together so that all items that begin with
"803" are in a contiguous range?

--
Biff
Microsoft Excel MVP


"Chad F" <Chad wrote in message
...
I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all
the
matches. How do I do this?

T.,

Max has helped me with most of this, but look at my reply to Max on 1/9. I
am trying to do the same thing as I originally asked but with a range of item
numbers. Max has gotten me close, but can you help?

"T. Valko" wrote:

Is your data sorted or grouped together so that all items that begin with
"803" are in a contiguous range?

--
Biff
Microsoft Excel MVP


"Chad F" <Chad wrote in message
...
I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all
the
matches. How do I do this?

Hi,

Well, all the steps I suggested can be recorder with the macro recorder and
run automatically. Or if you are going to use a macro you can also use the
Advanced Filter command. this might be a little more challenging to set up
but once done, it will be automatic. However, to automate anything in Excel
you will need to post a lot of detail, not confidential detail, but data
layout desired locations, criteria for filters and exactly how you want the
different data to be handled, and so on.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Chad F" wrote:

I want to search sheet1 for all items that begin with "803" (803000 thru
803999) from A1:A4000 range and just list those item numbers in sheet2
starting in cell A1 with the lowest and continuing down columnA with all the
matches. How do I do this?