Data transfer problem

I am working on a project that deals with two sets of information.  M
first set of information includes:

A list of all the state abbreviations in the first column, the secon
column has prices.  

E.X.  AL (A1), AR (A2)  ....and so on all the way through the 5
states.
EX.  $1.14(B1), $1.20(B2)

ex--so AL has a price of $1.14, $AR has a price of 1.2 and so on...


My second set of information includes:
A list of state abbreviations, out of alphabetical order. 

I want to be able to merge these two sets of information around th
prices.  I do not want to go through the list and find each A
abbreviation and have to type in $1.14, 


I was wondering if there was a way to run a function that would sca
the second set of information for AL, since it is listed multipl
times, unlike the first set of information.  Once it finds AL, I wan
it to match it up with it's correct price aka $1.14

***(i can't sort the second set of information into alphabetical orde
because it needs to stay in the orignal order it came in

--
Message posted from http://www.ExcelForum.com

0
8/20/2004 8:24:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
650 Views

Similar Articles

[PageSpeed] 36

If your data is in a column, maybe you could use a helper column and drag a
formula down.

Say your state/price stuff is in A:B of sheet1.
Your states are in column A of Sheet2.

Then put this in a helper cell:
=vlookup(a1,sheet1!$a:$b,2,false)
And drag it down.



"borchesz <" wrote:
> 
> I am working on a project that deals with two sets of information.  My
> first set of information includes:
> 
> A list of all the state abbreviations in the first column, the second
> column has prices.
> 
> E.X.  AL (A1), AR (A2)  ....and so on all the way through the 50
> states.
> EX.  $1.14(B1), $1.20(B2)
> 
> ex--so AL has a price of $1.14, $AR has a price of 1.2 and so on...
> 
> My second set of information includes:
> A list of state abbreviations, out of alphabetical order.
> 
> I want to be able to merge these two sets of information around the
> prices.  I do not want to go through the list and find each AL
> abbreviation and have to type in $1.14,
> 
> I was wondering if there was a way to run a function that would scan
> the second set of information for AL, since it is listed multiple
> times, unlike the first set of information.  Once it finds AL, I want
> it to match it up with it's correct price aka $1.14
> 
> ***(i can't sort the second set of information into alphabetical order
> because it needs to stay in the orignal order it came in)
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/20/2004 10:39:25 PM
Reply:

Similar Artilces:

Outlook 2007 Delay Delivery Problem
I'm having a problem with the Delay Delivery option in Outlook 2007. All new messages default to a Delay Delivery of <Today> at 5:00 PM. How do I turn this feature off? As far as I know, I never turned it on in the first place, all my messages just started being delayed a few days ago! I have to uncheck this box in every new message, which is very annoying. Please help! Thanks, -- Carlos I am having the same problem, it keeps ticking the box for 5pm. Can anyone help?? Carlos wrote: Outlook 2007 Delay Delivery Problem 03-Dec-09 I am having a problem wi...

setting the status programmatically on a custom entity
Hello, I have the following code which closes a custom entity (5 being the value of the status reason associated with "Close" in the "Inactive" state). THis code is ran in a pre-update plugin. SetStateDynamicEntityRequest request = new SetStateDynamicEntityRequest(); request.Entity = new Moniker(); request.Entity.Name = "lund_cheevent"; request.Entity.Id = CHEEventID; request.State = "Inactive"; request.Status = 5; service.Execute(request); The problem is that I receive the following error: "State Code...

Problems with outgoing e-mail
I am having problems sending e-mails out using my Outlook. I am using a high speed connection in my hotel room. The IT person from the hotel said it's because of their security system (spoofing!?!). I am able to use my browser just fine, Yahoo mail works fine, I receive mail in Outlook fine, but can't send any out The error I get is "The SMTP server name you specified cannot be found". The "My server requires authentication" is checked. I'm afraid it is not my Outlook, and the hotel can't do anything else I appreciate anyone's help. On Wed, 19 May 2004...

delete data between subtotals
I would like to delete all data between my subtotals without the subtotals changing. I tried to collapse the whole sheet and to copy the then visible data (with paste values only). this did not work. is there any other way to get rid of data between subtotals? appreciate your help. please see attachement. Attachment filename: illustration_1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=446537 --- Message posted from http://www.ExcelForum.com/ Hi see Peo's response in your original thread -- Regards Frank Kabel Frankfurt, Germany >...

HELP! **Transfer orders to invoices error
I need some serious help. We just upgraded from GP 7.5 to GP 8.0 and now I am unable to transfer orders to invoices. I keep getting an error that says "Order ORD1555 has no line items to transfer to Invoice INV144408." Order# 1555 does have about 10 line items, which I can view on the screen. I did go in and run the "Check Links" function, but it hasn't done anything. This is our busiest time of year and a really bad time for something like this to happen. Can someone please give me some advice on how to fix this? if this is just one order, why not cancel...

Problem with comments
Have a strange problem with a computer I recently updated. I have been imaging computers in order to update several software items - one of which is Excel. The problem I have is with the users old excel documents which we transfer accross (Even those created with the same version). The workbooks contain many comments and these are unaffected - but when a user tries to add a new comment on an old sheet the comment will come up in very light grey and be impossible to read. If you create a new sheet in the same workbook comments are fine. How do you change comment colour for a sheet? It has st...

log-in problem
Hi All, I apparently shut off the power to my computer the other night before windows had finished logging off. Now when I go to start My Money program it gives me the following message: Money has detected that you did not close the file "My Money" before exiting. Money now needs to verify the information in your file. If your file is large this may take a few nimutes. Do you want to do this now? If you click no, you'll need to open a different Money file. YES NO Send Error Report If I click Yes or No, it keeps saying the e-mail address or...

Problems with "CommitFile" with EWF from Win XP embedded
Hello NG Maybe it's a little off-topic, but has anyone an idea, why the "EwfMgrCommitFile" Function does not seem to work? If I change a file, call the EwfMgrCommitFile function, I get a "successful" return code and everything seems to be OK, but if I restart the system, the file is old. Is there anything I forgot? Regards Eunet Eunet, Post your question to microsoft.public.windowsxp.embedded. If you don't get that NG from your news server use the msnews.microsoft.com server. I also suggest that you provide more information such as which mode of EWF you...

External data query using MAX/SUM
I'm trying to create an external data link using Microsoft Query. Ho do I write SQL in Microsoft query that will return a max date and su an amount field for the max date only? Current SQL is as follows: SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT) Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS) FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM CB_PORTFOLIO_DMN WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME This returns the sum of the exp...

jumping data
hi i have a form which shows customer details, and this has a subform where you can enter the date and notes about the customer. the subform is in a datasheet view. so everytime you want to add some notes about the customer, you enter the date and write the notes. there are many customers on the database, and we make quite a few notes for each customer. Now when i go into the customer and put in a note, then come out and go back into it, the data has moved up a few columns and goes into the wrong place, rather than stay in the same place where it was entered. also when...

how to insert chart for imported data in a report?
plz reply soon if you know Please use the large white space under the subject to enter a detailed question. It also helps to read some past threads that might pertain to your question (whatever it is). -- Duane Hookom Microsoft Access MVP "varsha from pilibhit" wrote: > plz reply soon if you know ...

Outlook client for CRM V3
We have a client who has users who want to make changes to the 'my contacts' local data group, so they sync their Outlook contacts with CRM. They all use the desktop client. But the save button is greyed out. They have a CRM role assigned with the Outlook sync permission and they have local admin rights. I've also checked they have write permissions to the C:\Documents and Settings\User.Name\Application Data\Microsoft\MSCRM folder and it's subfolders. What else could be disabling the save option? Becuase My Contacts is a default group, I don't think you can make c...

Problem refreshing subform
I'm having problems refreshing a local table in a subform. After selecting a new customer on the main form, my code is supposed to delete the records in the subform and inserts the current customer's records for display . The odd thing is that when I insert a breakpoint and step through the code line-by-line, the subform displays the new records just as I intended. But when I remove the break point, the subform remains on the previous customer. What is it about executing the code line-by-line that causes the subform to behave differently? "CDM" <CDM@discuss...

forecast report depending on quotes -> revenue change -> problem
hi, one of our subsidiary companies using crm 3.0 has commissioned a specific, complicated forecast report. this report is depending on quotes. now let us assume that a quote has been "migrated" to an order and is closed now. however from time to time they are seeing that the expected amount of order is lower than forecasted. now the forecast report is wrong as it is dependent on quote. how can this problem be handled without changing the forecast report (which is not wanted)? i.e. is it possible to somehow "fake" the system by entering negative amounts in an add...

Active Directory Problem
Hi Everyone, Hopefully you can help me out a bit here. Long story short, we had a failure last week that we were unable to recover Active Directory from. As such, we've had to rebuild AD from scratch - now the domain name and all user accounts are recreated and identical to the previous setup. However, I can no longer access CRM. I assume CRM 4.0 is authenticating on, not only AD membership, but based on an AD GUID of some sort - which I can't replicate. How can I get back in to re-load my users? Thanks, Craig Hi, Did u check if the users were part of CRM groups created ...

problem in SDI when I close application
Hi, I have made SDI application . But the program crashes in CDocument destructor when I close the application . In this statement: m_pDocTemplate->RemoveDocument(this); I havent changed generated code of document and view class of SDI application. I am unable to understand why it happens. Please help. Thanks, Dhaval Define "crash". There is no such thing. There are exceptions thrown due to access faults or similar errors, and they occur in some specific place in the code; there are assertion failures, which always indicate a line of code. Your description doe...

Moving Data Automaticaly
Can you help I have a table (part shown below) the Date and Day moves automaticaly to left at midnight - is it possible to get the rest of the data to move as well 25-Nov 26-Nov 27-Nov 28-Nov Sun Mon Tue Wed 07:00 07:00 TBA on off on off on off on off 1 1 1 1 1 1 1 1 1 1 1 1 1 1 -- JohnM Please explain meaning of "date and day MOVES to left" Are you using conditionally formatting to highlight the cell with the current date? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bli...

Test linked form for data
Hi Groupies How can I test to see if a linked form, that might not be visible, has data? My main form has a yes/no box that enables a button when it is set to yes. The button opens a linked form. When the user moves to the next record, I would like the main forms BeforeUpdate event to make sure that the yes/no box is not set at No while there is data in the linked form for that record. I have tried something like: If Me.Form.frmVehicles.RecordsetClone.RecordCount > 0 And Me.CompanyVehicle = False Then Cancel = True MsgBox "Please check vehicles", vbOKOnly Me.f...

Data label above columns??
Hi, I have some stacked column charts and I want the data labels to appear just above each column. Is that possible? I'm using excel 2003 version and the only options i can see for the label positions are "inside end", "center" and "inside base" - so at the moment I'm moving each data label manually and this is very time concuming! Thanks in advance for any advice you offer :) K I'm afraid it's not an option due to the confusion it would cause. If the label is above the stack it refers to, it could be sitting on top of another stack, th...

