Help with Lookup #2

I have an excel worksheet that has approximately 20,000+ records. I
would like to classify these recorded based on two columns.  The first
column's name is "YB Description" and the other is "Sold Date". The YB
column mainly contains 4 digit numerical values, with approximately
300-400 records that are text, ie. "classified", "not verified",
"public use". The Sold Date column is pretty self-explanatory, all the
records are dates with the following formatting - M/D/Y. What I would
like to do is create a third column called YB/Sold that classifies the
records according to the the values in the YB column. I've been able to
create a simple lookup formula but I run into a problem when it comes
across the text values "Classified .... ". Is there a way I could
adjust the formula so that when it comes across theses three text
values it will look in the 2nd column, "Sold Date", and only use the
Year portion (M/D/Y) for the YB/Sold cell. I hope this all makes sense.

Tom.

0
7/29/2005 7:57:03 PM
excel 39879 articles. 2 followers. Follow

5 Replies
411 Views

Similar Articles

[PageSpeed] 55

The details of what you're asking are not clear to me, but does

=RIGHT(YEAR(VLOOKUP("Classified",A1:B10,2,0)),2)

help?

Alan Beban

tomwesnick@yahoo.com wrote:
> I have an excel worksheet that has approximately 20,000+ records. I
> would like to classify these recorded based on two columns.  The first
> column's name is "YB Description" and the other is "Sold Date". The YB
> column mainly contains 4 digit numerical values, with approximately
> 300-400 records that are text, ie. "classified", "not verified",
> "public use". The Sold Date column is pretty self-explanatory, all the
> records are dates with the following formatting - M/D/Y. What I would
> like to do is create a third column called YB/Sold that classifies the
> records according to the the values in the YB column. I've been able to
> create a simple lookup formula but I run into a problem when it comes
> across the text values "Classified .... ". Is there a way I could
> adjust the formula so that when it comes across theses three text
> values it will look in the 2nd column, "Sold Date", and only use the
> Year portion (M/D/Y) for the YB/Sold cell. I hope this all makes sense.
> 
> Tom.
> 
0
unavailable (273)
7/29/2005 8:18:36 PM
anything with that many records in Excel is a waste of time.

Start using DATABASES for your DATA and throw Excel out the Window.

0
aaron.kempf (776)
7/29/2005 9:06:00 PM
Sorry for not explain myself better. But I've figured out a solution to
my problem. I used a logic formula. Aaron the actual records I believe
sit in a Access database. I use excel just analyzing the data. Not to
familiar with Access. 

Tom

0
7/30/2005 5:01:55 PM
Tom

Sorry you ran across Aaron, he tends to be on a mission that believes Access 
is the answer to the universes issues.  What you are doing with Access as a 
data store and Excel as a reporting front-end makes perfect sense. Despite 
what you may have been told by Aaron.

The interesting point you make about 'Not to familiar with Access.' is a 
point Aaron refuses to accept can *ever* be the case.

Please feel free to ask any other questions you may have, despite the 
unwelcoming attitude of Aaron

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


<tomwesnick@yahoo.com> wrote in message 
news:1122742915.727315.274080@o13g2000cwo.googlegroups.com...
> Sorry for not explain myself better. But I've figured out a solution to
> my problem. I used a logic formula. Aaron the actual records I believe
> sit in a Access database. I use excel just analyzing the data. Not to
> familiar with Access.
>
> Tom
> 


0
8/1/2005 6:48:49 PM
listen fucknut

Excel is for babies

it doesnt scale; it can't be used by multiple programs.

Office Web Components friggin rock.

I would reccomend throwing Excel in the trashbin and starting to learn
a program that is a LOT more powerful.

Access queries are EASY.

Macros are EASY.

With queries and macros; you can do almost anything in the world.

Macros  in Access are all multiple choice; it's not like programming
hundreds of pages of Excel vba

