match dates in two columns get value from third

I have a data set on daily basis and another data set on weekly basis. i want 
to put the weekly data in daily data set by putting a formula which picks 
values from col c for relevant dates in daily set. example - let us say this 
is the data set

ColA        Col B      Col C         
jan1         jan1        8               
jan2         jan7        7
jan3
jan4 
jan5
jan6
jan7

I want the data from colC against dates in ColA as in ColD below. it should 
leave other cells blank (colA and ColB are dates format)
ColA        Col B      Col C         ColD
jan1         jan1        8               8
jan2         jan7        7          
jan3
jan4 
jan5
jan6 
jan7                                          7 

Kindly suggest a formula
0
Utf
12/17/2009 7:59:02 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1778 Views

Similar Articles

[PageSpeed] 55

Try this in D1:

=3DIF(ISNA(MATCH(A1,B:B,0)),"",VLOOKUP(A1,B:C,2,0))

Then copy down.

Hope this helps.

Pete

On Dec 17, 7:59=A0am, jaichander <jaichan...@discussions.microsoft.com>
wrote:
> I have a data set on daily basis and another data set on weekly basis. i =
want
> to put the weekly data in daily data set by putting a formula which picks
> values from col c for relevant dates in daily set. example - let us say t=
his
> is the data set
>
> ColA =A0 =A0 =A0 =A0Col B =A0 =A0 =A0Col C =A0 =A0 =A0 =A0
> jan1 =A0 =A0 =A0 =A0 jan1 =A0 =A0 =A0 =A08 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> jan2 =A0 =A0 =A0 =A0 jan7 =A0 =A0 =A0 =A07
> jan3
> jan4
> jan5
> jan6
> jan7
>
> I want the data from colC against dates in ColA as in ColD below. it shou=
ld
> leave other cells blank (colA and ColB are dates format)
> ColA =A0 =A0 =A0 =A0Col B =A0 =A0 =A0Col C =A0 =A0 =A0 =A0 ColD
> jan1 =A0 =A0 =A0 =A0 jan1 =A0 =A0 =A0 =A08 =A0 =A0 =A0 =A0 =A0 =A0 =A0 8
> jan2 =A0 =A0 =A0 =A0 jan7 =A0 =A0 =A0 =A07 =A0 =A0 =A0 =A0 =A0
> jan3
> jan4
> jan5
> jan6
> jan7 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A07
>
> Kindly suggest a formula

0
Pete_UK
12/17/2009 9:34:17 AM
Reply:

Similar Artilces:

Automatic transfer/download match up
From my paycheck, I have $250 withdrawn and placed into a separate bank account. When I download my transactions for this separate bank account, it downloads the $250 transaction and doesn't realize this is the same transaction as the transfer from my main bank account. My question: is there anyway I can force Money to always match this transaction? The way I do it now is to delete the transaction that I downloaded and keep the one that shows as an automatic transfer. Thanks for any ideas! Joey ...

Excel, how do I get ALT F C to work the same as ALT F Enter C ?
The above is an example, but it aplies to any menu. Before, if I pressed say, ALT F, the drop down menu would appear and I could press, say, C and get to the submenu. Now I have to press Enter, before the C, which is a bother. Thanks. On my Windows 2000 version 5.00.2195 with Excel '97 and Excel 2003 the Alt F C still works. What version software / Excel are you using? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www....

Trying to Get Subset of XML
I want to display a segment of an XML (see below) file in a datagrid. I want to select a specific <EventID> and get all of its children into a dataset so that I can bind a datagrid to those values. I am trying an expression as follows but I get an error "Value of type 'System.Xml.XmlNode' cannot be converted to 'System.Xml.XmlNodeList'." myNodes = xmldoc.ChildNodes("/Dataset/Events/[ShowName=Round 2]") What is the correct way to "get" that set of nodes and convert them to a dataset? ================== Portion of XML File ===============...

Payment For Invoice
In my business account, I have payments for invoices. When I download from my bank, they never match up. I try to match them manually, and it won't let me. I delete the downloaded transaction, and it shows back up on the next download. How can I work around this, and still show that my customer made a payment? C'mon... I know people from Microsoft are looking at this forum. Why haven't I received an answer yet?? This is a serious matter, and I'm 99.9% sure I'm not the only one who's been in this situation. JB2K "JB2K" <spam_catcher...@jb2k.c...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

How do I display two excel pages at the same time?
I want to have two excel pages displayed on my computer at the same time, but when I open both pages it uses the same master excel and when I toggle between the two it won't open both of them up. I'm using office 2003. Thanks for the help. -- snowtime ------------------------------------------------------------------------ snowtime's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25982 View this thread: http://www.excelforum.com/showthread.php?threadid=393499 Open both and then do windows>arrange and vertical (or any of the other choices, I usuall...

