Multiple Item Lookup

[Chad F]

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?

 

[Luke M]

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.

 

[Michael]

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.

 

[Shane Devenshire]

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.

 

[Chad F]

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.

 

[Chad F]

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?

 

[Chad F]

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?

 

[T. Valko]

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

 

[Shane Devenshire]

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.

 

[Max]

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

 

[Max]

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",Sheet1!A1+ROW()/10^10,""))

In B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Sheet1!A:A,MATCH(SMALL(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]

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",Sheet1!A1-ROW()/10^10,""))

In B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Sheet1!A:A,MATCH(LARGE(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
---

 

[Chad F]

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]

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,Sheet1!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,Sheet1!A1+0<=705999),Sheet1!A1+ROW()/10^10,""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[Max]

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

=IF(Sheet1!A1="","",IF(OR(LEFT(Sheet1!A1,3)="804",LEFT(Sheet1!A1,3)="805"),Sheet1!A1+ROW()/10^10,""))

=IF(Sheet1!A1="","",IF(AND(Sheet1!A1>=103000,Sheet1!A1<=705999),Sheet1!A1+ROW()/10^10,""))

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

 

[Chad F]

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,Import!C1+0<=705999),Import!C1+ROW()/10^10,""))

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

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

Thanks,
Chad

 

[Max]

Chad,

(A1)
=IF(Import!C1="","",IF(AND(Import!C1+0>=103000,Import!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(AND(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
---

 

[Max]

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

 

[Chad F]

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?

 

[Chad F]

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?