Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I thought I knew how to do this in Excel, but apparently not.

All I'm trying to do is write a simple (or at least it seems like it

should be simple to me) "If/Then" statement in Excel.

Here's what I'm working with.

I have two columns of data, lets call them column D and column F.

Column D has about 600 different values in it. Column F has about

3000 or so. I want to test each of the values in column F to see if

any of them are contained in column D.

Since I need to hold the range constant, I thought the forumula

was ...

=IF(F9=($D$9:$D$608),"true","false")

I thought the above statement would have Excel syntax something

along the lines of the following statement: "If the value of cell F9

is found in any of the cells between cells D9 and D608, then

say "true" and if not, say "false"."

Apparently not so.

What I'm trying to do in the forumla above is to see if the value in

cell F9 is the same as any of the values in the range of cells from

D9 through D608. Then I want to use the same forumla to test cell

F10 against cells D9 through D608, then cell F11 against D9 through

D608 and so forth.

I have some kind of a minor error in syntax in the formula, since

the formula is only testing cell F9 against cell D9.

I thought I'd specified the range correctly, and have

looked in the Help section, to no avail. I'm using an older version

of Excel (from Office 97).

Someone else told me I need to use an Array formula instead of an

If/Then statement to perform the above function. I'm not familiar

with Array formulas, or Lookup tables either for that matter, though

I'm sure there has to be an application in Excel that will do what I

want it to.

I'd just like to have a solution to what seems to me like it should

be a simple problem.

Any ideas?

I'm more newbie than expert - but this *might* work:

---------------------------------------------------------------

dim Drow

dim Frow

For Drow = 1 To 608

For Frow = 1 to 3000

IF cells(Drow, 4).value = cells(Frow, 6).value Then

'record the dupe somewhere - in this case the next cell over from

the Drow

cells(Drow, 7).value = "True"

Next Frow

Next Drow

-----------------------------------------------------------------

This will let you know which values from Column D appear in F, but

will overwrite multiple occurances, so you'll need to wtreak it if

you need that info.

Try using this formula in cell G1, then copy it to all rows below. It

does a lookup of the value from column F in column D, then checks to

see if it is N/A (meaning not found in column D). The ISNA function

will return true if not found. You want the opposite, hence the NOT

function.

=NOT(ISNA(VLOOKUP(F1,D:D,1,FALSE)))

You must to write the formula:

=Or(IF(F9=($D$9:$D$608),True,False))

Formula 1

True and False without “”

and then, before to press Enter, press Ctrl+Shift+Enter in the cell with the

formula. Excel will automatically add the braces.

{=Or(IF(F9=($D$9:$D$608),True,False))}

Formula 2

If you write the Formula 1 the result will return True if F9 has the same value

that any value in the range $D$9:$D$608 and the other hand this will return

False.

you don't need "if/then", simply use the VLOOKUP function. Check out

Excel's help on how to apply it. If you still any help, then let me

know.

Okay, well I figured out how to take care of the earlier question I had about

the Vlookup formula, and you're right ... so that's all taken care of.

I have another problem that I could use some help on however.

The file I'm using the Vlookup formula in is about 8.4 MB in size. When I try

to do sort functions, and sort several thousands of rows of data, I get a

message back saying the clipboard cannot be opened. If I sort a smaller portion

of the data -- say 3000 rows or so -- and go to save it, I get a message that

comes back saying Data Not Saved.

Can someone tell me what the problem is I'm having here?

I have 224 MB of RAM and a 40 GB hard drive of which only 6 GB or so is being

used. I'm running Windows XP Home Edition and am using an older version of

Excel -- from Office 97.

Also, and this is really frustrating too, but every 15 minutes or so when I'm

working on this spreadsheet I have a window that pops up saying something like

"Microsoft Excel has encountered a problem and needs to close." Needless to

say, I always lose my unsaved data.

Any suggestions?

I'm not sure if this is what you need but you could try selecting the

bit you want in the htm file - you'll have to do this with the mouse -

and then hitting Ctrl C for Copy and then pasting it into Notepad and

save it. See what you get. You may be able to import *that* file into

Excel.

A more complex solution could be to use ADO but I'm not sure about

that.

I know how to copy and paste the data from my Yahoo message boards into Excel

and that does work. The problem is it's very tedious and time consuming to do

that page by page. So, I thought there has to be a better way to do this and I

was wondering if the Excel import/export or one of the data management functions

would accomplish what I'm trying to do so that I could just hit a button and

send the data automatically to my spreadsheet and not have to do all the copying

and pasting.

Anyone have any ideas on this?

If you follow the export link then it's only one page you need to

copy.

If you want to automate it then (works in 2000 onwards, not sure

about 97):

go to data->Get external data->New web query

then browse to the *export* page of the yahoogroup member list and

choose the radio button for 'tables only'.

Then save the query as an .iqy file.

repeat this process for each group.

Run the query, and XL will open the web data in a weird format:

-save this file as a tab delimited .txt file.

close workbook

open the .txt file as a comma delimited file with ' as a text

delimiter.

Then just delete the first character in column A, and the last

character from column I and your done.

go to members page

press 'export' link

wait for page to load

press crl-A to select all the text

open notepad

press ctrl-V to paste

save as .csv

import the .csv into Excel.

once you've saved the notepad file as a .csv it'll open in Excel just

by clicking the file.

Didn't find what you were looking for?
Find more on "If/Then" statement in Excel
Or get search suggestion and latest updates.

Related Topics:

- Add-Ins loaded into Excel
- Excel VBA - need to copy paste conditional statement
- jdbc and statement.executeQuery() throws, statement.execute() doesn
- jdbc and statement.executeQuery() throws, statement.execute() doesn
- Excel VBA, is there an "IF" statement verifying toolbar exist
- help requested for using excel's built-in function in coding
- File formats ( Excel 2003 & Excel 97 ) ?
- Copy Excel Object Within Word to Excel
- pasting word table to Excel but needing to match excel
- Excel User Form and Active Excel Work Sheets Question
- excel question from a basic excel user
- Need Help In Deleting Duplicate Items in Excel a excel file
- Excel 97-03 file connecting to Excel 2007 file as data source
- Excel VBA Problem in excel 2000
- Convert code from excel 2000 vba to excel 2003 vba
- Forcing Excel 2003 to open file when Excel 2007 also installed
- Excel User Form and Active Excel Work Sheets Question
- how can paste excel data in autocad command line using excel macros
- Open Password protected Excel File via eSoftTools Excel Password Recovery
- If statement in a macro ?
- Mid statement
- if statement based on Shapes.Value
- Answering message box with visual basic statement
- on If statement in a macro ?
- Find Word from long statement