consolidating data by date

Sheet 1 is for Store 1.  In column A I have dates, in column B I have 
profits.  Sheet 2 is for Store 2 with the same data as Sheet 1.  However, 
there is not necessarily an entry on every date.  For example, Store 1 could 
have entries on 1/1, 1/2, 1/5.  Store 2 could have entries on 1/1, 1/3, 1/5.

How can I consolidate the data from these two sheets into one to look like 
this:

1/1  Store A profit   Store B profit
1/2  Store A profit
1/3                           Store B profit
1/5  Store A profit   Store B profit  

So basically, I would want to consolidate the dates in column A with no 
duplicates, and then have column B list Store A profits for that day (if any) 
and column C list Store B profits for that day (if any).  And then I can 
manually total Store A + B into column D.
0
Utf
12/18/2009 8:48:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1421 Views

Similar Articles

[PageSpeed] 37

      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"yowzers" <yowzers@discussions.microsoft.com> wrote in message 
news:8891C94F-BC1B-445D-878A-DF380AD717C7@microsoft.com...
> Sheet 1 is for Store 1.  In column A I have dates, in column B I have
> profits.  Sheet 2 is for Store 2 with the same data as Sheet 1.  However,
> there is not necessarily an entry on every date.  For example, Store 1 
> could
> have entries on 1/1, 1/2, 1/5.  Store 2 could have entries on 1/1, 1/3, 
> 1/5.
>
> How can I consolidate the data from these two sheets into one to look like
> this:
>
> 1/1  Store A profit   Store B profit
> 1/2  Store A profit
> 1/3                           Store B profit
> 1/5  Store A profit   Store B profit
>
> So basically, I would want to consolidate the dates in column A with no
> duplicates, and then have column B list Store A profits for that day (if 
> any)
> and column C list Store B profits for that day (if any).  And then I can
> manually total Store A + B into column D. 

0
Don
12/18/2009 9:13:07 PM
Excel 2007 PivotTable
Consolidate stores on multiple tabs.
http://www.mediafire.com/file/wcjgiznmztw/12_26_09.xlsx
0
Herbert
12/26/2009 4:25:50 PM
On Dec 18, 12:48=A0pm, yowzers <yowz...@discussions.microsoft.com>
wrote:
> Sheet 1 is for Store 1. =A0In column A I have dates, in column B I have
> profits. =A0Sheet 2 is for Store 2 with the same data as Sheet 1. =A0Howe=
ver,
> there is not necessarily an entry on every date. =A0For example, Store 1 =
could
> have entries on 1/1, 1/2, 1/5. =A0Store 2 could have entries on 1/1, 1/3,=
 1/5.
>
> How can I consolidate the data from these two sheets into one to look lik=
e
> this:
>
> 1/1 =A0Store A profit =A0 Store B profit
> 1/2 =A0Store A profit
> 1/3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Store B profit
> 1/5 =A0Store A profit =A0 Store B profit =A0

Here=92s one way with Excel 2003.

In my example, the dates are Excel dates in 2009, and they start in A1
of Sheet1 and Sheet 2.

In Sheet3, column A is a helper column. It can be hidden later to
avoid clutter. The desired results will be in columns B:D.


In Sheet3!A1 put
   =3DIF(AND(
      ISERROR(
        MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet1!$A:$A,0)),
      ISERROR(
        MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet2!$A:$A,0))),
     0,1)

In Sheet3!A2 put
   =3DIF(AND(
      ISERROR(
        MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet1!$A:$A,0)),
      ISERROR(
        MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet2!$A:$A,0))),
    0,MAX($A$1:$A1)+1)
and copy downward.

In Sheet3!B1 put
   =3DIF(ROW()>MAX(A:A),"",
     DATEVALUE("1/1/2009")+MATCH(ROW(),A:A,0)-1)
and copy downward.

