Multiple Item Lookup

  • Thread starter Thread starter Chad F
  • Start date Start date
Max,
I responded yes to all of your responses. Thank you!
Click to expand...

Yes, I can see that. Thanks
When I put in the formula below, now I have "True" in cells A1:A4000.
Any ideas?
Click to expand...

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

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

In B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMALL(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(AND(Import!C1+0>=103000,Import!C1+0<=705999),Import!C1+ROW()/10^10,""))
=IF(OR(Import!C1="",ISERROR(Import!C1+0)),"",IF(AND(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(AND(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
 
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
 
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,""))
Click to expand...

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 said:
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:
Click to expand...
=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
 
.. 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
 
Max said:
I'm not sure there's a formulas way to do an auto "full" alphabetic sort
which can emulate Excel's Data > Sort functionality.
Click to expand...
....

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