Replies

WOW
I really love the forum!

Chuck

Steve,

I did it by nesting IF statements. It does not exceed the limit.
=IF(M2>0,N2,IF(K2>0,L2,IF(I2>0,J2,IF(G2>0,H2,IF(F2>0,F2,IF(C2>0,D2,IF(A2>0,B2,”nutin yet”)))))))
Why would the Array formula be preferred?
Is the array approach better, performance wise?

Chuck

amcs,

Column G on sheet main is entered as numbers and the column A on sheet lookup is all entered as Text.
I think you need to have both as Numbers or both as Text. Apples and Oranges do not mix well.

Chuck

(Edited by WebGenii on 28-Feb-03 12:25. to add links. Linking to posts is easy, just put square brackets around the following: post#=yourpostnumber)

Roberta,

You might find what you are looking for in post 227113, or you might wish to check out the Flexfind Utility by Jan (post 229625)

Hope this helps,
Chuck

Steve,

Dead on! Works great! It even works when I copy it to column L.
This is what the user said he wanted and he will be very happy.

I thank you and the entire Forum in general.
I Love this place!

Chuck

Steve,
I tried copying your formula into M2 and copied it down.
Only one date matched. I am attaching the workbook for you to look at.
I am sure this is the right track, but I got a detour!

Thanks,
Chuck

Legare,

Sorry for not being more clear. The worksheet the user recieves is make up of random picks. Each row was randomly selected to be in the samplepick.
The user wants to know what date should be associated with each day.
The sample I sent shows the first row date (entered by user) as 3/16/2003. The next three rows are all in the same week. So the Thursday of the same week has to be 3/20/2003.
He is NOT looking for a random number generator -That part was done by his samplepick software. Hope this helps clarify.

Chuck

I would be amazed if I have stumped the Lounge on this. grins

chuck

Steve,

I know. That has bothered me for some time. In trying to get the Save-As to work, I even converted the entries to pure text first, then did the Save-As but the result was the same.
I have had trouble converting Excel files to CSV files for my imports, but for different reasons. That was how I knew to use Access and save the query as a CSV.
Excel is Very powerful, but can also be Very frustrating.
It certainly helped when I found this forum! I have learned more for reading this forum than all the Excel classes I have taken.

Chuck

Boyley,

Yes. You can put a formula in a blank column that will convert the text to proper case.
If the text is in column A (starting in row 1) and column D is blank, enter the following formula in column D.
=Proper(A1) then copy this formula down to convert all the entries in column A.
Press F9 to calculate the workbook.
You will probably want to convert the formulas to values after they are calculated.
Select all the formulas and press Ctrl C (copy), then select the top cell where you want the converted text to appear and choose Edit >
Paste Special > Values.
Hope this helps.

Chuck

bigfilo,

I cobbled together a macro that appears to work. This is by NO means the best that can be done, I just am not very good at this -YET.

Chuck

Buckshot,
I have attached a workbook that shows one way of doing what I think you want. It involves inserting two columns on each sheet. The first column has formulas that add the strings together from col A and col B (Matt and TX becomes MattTX). The cells containing these formulas is assigned a range name (JanList on sheet JAN and FebList on sheet FEB). The second column has a formula that determines if the combined string exists in the named range on the other sheet.
Then all you have to do is sort the data (on sheet FEB) based on the column that indicates if it is a new name or an existing name and copy the desired rows to the new sheet.
I am sure there is a way to do this with VBA but the process I laid out could be done manually in less than 10 minutes.

Chuck

Beryl,

It has to be entered as an Array formula. After you type the formula in the cell press Crtl and Shift while pressing enter key.
Have a Great Holiday Season everyone!
And, yes you replace the A1:A100,A1:A100 with the actual range.

Chuck

It is easy once you get the hang of it.
I created a simple sample of Vlookups that I have attached.
If you need more help, please send a sample of the workbook.

Chuck

Post Edited — due to stupid mistake!
I tried using the column and Row headers.
It worked fine and I posted it as a solution.
THEN I hit calc and saw it did NOT work.
Do not bother with this method….
Sorry

Chuck