good luck; back in '97 i was working for a company in a software
testing role; and they trained 30 of us in Access queries in about an
hour.  I wrote hundreds of queries that summer; it was the best of
times.

-Aaron

-aaron

0
aaron.kempf (776)
8/3/2005 10:26:52 PM
Reply:

Similar Artilces:

Originator <> #2
Does anyone know how to get rid of these? < > in the Internet Mail Service Queues. We get so many of these and we are not a relay. This has been confirmed via Microsoft. Any help would be great. Thanks. What you are seeing is NDR traffic. Non-Delivery reports are issued with a null originator to prevent mail-loops. See http://support.microsoft.com/default.aspx?scid=KB;EN-US;304897 -- Denis McDowell [MSFT] "Kevin" <anonymous@discussions.microsoft.com> wrote in message news:1855601c44a49$450a2fa0$a101280a@phx.gbl... > Does anyone know how to get rid of these?...

Default email client #2
I have set Outlook 2000 as the default email client after switching from Eudora to Outlook, but when I try to send email from the web, it still brings up Eudora. I have checked the programs tab in internet options and it is correct, as is the entry in registry key. This is very frustrating! look at the mailto entry in file associations - windows explorer's tools, folder options, file types dialog. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (...

need to make chart w/ confidence intervals and don't know how #2
...

3 series
Hi, I have three series of data and I would like to show one as a bar xhart and the two others as lines. This does not exist in the standard chart types nor in the customs ones. Any idea? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Create a line chart from your data. Then, right-click on the series that you want as a column, and choose Chart Type from the menu. Select the Column category, and one of the Column subtypes, click OK. Jon Peltier has instructions ...

Synchronizing 2 x Microsoft Outlook 2007
I have MS Outlook 2007 installed on two PCs. What is the recommended way to keep the Calendars from both MS outlooks in sync? Thanks. Locksley http://www.slipstick.com/outlook/sync.asp -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchan...

Excel help #8
Hi Please can someone help with this Excel error type message, using Windows 7 & Office 2000. Many thanks in advance. Regards, Problem Event Name: APPCRASH Application Name: EXCEL.EXE Application Version: 9.0.0.2719 Application Timestamp: 36f43422 Fault Module Name: EXCEL.EXE Fault Module Version: 9.0.0.2719 Fault Module Timestamp: 36f43422 Exception Code: c0000005 Exception Offset: 0004e154 OS Version: 6.1.7601.2.1.0.256.48 Locale ID: 2057 Additional Information 1: 9816 Additional Information 2: 981626252e0734cfbb061d9ef2627bd4 Additional Information 3: 35...

Consolidated Pivot Tables #2
Hi I have 4 workbooks with customer information in them. I bring them in to one file using consolidated pivot table. The problem is when I have the information - I cannot manipulate it ie: Drag around the columns and Rows - is this possible at all? Thanks Ann If you create a Pivot Table from multiple consolidation ranges, you won't get the same pivot table layout that you'd get from a single range. There's an example here: http://www.contextures.com/xlPivot08.html If possible, store your data in a single worksheet, with a column toidentify the customer, and you'l...

Help! #8
Hello! We have a number of users that are not able to open recurring apointments or able to delete them. The messages that they are receiving are as follows: "Can't open this item. This object could not be found." "Could not save item. Your changes could not be saved becuase you don't have permission to modify some or all of the items in this folder." Need your help! Ed booth@vsac.org .. Hi, What version of Outlook are you using? In 97 there is a known issue with recurring appointments: http://support.microsoft.com/support/kb/articles/Q228/5/09 .....