In Sheet3!C1 put
   =3DIF(B1=3D"","",
      IF(ISERROR(VLOOKUP(B1,Sheet1!A:B,2,FALSE)),"",
         VLOOKUP(B1,Sheet1!A:B,2,FALSE)))
and copy downward.

In Sheet3!D1 put
   =3DIF(B1=3D"","",
      IF(ISERROR(VLOOKUP(B1,Sheet2!A:B,2,FALSE)),"",
         VLOOKUP(B1,Sheet2!A:B,2,FALSE)))
and copy downward.

Modify to suit.
0
zvkmpw
12/28/2009 1:00:41 AM
Reply:

Similar Artilces:

Transfer data only from Excel spreadsheet into Word or text document
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) I have to move the data only that is in an Excel spreadsheet into a Word or text document so that I can edit the info into a paragraph style for a book. What's the easiest way to do this? You can also paste the tabel into Word and then choose from the top menu: Table &amp;gt; Convert &amp;gt; Convert table to text. You can choose the options of you liking in the following dialoge. <br><br>Good luck. > You can also paste the tabel into Word and then choose from the top menu: Table &amp;gt; Convert &...

How to retrive/store data in UTF-16 format
Hi all , I am getting some problem related to storing & retriving UTF-16 data. Actually i have a data in uncode character & i want to use that in my program in VC. I am considering that the data is in UTF-16.& in that format i want to store. Thanks, Sajal The question is ill-formed. "I am getting some problems" is not a meaningful description. Unless we know *what;* these "problems" are, there is no hope of answering your question. The problems will arise once you go above character value 65535, where you now have to use "surrogates". Surrogates ...

MS crm 4.0 email router LOAD DATA Problem
Unable to load users,queue and forward mailboxes when clicking on the load data button on the email router. An error appear that it can find http://crm/ORG. I have followed all configuration steps but still unable to get it to work. Anyone has any idea? have you entered the correct url to Microsoft crm server? /Aamir ...

Formulas that recognise data from two worksheets
I want to use data that is entered in one worksheet to calculate an equation in another worksheet. But i want worksheet one data to be recognized and correlated with data in worksheet two before the calculation happens. For example if i enter a type of material, the colour and the amount of metres cut, i want the formula in worksheet two to workout the total metres left by recognising the type of material and the colour and correlating it with the material and colour and total amount of material that has been entered in worksheet two. Reasons is there may be the same material but ...

How do I fit a graph to data?
I work at a non-profit agency. We receive funds on an annual basis for our work, which we distribute under contract to other agencies. The full details aren't important, the main point is the rate of "burn" through the money. We use different mechanisms to ensure that it lasts throughout the fiscal year, such as breaking it up into smaller chucks, etc. I watch the burn rate to see how it is going. I record this on a daily basis in an Excel spreadsheet. Due to business practices with the other agencies, the burn rate isn't a simple linear graph with a negative slo...

merge data from one workbook to another
I am trying to import data from one workbook to another, but need to do two things in this process. I need to compare data and only insert what is missing. IE in one column of one workbook there is a group of client ID's. IN the other workbook there is a sales associate for that client. I would like to merge these so that excel automatically reads the client id and inserts a new column with the sales associate for that id. I'd use the VLOOKUP function. "beck" wrote: > I am trying to import data from one workbook to another, but need to do two > things in this ...

Report combining data from 3 tables with a crosstab?
Ok, so, I'm self-taught in Access so sometimes efficient design withmore complex databases evades me. I've spent a while searching thegroups and trying to apply different situations to this one, but Istill haven't found a solution. I did previously ask a questionregarding the first two tables, but of course once I was able to applya solution to that, the person I'm doing this for wants something morecomplex. I tried to build on what I did, but all my attempts havegiven unexpected results. I'm missing something.Anyway, I have 3 tables (from a database I did not create):t...