Copying decimal data to another column to integer 'on the fly'
Hi, I am working on Asset registers/Depreciation Schedules and at the star of the year I need to convert over my previous years spreadsheets (fo about 40 schools) to the new financial year. This means moving the accumulate depreciation at the end of the previous year to being the 'openin depreciation' for the new year. I therefore want to copy a column of figures to another column an paste to that column as values only BUT as INTEGERS. I am running Excel 2003 - 'Paste Only' can solve the 'values' part bu does not have conversion to an integer 'on the fly'. ...

labelflash problems
You'll need to copy the file gdiplus.dll l into your bin-directory.how do i do this Have you tried the support at labelflash? This .dll was included in Windows, have you looked on your disk? Help here http://msdn.microsoft.com/en-us/library/ms533798(VS.85).aspx -- Mary Sauer http://msauer.mvps.org/ "countryfella" <countryfella@discussions.microsoft.com> wrote in message news:0CA16383-2D50-4465-AF13-6F99962C607B@microsoft.com... > You'll need to copy the file gdiplus.dll l into your bin-directory.how do i > do this i have vista premium 32 bit os.i downl...

Problems Sending/Receiving Email
I am using Outlook 2000 SP-3. Suddenly I've been having problems sending/receiving email. When I click the send/receive icon nothing happens at all. Nothing gets sent or received and no dialog box or error message pops up. I've also tried sending/receiving through the Tools menu and nothing happens. It's as though I never tried to send the message at all. If I restart my computer it "works" again. But I'm having to restart my computer several times a day in order to be able to check my messages! Any ideas? "Elizabeth" <anonymous@discussi...

Importing Calender data from MS Project
Hi, Has anyone has any experience exporting their calender data from MS Project into the MS Outlook calender, can this be done ?? Thanks Pete ...

Date format problem #2
Dear All, Please suggest me the solution of my problem, as i m handling data for 60,000 in no.Some body in data entry has enter the date in a cell in gernal format e.g 20.03.2003 tht is 20th march 2003, i wnt to convert in the date fomat,like 3/20/03.. i tried ..but i failed to tht please suggest some alternative solution for tht..becoz it is not possible to do it mannually waiting for the response Thanks NISHANT Nishant Try using Data/Text to Columns. and assign the information as a date. Andy. "Nishant" <nishant.khare@wipro.com> wrote in message news:090201c37ce0$1b...

Moving a line of data when information is entered in a specific cell.
HELP NEEDED. I am trying to create a spreadsheet to keep track of problems that we encounter at our volunteer organization. It is pretty simple. I am having one problem. One of my fields is "completion information." What I need is when completion information is entered into the field, I want that line of information to move to an "archived sheet". In effect, so that I have a sheet with only active problems and when the completion information is entered it moves to the archived sheet. Anyone have any suggestions? -- opshmo -----------------------------------------------...