Help with Formula?
For example worksheet 1 Column A B C D & E have multiple Numbers ranging from 2001, 2010, 2017, 2025, 2027, 2036 & 2038. Worksheet 2, 3 & 4 Have the same. What i want to acheive is something to look at each column in each worksheet and count, how many times each number appears and bring back a result for each in a different worksheet. =COUNTIF('Sheet1'!$A$2:$A$200,2001) =COUNTIF('Sheet2'!$A$2:$A$200,2001) and so on if you put the list of numbers in the summary sheet then just copy down (assume they are in A2:A6 =COUNTIF('Sheet1'!$A$2:$A$200,A2...

Help and advice on windows Service
I have a Stored procedure that returns an Int. what I would like to do is run a windows service that has a 'icon' in the task bar that will show the interger value. Can anybody point me in the right direction of a tutorial or give me any examples of how to do this. Im running VS2008 vb.net thanks in advance "Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message news:5934DBB6-BA4B-445E-9EFC-4CE83324E6A3@microsoft.com... >I have a Stored procedure that returns an Int. what I would like to do is >run > a windows service that h...

Hyperlink open in new window #2
Yeah, so how do I create a hyperlink on my site that opens in a new window rather than taking visitors from my site? Can this be done? ...

Help with keeping formulas in place!
Hi Hi! I need some rookie help! My situation: I run a youth group, and need to keep track of several hundred kids. know Access is the solution to all my problems, but as an interi solution am trying to figure out a way to do it in excel. I have one workspace with several sheets. sheet 1 - list of all participants and their contact information sheet 2 - copy of all 3rd graders contacts sheet 3 - copy of all 4th graders contacts etc... Goal: I need, while maintain full usability of the first "control sheet (sorting, adding new enteries, updating existing etc..), to hav excel to ke...

Help with expression for Qtr
I’m not very good yet with writing expressions. How would I convert this expression to get the quarter? Month_1: Sum(IIf([TransDate] Between ([Forms]![frmVariance]![txtMo1]) And DateAdd("m",1,([Forms]![frmVariance]![txtMo1]))-1,[tblTrans_Mstr].[Labor_Cost],0)) Thanks for any help. Just change the interval argument of the DateAdd function from "m" to "q". Ken Sheridan Stafford, England AccessKay wrote: >I’m not very good yet with writing expressions. How would I convert this >expression to get the quarter? > >Month_1: Sum(IIf...

HELP: how to evaluate a filename when looking up another sheet?
I hope someone can help here. Suppose I have a document called 'sheet1.xls'. Now I have another document master.xls, from which I want to reference certain cells in sheet1.xls - but next week it will be sheet2.xls, then sheet3.xls and so on. I would like to set aside one cell in master.xls to point to sheet(n).xls - whichever is appropriate, so I can then apply a formula to the cells in master.xls, e.g. ='[sheet1.xls]TABLE1'!$A$3 ='[sheet1.xls]TABLE1'!$A$4 etc. Except that I don't want to point to sheet1.xls, I want to evaluate the string to point to a file b...

Where can I find help to make a crossword with Excel?
Re: Where can I find help to make a crossword with Excel? Try: http://www.microsoft.com/education/Crossword.aspx -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Pedrov" <Pedrov@discussions.microsoft.com> wrote in message news:98AA6C62-8DD6-4BDE-87DA-ECBF4F4AC065@microsoft.com... > ...

Quick Questions #2
What's the earliest version of Excel that has a built in Fourier Transform? Also, are moving averages also built in, or countour plotting? TIA. ...

Excel graph column and two lines. Help
I need to create a combo chart with three data points, One as acolumn and two lines Waynes, First, create a simple line chart containing all three series that you want to plot. After the line chart has been created, click once on the series (line) that you would like to change to a column. To change the series to a column, go to the standard toolbar and select Chart -> Chart Type -> Column -> OK. ---- Regards, John Mansfield http://www.pdbook.com "Waynes" wrote: > I need to create a combo chart with three data points, One as acolumn and two > lines ...

Help with Error "MIsmatch in expression"
I am getting the following error "mismatch in expression" when trying to run my update query, UPDATE tblCardinalPurchases INNER JOIN tblKrogerStores ON (tblCardinalPurchases.CUSTNAME = tblKrogerStores.CUSTNAME) AND (tblCardinalPurchases.CUST = tblKrogerStores.CUST) SET tblCardinalPurchases.STORE = [tblKrogerStores]![Store]; If you need additional information let me know, as I have only been working with access for 2 weeks. Thank You! ...

Details from 52 worksheets into one master sheet Help please
Hi Gord Dibben has given me this formula to take the details from 52 weekly sheets and give me a total sheet for the year and it works very well (Thanks Gord) If sheets are not named Week1 through Week2, you can insert a dummy sheet to the right of Totals sheet. Name it Start. Add another dummy sheet after last sheet. Name it End. Formula in Totals =Sum(Start:End!G25) Second method allows you to insert new sheets between Start and End sheets as your weeks progress. Gord Dibben Excel MVP What I would now like to do is make one sheet and have all the weekly totals listed so I can...

relay help #2
I appear to have external e-mails going threw my server smtp even though I've disabled relaying entirly How did you determine your server is being used as a relay? Is it a single Exchange server environment? -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <administrator@fedorowich.com> wrote in message news:%23LhIoNuxGHA.476@TK2MSFTNGP06.phx.gbl... >I appear to have external e-mails going threw my server smtp even though >I've disabled relaying entirly > On Wed, 23...

Help about the default Check Box state of Date Time Picker Control!!
Hi, In my Dialog-Based MFC App, I used a Date Time Picker Control with Show None and Allow Edit properties. You know that default state of Check Box of this DTP control is 'Checked'. I need that default state of DTP control is Un-Checked when the main dialog shows. But it seems a pity that there is not any proper member function of CDateTimeCtrl for me to set up this Un-Checked state in the OnInitDialog() function. Any good ideas? Thank you in advance. Dave >In my Dialog-Based MFC App, I used a Date Time Picker Control with Show None >and Allow Edit properties. >You k...

Looking up information in 2 different workbooks
hi, Im not sure how to use a formula for this problem. On worksheet 1 (daily log) i wish to input a contract number that is asigned to a reg number which will be inputted. i.e. reg order no yt02hjg ? the order number is located in either workbook 2 (cars) or workbook 3 (vans) so i want a formaula that will search both workbooks for the order number and input it. hope that makes sence. cheers Assuming that both workbooks contain your lookup tables in A1:B100, try... =IF(ISNA(VLOOKUP(C1,'[Workbook2.xls]Sheet1'!$A$1:$B$100,2,0)),VLOOKUP(C1, '...

Help..Getting data from another spreadsheet
Hi, I am tring to put together a stats book.I have the data imported through a query. However it's missing some important info so I went to another site a imported the other data into a differnt spreadsheet. Now, is there a way to import data from one sheet to another. SAy I have player A and I want to import his average. How do I do that? Thanks for the help... PS. Is there a way to sort data by the highest or lowest numeral entry You can try this formula. The formula is something like this =[88888888888]Sheet1!$C$9 888888888= the file name that holds the wanted information Cell na...

HELP: Cannot retrieve my Outlook/Outlook Express messages from my Yahoo email account
In the past, I have always been able to use my Yahoo email account to download my emails from my Outlook/Outlook Express POP server. In other words, I could always use Yahoo to access my Outlook email without Outlook at all. Just go into an Internet cafe anywhere in the world and I can access my Outlook emails from the Yahoo email site. But lately, the Yahoo email kept giving an error message: "POP server does not support LAST command. You may only retrieve POP mail from this server using "get all messages". The Yahoo email webpage doesn't seem to allow me to input this ...

Excel 2003 help doesn't work. I got both Excel 2003 and 2007 insta
I have both Excel 2003 and Excel 2007 installed. The Excel 2003 help doesn't work, but the Excel 2007 help works fine. When trying to open help in Excel 2003, the help window open, but it's all grey. ...