get data from main form and update subform
Hi to Everyone I have a mainform with two subform. Mainform has a startdate data. I would like to get this startdate data to my subform which have also a similar date data. Every subfrom and form are different table. Thanks for any idea -- Message posted via http://www.accessmonster.com Hi, this is untested air code: On the subform's After Insert event put code something like this: Me.TheDateControlName = Parent.TheDateControlName Jeanette Cunningham "jfeketet via AccessMonster.com" <u40504@uwe> wrote in message news:8032bac88630d@uwe... > Hi to Everyone &...

DATA VALIDATION #6
data in column B is of 9 or 10 or 11 or 12 digit. In every case it contains alphabet "P" whereas others are digit. e.g. 10P060509 125P695847 how can i check this through data validation? HI aditya, > data in column B is of 9 or 10 or 11 or 12 digit. > In every case it contains alphabet "P" whereas others are digit. > e.g. 10P060509 > 125P695847 > > how can i check this through data validation? > Use a custom validation formula like; =AND(LEN(B1)>8,LEN(B1)<13,ISNUMBER(FIND("P",B1)),NOT(ISERROR(VALUE(IF(LEN(B1)=FIND(&...

Change Data from Now() to Date() Format
I've been entering data ( for some time ) in one of my field in a database with the Now() format - eg: 11/26/2007 9:52:0a AM I've now discovered that I should have used the standard date() format - eg: 11/26/2007 This would facilitate my record retrieval in the future. Is there a way to do an update query that will change to format from Now() to Date() without affecting the the contents of the data . Thanks for all your help. Take is easy on the cross posting here. It is considered VERY RUDE to just throw out your question to a GAZILLION newsgroups with the thought that this...

HOW CAN I VIEW MY OPENED DATA. IT'S OPENED BUT DOESN'T APPEAR
I was workign with my data when it went off from the screan. Main while it shows that it's opened. I tried to print it and it printed but still can't see it on the screan. Please I need help. How were you viewing it? In a table or query or report? "yaw" wrote: > I was workign with my data when it went off from the screan. Main while it > shows that it's opened. I tried to print it and it printed but still can't > see it on the screan. Please I need help. ...

adding lines to added data
I have added data to an existing worksheet but when printed the row lines and column border lines do not show. How do I add the lines to the added border to match the existing lines? ...

Count by Date
I am trying to create a query that will capture how long a particular record has been in a queue. I have another query called Status of Item. This query has the following Fields: ItemId, Date Entered, and Queue. Now I need my new query to group the Queues and count the # of ItemId's by how many days from when it was entered. I need it to look something like this: Queue <10 10-20 21-30 31-40 >40 Grand Total Blue 4 6 0 0 2 12 Purple 8 2 ...

Working out difference between 2 shown dates
I currently have a column which contains a date and time data (20/06/2008 16:30) time / date occured and a second column containing similar data (24/06/2008 15:25) time/date reported. I would like to have a third column showing the difference in either hours/mins or mins between the time/date an incident occured and the time/date the incident was reported.Is there a formula i could use? Can anyone help? Thanks in advance Just subtract the two cells and custom format the results as [h]:mm or just [mm] -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* &q...

Data Type Problem
Hi Experts In VC++, the maximum range for data type is 10 bytes which is the range for long double __int64 has 8 bytes range. Is there any data type which has range more than 10 bytes? Is there any data type which has range 16 bytes If not supported, then can i create my own data type which range should be more than 10 bytes? if yes then please reply thanks If you need to play with huge sections of numbers, you can always use a unsigned char array, and use bit shifting backwards and forwards for calculation (complex). Otherwise, for numbers there are other string ba...

Combining data from many files
I have approx 1,500 similar spreadsheets that summarize financial activity. Each file summmarizes the activity for 1 account. How can I pull certain data from each spreadsheet into one main spreadsheet without having to enter 1,500 cell references or lookups. (i.e. can the file name in a cell reference be a variable?) There are 1,500 different values for the file name in this formula "=VLOOKUP(B5,'[00204198.xls]DVR EXCESS CHECK'! $C$1:$H$63,6,FALSE)" Can I change "00204198.xls" to be a variable that is set to a list of account numbers? Indirect won'...

GP Detail Trial Balance returns no data
I ran the Account Modifier and successfully changed the COA structure. All our testing is going well so far except the GL Detail Trial Balance report. We changed the report parameters but we don't get any data. There's no errors, just a blank report. Any ideas? Thanks -- Jim Here's some more info. If I specify an account range, I get a blank report. If I run it for all accounts, I get data. -- Jim "Jim" wrote: > I ran the Account Modifier and successfully changed the COA structure. All > our testing is going well so far except t...

Automatically Copy Data from one text box to another
Experts, How would you go about dynamically copying data from an unbound text box to a bound text box on the same form. Thanks, ...

Lookup value based on nearest date
I have a table of rates which occur on certain dates (one a month). It has two fields Rates.Date and Rates.Value I then another table where each record has a date in it, and I need to find the Value from Rates at the matching date, OR at the nearest date if there is not an exact match. It is possible that the date I need to lookup is before or after the range of dates in my Rates table. Is there a nice elegant way of doing this? Carol I believe both of those fieldnames ("Date" and "Value") are reserved words in Access. I'm certain about the first and confident ...

How do i show the date a report or query was last run?
I am using Access 2007. I have several reports and queries which are run on a fairly regular basis. What I would like to be able to do is show the date the report was last run. Is there any way I can show this on a form which would show the name of the report or query and the date it was last run. Thank you for any help you can give me. Sean Bishop The easiest way I know is to have whatever triggers your report to also append a record to a table and to use DLokup on form to display Max dated record. -- Build a little, test a little. "Sean Bishop" wrote: ...

Labeling dates
I am looking to have Excel recognize that a date is between a certain period and put a month label in the next cell. Basically, it would see in A1 that the date is between January 1 and January 31 and would put the label "January" in B1. Is there any way to do this so that it would recognize all of the months? Also, can I make it recognize that the month is January (or whatever) regardless of the year following the month and day? Thanks for any tips. ...

Is there a date picker for Access 2003 ?
I don't find it from Tools. Thanks. Jeff Conrad lists a number of alternatives at http://www.accessmvp.com/JConrad/accessjunkie/calendars.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "JJ" <JJ@discussions.microsoft.com> wrote in message news:B7D9ECC2-CC77-4577-97D6-1805D36C0C9B@microsoft.com... >I don't find it from Tools. > > Thanks. ...

Hide Rows and Columns based on Date Input
Dates are listed in Column A, starting from Cell A3, and data appears in the corresponding rows (across the page). Not every cell has data in for the corresponding date. For example: The date in cell A5 may have data in Cell B5, E5 & G5. I would like to enter a date (using a command button and input box), this will then hide all rows, except for the row which relates to the date that has been entered. At the same time I would like it hide all columns that do not have data in for the given date. .... carrying on from example above. If the user entered the date that appeared in cell A5...

dialog to dialog data
I have a dialog based app that shows another dialog. Within that second dialog I have some data that I want to put in a text control of the first dialog. I tried saving a pointer to the first dialog and using a function in it to copy the string to the member variable and then UpdateData but the CString= operator code crashes indicating the first dialog member variable isn't valid. I tried a ::SendMessage to the 'safe hwnd' of the first dialog but that doesn't seem to ever get handled. What's the technique (I can figure out the details if I know the approach) for ...

Link excel and word in mail merge without losing your data source?
I'm trying to make labels and envelopes but I want Excel as my data source to be linked in the Word mail merge in case of changes to the data source and then it will automatically change in the mail merge. I can do that part but when I ready to share this information with other people within the organization, it states it can't find the data source that I've used. Please help, if would save me a lot of time. Thanks. Is the Excel file on your PC drive or on a network drive? -- Sincerely, Michael Colvin "angie" wrote: > I'm trying to make labels and enve...