[Tonservices]

I was referring to this formula in my previous message. Thanks.

IF(ISNA(INDEX(Sheet2!A:B,MATCH(I2,Sheet2!B:B,0),1)),"MISSING"

[slurpee55]

Frankly, may I ask why you are bothering to mess with this? You asked for an answer and I gave you a perfectly good one. Sorting the names is not for that formula. Also, your formula doesn't work as you have posted it.
But frankly, if you want to mess around with VLOOKUP and ignore what I did for you, go ahead. Just don't expect any sympathy from me. I do have a job.

[Tonservices]

I'm not having the same success as you are in making this formula work. I'm sorry for trying to change things around with the formula. By sending me a copy of my second attachment I can see it visually. You appear to be frustrated, I'm sorry.

[slurpee55]

The formula works in the file I uploaded, so what is different? Just paste the formula into the formula bar after selecting a cell on Sheet1 - any cell, whether in I or L or wherever you want - and drag it down.
As long as the data in Sheet1 stays in the same column and the data in Sheet2 stays in the same columns, it will work.

[slurpee55]

So, let me reiterate.
Copy the following as it is

=IF(ISNA(INDEX(Sheet2!A:B,MATCH(I2,Sheet2!B:B,0),1)),"MISSING",INDEX(Sheet2 !A:B,MATCH(I2,Sheet2!B:B,0),1))

Click on cell G2 or L2 or wherever you want the result to appear.
Click in the formula bar.
Right-click and paste.
Hit enter.
Go back to the original cell and when your mouse is over the bottom right corner it will become a small cross.
Left-click on the original cell when it does, and, holding the mouse key down, drag the cursor down the column.
When you get to the bottom of the data, let go of the mouse key.

[Tonservices]

Slurpee55,
I am sorry for being a "dumb blonde". I truely appreciate your time, effort, and being patience with me. Again, Thanks so much!!!! This problem is solved!!!!!

[slurpee55]

I still wish we had worked out the drop-down list. I just have never made one (well, frankly, I learned a lot about Index/Match doing this!) so I don't know how to do it.
Usually people have a list elsewhere on the same worksheet to reference, but that is about all I could figure out.

[Tonservices]

I think a drop down list is a great idea but I copy and paste this information from another program. I thought this approach is faster for me than a drop down box.

[slurpee55]

Frankly, despite all the work that went into this, have you ever used Access? You could set up a table with the stores and codes, make another table with the imported data (switching it out every week, month, whenever) and then have a single query that would show all the data plus the matched codes (and the concatenation).
It would be very easy to do.
You could also quickly find items that did not match - have that query always set up as well.

[Tonservices]

I use access almost daily basis but never thought of doing a query/table. The reason why I used excel was due to a ramdon variable function. I use a random variable and site number to create a larger number. Is there a way to drag and pull a random number? A random number is generated and I hand type this number in. Is there a way to drag and pull a random number?

[slurpee55]

Oh, heavens yes!!!!
For instance, in Access, you can set a field to be an Autonumber, then go to the table design and in that field's General tab, New Values can be set to Random. It is possible for these number to be negative, and, if you don't want that, use a formula like this as an expression in a query of that table (in my table I called the random autonumber "auto")

Expr2: IIf([auto]<0,[auto]*-1,[auto])

(Note that, like in Excel, these are not true random numbers - actually, they are less so than Excel's, but, unless you are using them for tricky statistical purposes, that should not matter.)

Another thing you can do is to use the RAND() or RANDBETWEEN() function in Excel, copy the whole column, go to Access and paste it straight in. Note, however, that if your random number is 0.47071623, unless you format Access carefully, it will show it as 0 (or, perhaps as .47). If the number is just there to randomize data, multiply it all by, say, 1000 before doing this.
But the random Autonumber works better, I think - as an example, in the first 5 rows I generated just while writing this, I got
auto
165621499
1479590944
-1194903135
2081146702
815930551
That's a low of -1194903135, a high of 2081146702 and a difference of 3276049837. Graphing it shows the apparent randomness all the more.

[Tonservices]

I need the numbers to be all positive. They can range from 6 to 8 digits long. Will this formula work for excel IIf([auto]<0,[auto]*-1,[auto])? Does it matter if I already have used 3,000 other random numbers? Let me know what you recommend for this problem.
Stupid Question - All this help is 100% free?

[slurpee55]

No, Iif is strictly an Access function, However, you can generate the numbers you need in Excel using this formula
=RANDBETWEEN(100000,99999999)
which derives a random number between the low of 100000 (including 100000) and a high of 99999999.
Another way to do it (and one that seems to give more variation) would be to enter 3 formulas.
=TRUNC(RAND(),6)*10000000
=TRUNC(RAND(),7)*10000000
and
=TRUNC(RAND(),8)*100000000
all return 6 to 8 digit figures. RANDBETWEEN() is more reliable, but hey, variety is the spice of life. I usually use =LEN() to find out how many digits are in a cell when I have to have a specific number. Also useful are the =MIN() and =MAX() formulas. Used in conjunction with =LEN(), you can easily find out the length of the shortest and the longest number in a column or an array.

[slurpee55]

Another thing - the [auto] field was the name of the field in the table I made in Access - it isn't a function or calling anything special.
And equivalent in Excel would be to write
=IF(A1<0,A1*-1,A1)

[slurpee55]

Originally Posted by Tonservices

Stupid Question - All this help is 100% free?

Yeah, cool, isn't it?

Two things you can do: help others and donate http://www.techguy.org/donate.html
(And, if you donate, you don't see the ads anymore!)