Made two versions; both run very slow . Any spare nitro around
I've been recording some macros to append together, but put the following together by writting it (well in my case it's a bitsa from all the good code on this site). However, it runs very slow. Every 3 letter code in cells down column("K") needs to be checked to see if the same is found down column("A"). As a check, I've just been putting a "1" in the same row in column("N"). Latter columns("K:N") can then be sorted on column("N"). Column("A") has 3 letter codes from row 3 to 1402 (but changes each run). Colu...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

How do I get excel files to open automatically from directories?
When I try to open excel files from the directory or from desktop I only get a blank worksheet not the file. I have to then go through File Open to get the file I want. There must be a way to have them open automatically. On Mon, 2 Jan 2006 21:22:01 -0800, Damian <Damian@discussions.microsoft.com> wrote: >When I try to open excel files from the directory or from desktop I only get >a blank worksheet not the file. I have to then go through File Open to get >the file I want. > >There must be a way to have them open automatically. Go to Tools -> Options -> Gen...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at the end.. is there a function/formula I can use to remove them if it ends in A or B? For example (my list): Accounting Sr Mgr B Accounts Payable Sr Mgr B Ambulatory Plng Sr Prog Dir A Need it to look like this: Accounting Sr Mgr Accounts Payable Sr Mgr Ambulatory Plng Sr Prog Dir Thanks! This will get rid of the A or B at the end along with the space before it. Assuming the value is in A5: =IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5) kvc wrote: > Hi, I have a list of titles a...

I can't get rid of 1 "Payment to Send' Reminder-M07
On my Home Page in the Reminder section, I have a "1 Payment to Send" listed there. But there is NO payment to send. I"ve opened up back-up copies as far back as May and the reminder is there, but there is no payment that needs sending. Does anybody have an idea how I can get rid of this Reminder? Just removing the Reminder from Home Page doesn't do it. When you put the Reminder back on the Home Page, the "Payment to Send" is still there. Clicking on the link just takes me to Bill Summary page. ...

Time Zone / Incorrect Dates Displayed to User
When a user in the Central Time zone closes an opportunity and someone in the Mountain Time zone views it, the Actual Close Date is 1 day before the date the person in the Central Time zone selected. For instance... If someone in the Central Time zone closes an opportunity and selects the Actual Close Date as 6/2/2007: -- Users in the Central Time zone see: 6/2/2007 -- But users in the Mountain Time zone see: 6/1/2007 This is a big issue when running monthly reports as it makes some opportunities show in the wrong month... and will put that revenue in the wrong month. It would make mo...

getting added to other peoples meetings in shared calendar
I opened shared calendars in my group by choosing their name and then the email messages went out giving them access to my calendar and requesting to view theirs. Ever since, I have been copied in as a "required attendee" on all meetings for everyone. I get the calendar items on my calendar and emails requesting Accept or Decline in my inbox. When others view view thru their outlook, they do not see me as a required attendee? What am I doing wrong? are you adding them as delegates or just giving them permission to view the calendar (by right clicking on the calendar fold...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

Can Work thread get a windows class and manipulate it?
Suppose that I create a work thread when my dialog box started, then in the thread I get the pointer to the dialog mfc class and call updateData method. Does that work? I tried, but failed. Is there any other way to do? thanks!! No, it won't work, don't even waste time trying. It is almost guaranteed to fail. You do not manipulate the windows owned by one thread from a different thread. What you do is PostMessage requests from the worker thread to the main UI thread, usually to the window that contains the controls. The fact that you are using UpdateData already says you are in t...

Cannot get my CImageList to display images?
Ok i am successfully diplaying images in my tree controls but not my list controls. I am using the following but not image is being displayed??? int nIndex = mListCtrl.InsertItem(LVIF_IMAGE | LVIF_TEXT,0,"hello", 0,0,7,0L); Where 7 is a valid index into my image list! Please help? That really doesn't help much. Since you got the tree control working I'm assuming that you are calling the list control's SetImageIist. Also for debugging purposes call GetImageCount on your image list to see how many images it loaded. Let me also point out that you can call this ...

need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is (How Many Days). Now I need to a assign a default value, or expression (not sure which way to go about this) that will take the date value for (Date) and add the value (How Many Days) I figured that the formula should read =sum([Date]+[How Many Days]) But that is not giving me any results, thanx for your help in advance =DateDiff("d", Date(), [How Many Days]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

adding date/time picker to inputbox control
Hi all,is there a way to add a date/time picker control to inputbox?...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

column value translation
I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...