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?

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!

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?

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?

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?

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?

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
---

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
---

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
---

"Chad F" wrote:
Max, This worked great! Thank you!

Welcome, pl press the YES button (like the one below) in that response

To handle each of the 2 scenarios, you just need to tweak the criteria
formula in A1 to suit, then copy A1 down. No need to change the formulas in
col B.

..pull in all items that start with 804 AND all items that start with 805
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="803", LEFT(Sheet1!A1,3)="804"),Sheet1!A1+ROW()/10^10,""))

pull in .. all items between the range 103000 thru 705999**
=IF(Sheet1!A1="","",IF(AND(Sheet1!A1=103000,Sheet 1!A1=705999),Sheet1!A1+ROW()/10^10,""))

**assuming source data are real nums

If source data could contain mixed text nums and real nums,
use a "+0" to coerce it, viz:
=IF(Sheet1!A1="","",IF(AND(Sheet1!A1+0=103000,She et1!A1+0<=705999),Sheet1!A1+ROW()/10^10,""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Sorry, there were 2 typos in the earlier response, corrected below:

..pull in all items that start with 804 AND all items that start with 805
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="804", LEFT(Sheet1!A1,3)="805"),Sheet1!A1+ROW()/10^10,""))

pull in .. all items between the range 103000 thru 705999**
=IF(Sheet1!A1="","",IF(AND(Sheet1!A1=103000,Sheet 1!A1<=705999),Sheet1!A1+ROW()/10^10,""))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Max,

Thanks for all of your help! I'm almost there, but I need a little more
assistance please.

I now have the single ("803") working, the double ("804" and "805") working,
but the range does not seem to be working exactly right. I did have to use
the "+0" to get it to work so far.

Here is what I am seeing starting at A1:

#VALUE (Cell A1)
(A2:A438 are blank)
103000 (Cell A439)
103005
103010
104000
104005
etc.
705980 (Cell A1837)

Moving to B1, I am trying to shrink the list and get rid of the gaps, but
here is what I am seeing:

#VALUE (B1:B1399)


Here are the formulas that I have right now:

(A1)
=IF(Import!C1="","",IF(AND(Import!C1+0=103000,Imp ort!C1+0<=705999),Import!C1+ROW()/10^10,""))

(B1)
=IF(ROW()COUNT(A:A),"",INDEX(Import!C:C,MATCH(SMA LL(A:A,ROW()),A:A,0)))

Have I done something wrong? Please look it over and let me know.

Thanks,
Chad

"Max" wrote:

"Chad F" wrote:
Max, This worked great! Thank you!

Welcome, pl press the YES button (like the one below) in that response

To handle each of the 2 scenarios, you just need to tweak the criteria
formula in A1 to suit, then copy A1 down. No need to change the formulas in
col B.

..pull in all items that start with 804 AND all items that start with 805
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="803", LEFT(Sheet1!A1,3)="804"),Sheet1!A1+ROW()/10^10,""))

pull in .. all items between the range 103000 thru 705999**
=IF(Sheet1!A1="","",IF(AND(Sheet1!A1=103000,Sheet 1!A1=705999),Sheet1!A1+ROW()/10^10,""))

**assuming source data are real nums

If source data could contain mixed text nums and real nums,
use a "+0" to coerce it, viz:
=IF(Sheet1!A1="","",IF(AND(Sheet1!A1+0=103000,She et1!A1+0<=705999),Sheet1!A1+ROW()/10^10,""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Chad,
(A1)
=IF(Import!C1="","",IF(AND(Import!C1+0=103000,Imp ort!C1+0<=705999),Import!C1+ROW()/10^10,""))

Use this in A1, copy down (front IF trap modified to trap non-numerics):
=IF(OR(Import!C1="",ISERROR(Import!A1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))

The above should settle it fine for you
Pl press the YES buttons in ALL responses which helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

whoops, missed out correcting one "A1" to your "C1" earlier

Use this in A1, copy down (front IF trap modified to trap non-numerics):
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))

Pl press the YES buttons in ALL responses which helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Max,

I responded yes to all of your responses. Thank you!

When I put in the formula below, now I have "True" in cells A1:A4000. Any
ideas?

"Max" wrote:

whoops, missed out correcting one "A1" to your "C1" earlier

Use this in A1, copy down (front IF trap modified to trap non-numerics):
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))

Pl press the YES buttons in ALL responses which helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

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?

"Chad F" wrote
Max,
I responded yes to all of your responses. Thank you!

Yes, I can see that. Thanks

When I put in the formula below, now I have "True" in cells A1:A4000.
Any ideas?

It should work fine. You can proof it by manually entering some test nums
within and outside the range into Import's col C and see the returns

Probably your imported data contains some other "invisible" stuff which
needs to be cleaned off.

