Combining Data #5
I have a question I am hoping someone can help me with. I have 2 worksheets
that I need to merge the data. Both have a common field that I key on, Case
ID. If the Case ID is found in both worksheets, the data will be pulled from
worksheet 2. If the Case ID is exists in worksheet 1 but not 2, the data is
pulled from worksheet 1. If the Case ID exists in worksheet 2 but not 1, the
data is pulled from worksheet 2.
Basically, I am exporting data from a system that only gives me current
data. That file may contain existing cases or new cases that the old file
does not. The old file ma...Function to mapping data
I am struggling for an excel data combination. Hope someone can help
Below col A and Col B is a pair while col C and col D is another pair,
originally col E, F and G are empty.
Then in col E, col F , col G, they copy Ax+Cx(eliminate duplicate),
Bx, Dx respectively.
A B C D E F G
C1 AA C1 111 C1 AA 111
C2 BB C3 222 C2 BB
C3 CC D1 333 C3 CC 222
D1 DD D2 444 D1 DD 333
D3 EE D3 523 D2 444
D4 SS D6 321 D3 EE 523
E4 345 D4 SS
From the above sample, col A & col C are copied to E1(eliminate
duplicate) and then col Bx copied to Fx(e..g c...IE or Excel caching data?
I am accessing a website that generates csv reports for many different
facilities I service. The csv files all have the same name and, subsequently,
the same URL. There is some kind of backend script that generates these files
and opens them in Excel for me automatically when I click the appropriate
The first time I click the link, it works fine. The second time, I click the
link that is associated with a different facility, but the data for the first
facility still shows up in the csv Excel file. I have played with IE's cache,
cookies, history, and advanced settings to no av...Office 2007
I have been working with Office 2007 beta. In prior versions I was able to
use the mouse to highlight the cells that were to be added to a chart. I
cannot highlight the cells that I need, but have to manually type in the
beginning and ending cell range.
...How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the
workbook. How do I do that?
There are limits to how you can combine data from different sheets. This
article describes them.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
"Donna" <Donna@discussions.microsoft.com> wrote in message
> I've created a graph that I want to...Data Validation
Is it possible to get list of unique values for data validation. In range a2
- a10 that I use as data validation list i have values x, y, z, x, x, ... In
a drop down I get letter x listed more than one time. Is it possible to make
it unique? I use Excel 2003.
Use a different source as your DV list that only has one of each entry.
If your list is as short as you say, then manually typing it would be the
If, in fact it is a much bigger list in reality, then use Advanced filter
Select your source range>Data>Advanced Filter>Unique valu...How can I make another version without all the data
I have build a report in excel. The workbook (nr. 1) contains multiple
tabs which get their data from a txt-file. One of these tabs gets its
data from another workbook (nr. 2), which is stored in the same folder
/ map. When I open the first workbook (nr. 1) it doesn't show data
which is stored in the other workbook (nr. 2), but when I open both
files at the same time the problem is solved. But now comes the real
problem. One of the tabs of workbook nr. 1 is saved as a pdf-file. This
tabs contains graph's and a summary of the data in the other tabs. Is
there a possibility to...MS Query No visible tables
I am trying to link Excel to an Access file using MS Query (Data/Get External
Data/New database Query/MS Access DAtabase. When I select the file, I get the
message "This data source contains no visible tables". But the Access file
has 5 tables. I have tried other Access files, some of which work OK, and
others not. What do I need to do to the Access file to make this work?
The tables are probably hidden. If Access is set up to show hidden
tables, you may not notice this, although the icon is slightly dimmer.
Open the database, right click on the table name, select ...Copying calender entries via code
Is there a way to copy entries from a calendar in the public folders to the
standard calendar via code ? A code example would be highly appreciated. If
this code can run under a timer then that would be even great.
Try this macro, which will copy all of the selected items in the active
Calendar to the default Calendar:
Dim objNS As Outlook.NameSpace
Dim objAppt As Outlook.AppointmentItem
Dim objCopiedAppt As Outlook.AppointmentItem
If ActiveExplorer.Selection.Count = 0 Then Exit Sub
Set objNS = Application...Using a Form to Archive Old Data
I have a table that contains some data, including a EmpDate field. I
have a query that will bring up the last 90 days worth of data, based
on that date field.
What I need is to be able to bring up that 90 day history, click on a
record, and hit a button that will move that record from the live
table to an archive table, and append an "archived date stamp" to the
record in the new table.
I can provide more data or information if necessary, but I don't even
know where to start with this one. I am assuming I will need to use
some sort of subform? Any help is appreci...Data Validation visibility
Data Validation Question
I am building a spreadsheet using data validation but I would like th
drop down boxes to stay visible at all times. I have researche
thoroughly and am not aware of any way to have the icon remain visibl
unless I switch to using combo boxes. I would assume this may be don
via VBA but am not clear on the code.
Thanks for the help.
Message posted from http://www.ExcelForum.com
AFAIK not possible
> Data Validation Question
> I am building a spreadsheet using data validation but I would like
>...Data Analysis in PWA 2007
Where can I find an exhaustive treatment of the details of
Data Analysis in PWA 2007?
A lot depends by what you mean by "exhaustive." Our Implementing and
Administering Microsoft Office Project 2007 book has a thorough chapter in
it about creating Data Analysis views and the OLAP cubes upon which they can
be based. We also provide some real world examples of useful Data Analysis
views that can be created for each family of OLAP cubes. Beyond this, the
book also includes a free download file that includes two Excel workbooks
that help the Project Serv...Compare/Validate and add new data
I am new to VBA. I need a help in the following :
I have 2 spreadsheets :
A) Workbook A :
C1, C2, C3, C4, C5
B) Workbook B
C1, C3, C5
Note the following :
1. Workbook A has more columns and rows (new rows).
2. Both workbooks will have data
I want to update Workbook B with following requirements:
1) Look for all existing rows and columns of Workbook B in Workbook A
and then update Workbook B with latest values for those already
existing and then add new rows with only required columns (C1, C3 & C5)
of new rows.
2) I also ...'xxxxx' data in SOP, POP files
GP Version 9, running on SQL 2000 w/Nolan Advanced SOP, Myridas Intersite,
Myridas Vendor Pricing, GP Manufacturing
We have 10 GP companies. Last week, 2 of the companies had a couple of Sales
Order Headers and Purchase Order headers where all of the character data was
'xxxx' and all of the numeric data was '999.
Some of the orders had detail lines with the same kind of data. The SOP
headers had SOP_TYPE = 7 and 8.
The data looks suspiciously like the data used for alignment or what appears
when you do a print preview in GP report writer.
Can anyone shed light on this even...pass through query
If a combobox is populated by a pass-through query, does it requery every
time the form is closed and reopened?
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
> If a combobox is populated by a pass-through query, does it requery every
> time the form is closed and reopened?
...validation rule in query?
I have a validation rule in the table to specifically enter one of 8 state
abbreviations. ="IA" Or " IL" Or "IN" Or " MI" Or "MN" Or "MO" Or "OH" Or
The Validation text that appears if not followed is Not a valid R3 State
I have built several queries based on this table and have a parameter query
for State that says Enter State Abbreviation. However, if one mis-types the
state, the validation text doesn't appear.
Is there something special I need to do??
The v...Adding a Formula as a data variable in a pivot
Is it possible to create a formula as data column in a pivot table?
For eamaple let's say I have the following Table:
Name Billed Charges Paid Charges
George 1000 500
Pilar 2000 750
Fred 1211 602
I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1
I know that one way to do this is to add columns to the raw data and
refresh the pivot, but I am wondering whether I can create a
calculated formula as...How to query
I have 2 fields in a table
and want to query result
JobNo Employee1 Employee2 Employee3
1 A B
2 X Y
3 H J K
How to create query?
On 24.03.2010 09:51, Nova wrote:
> I have 2 fields in a table
> JobNo Employee
> 1 A
> and want to query result
> JobNo Employee1 ...Link a Query
I want to link a query from a seperate database into my database. I found
how to link a table, but can't find any way to link queries. Can this be
On Wed, 19 Mar 2008 08:38:01 -0700, Nightshade
>I want to link a query from a seperate database into my database. I found
>how to link a table, but can't find any way to link queries. Can this be
Not to my knowledge. You can use File... Get External Data... Import to import
the query, or copy and paste th...VLookup/Data Validation
Last week I posted for help w/VLookUp, Data Validation or anything that
would populate a spreadsheet based on a criteria. I don't think I
explained correctly or gave enough information, so I'm desperately
trying this again - I need so much help.
I used http://www.contextures.com/excelfiles.html#DataVal -
ProductsList and did some editing to suit our needs.
I have a spreadsheet with 2 tabs.
Tab 2 is called EmployeeList, with a list our employees with their
name, start date, dept, roll (manager, clerk, etc.) and a list of
classes (Class I, Class II, Class II) needed to attend (all in...Autonumbering Query
I have 2 tables. Table 1 contains an indexed autonumber field named QID.
Table 2 contains the same name field - it is a number field. The two fields
are linked in the relationships as 1 to many and the form is working as
expected. What I am trying to do is insert a secondary autonumber field in
Table 2 which will restart at #1 everytime a new record is created in Table
1. Eg. Table 1 will have record 1, 2, 3....etc. I want Table 2 to have
record 1.1, 1.2, 1.3....etc. (the 1 coming from the QID field in Table 1).
The .1 etc. from the autonumber field in Table 2. Therefore ...Distilling a Query?
I have two tables: one that lists Clients and another that lists Client
The tables have a relationship between their respective Client fields.
Not every Client in the Client Table has a Project in the Client Projects
Table. Furthermore, many of the Clients who do have a Project have several
Projects in the Client Projects Table.
I have managed to create a query that lists only Clients who have a Project
in the Client Projects Table but each Client is listed as many times as it
My question is: how can I create a Query that lists all the Clients who have
a Proje...Capturing Query Parameters
I've got a report I've built for a customer that runs a query that has 2
parameters which requires the user to input a start date and an end date.
The query runs correctly based on the user's date selections. How do I
capture the dates selected as parameters and include them in the overall
report? This way the user knows which dates they selected for the results
they see. I'd like them displayed in the report header for example.
Thanks in advance for any suggestions.
The parameter names are available in the report.
For example, if you called the first paramet...GL Trial balance via sql query
Hi everybody -
I need to be able to create a trial balance via a sql query. I know GP
already has this report built in, but I need to make it via a query. Your
help is appreciated.
You can start creating your query with the following company tables, which
will provide you the following:
GL00100 - Account Master
GL20000 - Open Year Transaction Details
GL30000 - Historical Year transaction Details
GL00105 - Account number string, an alternative from conbining account
segments in GL00100
The following script is a very simple SQL query that is functionally TB
...Data in my pivot table changes to zeros
I have created a pivot table using a dynamic named range as my source.
The source data consists of 52,416 rows and 10 columns showing:
Everything appears as expected when I go through Excel's PivotTabl
wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I plac
in the data area of the PT) always come up as zeros. The PT seems t
have replaced all the correct data in each cell with a zero!
In addition, I have to manually change the fields to "-Sum of-..." a
opposed to the "-Count of-..." ...