Try this link for Dave M's Sub TrimALL():
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Run the sub on your imported data ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Max,

I tried the trim macro that you referenced, but nothing happened. So, I
created two new sheets, one named "Test Import" and one named "Test Results".
On the Test Import sheet, I keyed in my item numbers from scratch (not
copied from another source). I then entered the same formulas that you gave
me for columns A and B on the Test Results sheet and copied them down to row
4000. Now all I have in A:1 thru A:4000 is "#NAME?", and I have no results
in column B. Any suggestions?

"Max" wrote:

"Chad F" wrote
Max,
I responded yes to all of your responses. Thank you!

Yes, I can see that. Thanks

When I put in the formula below, now I have "True" in cells A1:A4000.
Any ideas?

It should work fine. You can proof it by manually entering some test nums
within and outside the range into Import's col C and see the returns

Probably your imported data contains some other "invisible" stuff which
needs to be cleaned off.

Try this link for Dave M's Sub TrimALL():
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Run the sub on your imported data ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Chad,

Can you upload your sample file using a free filehost,
then post a link to it here?

You can use this "easy-to-use" free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply

(desensitize the data in your sample as required)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

I tried the trim macro that you referenced, but nothing happened. So, I
created two new sheets, one named "Test Import" and one named "Test
Results".
On the Test Import sheet, I keyed in my item numbers from scratch (not
copied from another source). I then entered the same formulas that you
gave
me for columns A and B on the Test Results sheet and copied them down to
row
4000. Now all I have in A:1 thru A:4000 is "#NAME?", and I have no
results
in column B. Any suggestions?

Chad,

Here's my working sample for easy ref :
http://freefilehosting.net/download/440g0
Extract multiple num results in another sht n sort ascending.xls

In A1:
=IF(OR(Import!A1="",ISERROR(Import!A1+0)),"",
IF(AND(Import!A1+0=103000,Import!A1+0<=705999),Im port!A1+ROW()/10^10,""))

In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Import!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 "Import".
Minimize/hide col A. Col B returns the required ascending sort of nums
(inclusive text nums) in source data within the specified range, ie between
103000 to 705999 (inclusive)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Max,

Here is my file. Please take a look and see if I am messing something up.
http://freefilehosting.net/download/440g5

Thanks,
Chad

"Max" wrote:

Chad,

Here's my working sample for easy ref :
http://freefilehosting.net/download/440g0
Extract multiple num results in another sht n sort ascending.xls

In A1:
=IF(OR(Import!A1="",ISERROR(Import!A1+0)),"",
IF(AND(Import!A1+0=103000,Import!A1+0<=705999),Im port!A1+ROW()/10^10,""))

In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Import!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 "Import".
Minimize/hide col A. Col B returns the required ascending sort of nums
(inclusive text nums) in source data within the specified range, ie between
103000 to 705999 (inclusive)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

In Sheet1,
you had a "double" formula in A1 down which was incorrect, ie:
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))

(How did you end up with the double w/o noticing it?)

Just delete away one of the double, ie correct it in A1 to:
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))
then copy A1 down, and the results will magically appear in col B
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

Here is my file. Please take a look and see if I am messing something up.
http://freefilehosting.net/download/440g5

Thanks,
Chad

Max,

THANK YOU!!!!!!!!

I don't know how I did that. I think that I was trying to copy and replace
it in the formula bar, but I must have actually added the copy to the end.
As far as not seeing it, I don't know...call it stupidity.

Anyway, I knew that I must have been overlooking something. Thank you for
your time and patience! This will help me a lot.

I hope that I don't have to bother you on this anymore.

Best Regards,
Chad

"Max" wrote:

In Sheet1,
you had a "double" formula in A1 down which was incorrect, ie:
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))

(How did you end up with the double w/o noticing it?)

Just delete away one of the double, ie correct it in A1 to:
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AN D(Import!C1+0=103000,Import!C1+0<=705999),Import! C1+ROW()/10^10,""))
then copy A1 down, and the results will magically appear in col B
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

Here is my file. Please take a look and see if I am messing something up.
http://freefilehosting.net/download/440g5

Thanks,
Chad

No prob, you're welcome.
Glad that took care of it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

THANK YOU!!!!!!!!

I don't know how I did that. I think that I was trying to copy and
replace
it in the formula bar, but I must have actually added the copy to the end.
As far as not seeing it, I don't know...call it stupidity.

Anyway, I knew that I must have been overlooking something. Thank you for
your time and patience! This will help me a lot.

I hope that I don't have to bother you on this anymore.

Best Regards,
Chad

Max,

It's me again. Sorry to bother you, but I ran into something else.

On the "AND" formula below, I have a worksheet that I am trying to pull in
all of the discontinued items. The bad thing that we do (because we are
using an inferior system) is when an item is discontinued, the item number
(or I.D.) is changed, for instance, from 803100 to D/M 01.12.09A or D/F
01.12.09B. Don't ask me why, but we do this.

Anyway, the "D/M" is when the manufacturer discontinues the item, and the
"D/F" is when we do. That way, the customer can look at this list and see if
they need to disc. the item themselves or just buy it somewhere else, if it
is still available.

So, I tried using the "AND" formula, but I believe that it is viewing D/M as
a mathematical equation. How can I search for these prefixes correctly? My
shared file is still out there if you need to look at it (the old version
that you have seen).

By the way, how do I remove the shared file once you are done using it?

The formula that I put in was:
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="D/F",LEFT(Sheet1!A1,3)="D/M"),Sheet1!A1+ROW()/10^10,""))



Thanks,
Chad


"Max" wrote:

Sorry, there were 2 typos in the earlier response, corrected below:

..pull in all items that start with 804 AND all items that start with 805
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="804", LEFT(Sheet1!A1,3)="805"),Sheet1!A1+ROW()/10^10,""))

pull in .. all items between the range 103000 thru 705999**
=IF(Sheet1!A1="","",IF(AND(Sheet1!A1=103000,Sheet 1!A1<=705999),Sheet1!A1+ROW()/10^10,""))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Your adapted expression to trap the new criteria is fine, except that you
need to drop the ascending sort tie-breaker bit: Sheet1!A1+ROW()/10^10
since the data in Sheet1's col A is no longer a number, eg: D/M 01.12.09A
(this was the part causing problems, not the LEFT parts)

In Sheet2
In A1, replace your:
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="D/F",LEFT(Sheet1!A1,3)="D/M"),Sheet1!A1+ROW()/10^10,""))

with this:
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="D/F",LEFT(Sheet1!A1,3)="D/M"),ROW(),""))

Then use in B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW())))
and copy A1:B1 down

(Above is essentially the method shown in my very 1st response)

Start new threads for any new queries that you might have.
Better exposure for your queries to all responders.

P/s: Think the free filehost will purge the sample file after a certain
period.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

It's me again. Sorry to bother you, but I ran into something else.

On the "AND" formula below, I have a worksheet that I am trying to pull in
all of the discontinued items. The bad thing that we do (because we are
using an inferior system) is when an item is discontinued, the item number
(or I.D.) is changed, for instance, from 803100 to D/M 01.12.09A or D/F
01.12.09B. Don't ask me why, but we do this.

Anyway, the "D/M" is when the manufacturer discontinues the item, and the
"D/F" is when we do. That way, the customer can look at this list and see
if
they need to disc. the item themselves or just buy it somewhere else, if
it
is still available.

So, I tried using the "AND" formula, but I believe that it is viewing D/M
as
a mathematical equation. How can I search for these prefixes correctly?
My
shared file is still out there if you need to look at it (the old version
that you have seen).

By the way, how do I remove the shared file once you are done using it?

The formula that I put in was:
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="D/F",LEFT(Sheet1!A1,3)="D/M"),Sheet1!A1+ROW()/10^10,""))

Max,

Thanks! That worked.

Speaking of sorting. I am pulling in the description from Sheet1 with a
VLOOKUP formula and I want to enter a formula to automatically sort the items
alphabetically using the descriptions that pull in. How do I do this?

As far as starting new threads, I will try to do that in the future but I
don't want to have to start over explaining the history. Also, you have been
so helpful that I would like for you to continue to help me, even though you
are probably pulling tired of me by now.

If we fix the issue above, I will start new threads on a couple of more
items that I have. Just be on the look out for the same subjuect "Multiple
Item Lookup", if you would like to assist.

Thanks,
Chad

"Max" wrote:

Your adapted expression to trap the new criteria is fine, except that you
need to drop the ascending sort tie-breaker bit: Sheet1!A1+ROW()/10^10
since the data in Sheet1's col A is no longer a number, eg: D/M 01.12.09A
(this was the part causing problems, not the LEFT parts)

In Sheet2
In A1, replace your:
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="D/F",LEFT(Sheet1!A1,3)="D/M"),Sheet1!A1+ROW()/10^10,""))

with this:
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="D/F",LEFT(Sheet1!A1,3)="D/M"),ROW(),""))

Then use in B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW())))
and copy A1:B1 down

(Above is essentially the method shown in my very 1st response)

Start new threads for any new queries that you might have.
Better exposure for your queries to all responders.

P/s: Think the free filehost will purge the sample file after a certain
period.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

It's me again. Sorry to bother you, but I ran into something else.

On the "AND" formula below, I have a worksheet that I am trying to pull in
all of the discontinued items. The bad thing that we do (because we are
using an inferior system) is when an item is discontinued, the item number
(or I.D.) is changed, for instance, from 803100 to D/M 01.12.09A or D/F
01.12.09B. Don't ask me why, but we do this.

Anyway, the "D/M" is when the manufacturer discontinues the item, and the
"D/F" is when we do. That way, the customer can look at this list and see
if
they need to disc. the item themselves or just buy it somewhere else, if
it
is still available.

So, I tried using the "AND" formula, but I believe that it is viewing D/M
as
a mathematical equation. How can I search for these prefixes correctly?
My
shared file is still out there if you need to look at it (the old version
that you have seen).

By the way, how do I remove the shared file once you are done using it?

The formula that I put in was:
=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="D/F",LEFT(Sheet1!A1,3)="D/M"),Sheet1!A1+ROW()/10^10,""))

.. to automatically sort the items alphabetically ...
I'm not sure there's a formulas way to do an auto "full" alphabetic sort
which can emulate Excel's Data Sort functionality.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,
Thanks! That worked.

Speaking of sorting. I am pulling in the description from Sheet1 with a
VLOOKUP formula and I want to enter a formula to automatically sort the
items
alphabetically using the descriptions that pull in. How do I do this?

As far as starting new threads, I will try to do that in the future but I
don't want to have to start over explaining the history. Also, you have
been
so helpful that I would like for you to continue to help me, even though
you
are probably pulling tired of me by now.

If we fix the issue above, I will start new threads on a couple of more
items that I have. Just be on the look out for the same subjuect
"Multiple
Item Lookup", if you would like to assist.

Thanks,
Chad

"Max" wrote...
.. to automatically sort the items alphabetically ...

I'm not sure there's a formulas way to do an auto "full" alphabetic sort
which can emulate Excel's Data Sort functionality.
....

If the source data were in a range named rng and the topmost result
cell were E3, for ascending

E3:
=INDEX(rng,MATCH(0,COUNTIF(rng,"<"&rng),0))

E4:
=IF(COUNTIF(rng,E3)COUNTIF(E$3:E3,E3),E3,
INDEX(rng,MATCH(ROWS(E$3:E3),COUNTIF(rng,"<"&rng), 0)))

Fill E4 down as far as needed.

For descending, replace the "<" with "" in the 2nd arguments of the
COUNTIF calls in the formulas above.

Harlan,

This did not seem to work. Let me explain a little more.

I have a source data sheet named "Import".

Step1: In another sheet, I am searching column C in the source data for
items that begin with either "D/M" or "D/F" and importing them into the
current sheet in column A.

Formula in current sheet A1:A4000 is...
=IF(Import!C1="","",IF(OR(LEFT(Import!C1,3)="D/F",LEFT(Import!C1,3)="D/M"),ROW(),""))

The "C1" increments with each row all the way to "C4000"

Step2: Then in column B of the current sheet, I am removing the gaps in the
rows of column A so that all of the returns in column A start in B1.

Formula in current sheet B1:B4000 is...
=IF(ROW()COUNT(A:A),"",INDEX(Import!C:C,SMALL(A:A ,ROW())))

Step3: Then in Column C, I am doing a VLOOKUP formula that if there is a
returned value in column B of the currenst sheet (after removing the gaps) it
brings in the item description from the source data column G.

Formula in current sheet C1:C618 is...
=VLOOKUP(B1,Import!$C$1:$Z$4000,5,FALSE)

The "B1" increments with each row all the way to "B618"

Now, I would like the lookup formula above to be copied all the way down in
column C and not stop at C618, but I am afraid of the "N/A" returns messing
up the next step.

Before I get to the next step, I would also like to explain that I will have
other lookup formulas similar to the one above that will bring in other
information such as price, size, etc. into other columns.

Step4: I would now like to sort the whole sheet automatically by the
descriptions in Column C of the current sheet in ascending order. Can this
be done? I think that it may not be possible. I can't even get the Data
Sort tool to work.

Can you help?

Thanks,
Chad

"Harlan Grove" wrote:

"Max" wrote...
.. to automatically sort the items alphabetically ...

I'm not sure there's a formulas way to do an auto "full" alphabetic sort
which can emulate Excel's Data Sort functionality.
....

If the source data were in a range named rng and the topmost result
cell were E3, for ascending

E3:
=INDEX(rng,MATCH(0,COUNTIF(rng,"<"&rng),0))

E4:
=IF(COUNTIF(rng,E3)COUNTIF(E$3:E3,E3),E3,
INDEX(rng,MATCH(ROWS(E$3:E3),COUNTIF(rng,"<"&rng), 0)))

Fill E4 down as far as needed.

For descending, replace the "<" with "" in the 2nd arguments of the
COUNTIF calls in the formulas above.