I have a huge number of records (approx 140,000). These represent patients
attending a doctor's clinic. I am trying to harvest the data and obtain
figures for how many times each patient has attended the clinic. I could
use Countif but I do not want to add 5000 different patients as the lookup.
Break the data up into manageable chunks on separate sheets. Then select
one of the chunks, and use Data | Pivot Table. When you create your pivot
table, drag the name first to the row fields, and then again to the data
items....Matching QIF downloads to wrong amounts?!
When I download a QIF statement from my credit union, Microsoft Money
decides to "match" it to some completely unrelated transaction up to 2
months prior with a completely different payee and a completely different
AMOUNT! This stupid program is even taking reconciled transactions and
matching them up with downloaded ones, throwing the account out of balance
by moving these transactions onto a completely different statement with
different amounts! Then it complains about the starting balance being wrong,
which is a result of it's own stupidity!
How do you tell Microsoft Money th...Invoice Matching by receipt number
Some of our clients using Great Plains are always invoiced according to
shipment received, so they typically are matching against a shipment receipt
rather than the PO number. It would be great if in the left pane of the
invoice matching window you can choose to match by the receipt number
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-ba...filtering data with a variable
i wish to filter a list by using the contains option, but i want to
filter with contains a varible with its cell, then return the filtered
result, cell a only to another part of my sheet, can this be done.
Selection.AutoFilter Field:=1, Criteria1:="=*test*", Operator:=xlAnd
i want to replace test with a variable name, I have tried removing the
speech marks but cannot seem to get it right, any help greatly
Selection.AutoFilter Field:=1, Criteria1:="=*" & yourvariablenamehere & "*"
> i wis...How to move GP 7.5 Data to SBM 7.5
Is there a 'How To' for moving a company from GP 7.5 to SBM 7.5?
I did a backup and restore in SQL Manager, but I'm getting errors that I
think relate to differences in the account framework. Also, I get SOP not
setup properly message when I try to use Quotes, Orders Incovices in SBM.
Any help is much appreciated.
Thank you for posting in newsgroups!
There is not a migration tool or 'How To' for moving a company from
Great Plains 7.5 to SBM 7.5. There...entering data in forms
Access 2002; Users entering same entries in different rows. (example:
state)Would like to have the words inserted after the first few
letters/numbers are entered much like Excel. They would enter Kentucky the
first time and then the next rows when Ke or Ken is typed, Kentucky shows as
input. Is this possible? How?
A combo box with AutoExpand property set to Yes does that very thing,
Build a little - Test a little
> Access 2002; Users entering same entries in different rows. (example:
> state)Would like to have the words inserted after ...Data Import Utility
I need RMS data import utility.Can any1 guide me OR give me the utility.i
have thousands of Lookup codes in Excel & notepad format and i need to import
all of them in RMS database.I can use DTS in Sql Server but that is not
Thank & Regards
This is a multi-part message in MIME format.
I gave up on the SO Import Utility. Just didn't have the control we =
We use EMS SQL Import;
http://www.sql...Excel Sheet loses format when paste new data
Operating System: Mac OS X 10.5 (Leopard)
I have a sheet that I want to format with bold or font size and be able to paste data into it from csv files that are emailed to me. I want to end up with a sheet that is in the format I set the sheet up originally for. I hoped not to have to reformat each and every entry as I past new data in. Is there a way to set columns or rows to be a certain font size, bold, regular and have any data pasted in assume the new sheet's format that was set in the cell being pasted to?
Paste Special "Values" or use Data>...How to create 3 axes chart based on three series of data
...picking out matched data
I have 2 different sets of data each with 3 columns. I am going to place the
2 sets of data on Sheet 1 of a spreadsheet and Sheet 2 of a spreadsheet. The
data in a row across the 3 columns pertains to each other. This is true for
the data on each sheet. I need to take the data from sheet 1 and have it
match up with any entries that exist on sheet 2, and have it show these
matches on sheet 3. The problem is the data in sheet 1 and 2 are not exactly
the same. On sheet 1 the data is: Customer Name, Transaction#, Acct#. On
Sheet 2 the data is: Customer Name, Reference #, Acct#...Transfering data from one spreadsheet to another.
I have a spreadsheet that is created by a program I use at work. I run this
report weekly and there are hundreds of loads each week. The spreadsheet has
the info I need but not in the right format. I need to know if there is a
way to pull this info into another spreadsheet in the format I am looking for
without a bunch of copying and pasteing. He is an example of what the report
Load# Destination Charge Desc. Charges
12345 Columbus, OH Fuel $100
12345...Making pick list conditional on selection from previous pick list
I would like to know how to make a pick list (a "list" of names) conditional
on what was picked in the previous column pick list (e.g. of the 4 main areas
of a plant (pick list/ column A), there are anywhere from 3 to 5 sections
within each main area. I would like to format/program the second pick list
(coumn B) to only show the list of sections corresponding to the 'main area'
selected in column A. Then I need to do this for a third pick list (specific
equipment). If you have an example, it would be appreciated.
See Debra Dalgleish's Depend...Consolidating Data between worksheets that removes dulicates
I am interested in tracking lab values that my staff enter into a daily
worksheet. I would like to compile monthly percentages (% low, % in range, %
high) but sometimes it is necessary to have duplicate data on consecutive
worksheets for my staff's workflow. These duplicate values will cause my
percentages (and absolute numbers) to be incorrect if I just sum between the
worksheets. Does anyone have a solution?
...Retrieving Data From a Chart
I would like to build a formula that allows me to
go back 50 records in the database to locate and list how many records
since the last time the farthest back of the 3 evaluated cells last
A51 B51 C51 (The evaluated cells)
Therefore, the result would list 9, because from the cell values
3,7,2, #2 was the furtherest back in the last 50 records.
It last occurred 9 records ago.
~~ Message posted from http://www.ExcelTip.com/
~~ View and po...Importing data from XML
I just wont to import and update the data supplied by this xml source:
http://www.ecb.int/stats/exchange/eurofxref/html/usd.xml into a normal xls
sheet. It seems easy and it worked until recently but now it doesn't work.
Seems like a sort of schema error but I don't understand what is wrong.
How were you trying to get the data? QueryTable? Direct via XPath? A
bit more detail would be helpful.
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
[email on web site]
On Wed, 27 Jan 2010 06:29:02 -0800, Svilen
<Sv...Excel graphed trendline does not match derived equation
I have a scatter-point chart and I have been able to plot the linear
trendline through those scatter points. I then formatted the linear
trendline and got the equation for the line.
However, I noticed that the y-intercept (7.9...) of the actual line that was
graphed by Excel is not the same as the y-intercept (8.9573) for the equation
that Excel derived from the graphed line. The actual Excel graphed line and
the line the equation appear to be parallel to each other. As a result,
entering data into the equation supposedly derived from the graphed line will
not result in the line tha...Linearising Access Data
This may be obvious, but it has been puzzling me and I would have thought
that it comes up quite often�
I been sent a large access database containing a table of the form:
ID Date 1 2 3 4 ..etc
34 01/01/05 4565 73 56 876 �.
34 01/02/05 87 876 786 876 �.
And I want to convert into another Access table in the format:
ID Date Hour Value
34 01/01/05 1 4565
34 01/01/05 2 73
34 01/01/05 3 56
34 01/01/05 4 876
34 01/02/05 1 87
Any body got any suggestions on the quickest way of doing this bearing in
mind the original database has close to 90,000 entries so manipulating in
Excel...Chart Data Recovery
I have a dilemma I have a spreadsheet that only has a chart in it, that was
linked to another spreadsheet. The other spreadsheet has disappeared (HD
died and data was not recoverable and no backups were made). I can see that
the chart still has all of the correct data points in it, because I cna hover
the mouse over the line and it will tell me the values.
Is there a way to extract the data from the chart so I can rebuild my
original spreadsheet. The amount of data is 4 daily readings of 4 different
values from the Financial markets over thelast 4 years, so it is a lot of
I know that you can go to tools/options/edit to change the next cell after
you enter. I would like to go across the row instead of down the column.
Is there any way to make that selection just for a workbook, .
The change seems to be global until you change the preference again.
one way is to add this code in your sheet code
Private Sub Worksheet_Change(ByVal Target As Range)
play with it.
> I know that you can go to tools/options/edit to change the next cell after
> you enter. I would like to ...how to Match
Hi could you tellme i have some data in Sheet1 and sheet2 both sheet have
some same names and numbers how can i find out?
It really depends on how your data is laid out and what you want to compare.
If I'm trying to find out if a single cell's value is in column A of a different
sheet, I'd use a formula in an adjacent cell:
It'll return True if it's there, false if it's missing.
If I wanted to check the value of two cells in the same row against two columns
in the other sheet, I'd create a new column on both sheets...Error writing OLE data
I inserted some JPG pictures.
Now I can't save my file.
I get :
"An error (1441) occurred during the action Save.
There was an error reading or writing OLE data."
I'm using Visio 2003 SP2.
Can't reproduce the issue but clearly some or all pictures are containing an
try to save the .jpg picture one by one to determine if one of the pictures
is the culprit
try inserting in another format format such as .bmp, .png, .gif etc
try other office applications, if you don't experience a problem then
copy&...Pasting Data into a webpage text box
Simon move my posting at the codecage from excel to word so i'm reposting at
MS so it appears where I wanted the posting.
I'm working om a Webpage project where I'm copying from a word document to a
webpage with a box similar to the message box I'm presently typing in. The
webpage doesn't allow modifying the innertext or innerHTML. I saved the word
document to HTM file format. I then open the html file as a text file and
perform a readall. Then simply move it to the outter html
IE.myobject.outterhtml = fs.readall
This destroys the box and just displays th...Data Validation #22
I have data validation on two sheets of a single workbook. On one sheet the
dropdown list appears when the cell is selected. However, on the second sheet
the dropdown list does not appear. I have checked the "incell drop-down"
option for both sheets. Infact I have been using this workbook for a while
and it worked until I allowed others to work on it. The data validation has
not been deleted. How can I solve this problem? Thanks for your help.
If you delete the row that contains the cell with the problem data|validation,
then insert a new row in its place (and put back all the ...Pick cell from chart
I am developing an estimating spreadsheet that calculates labor hours. I have
built a separate 10 column and 6 row chart with info I want to pull into the
estimating spreadsheet. The colums are labeled as "Difficulty Factors" and
the rows are labeled as "Pipe Sizes". Each intersecting cell is a portion of
a labor hour.
The estimating sheet has 3 drop down lists. I want to build a IF AND
statement that will select a intersecting cell in the chart if certain
criteria from the drop down lists are true.
There are 60 different cells in the chart. Is it necessary to write ...Retrieving closest match
Can someone give me some guidance on how I can retrieve the closest value in
a column to a target number, whether it be larger or smaller than the target
number. VLOOKUP doesn't seem to work for what I am trying to accomplish.
The problem is analogous to a series of guesses for the number of jellybeans
in a jar, determining what is the closest guess to the actual number and
return the winner's name.
A1:A10 are names
B1:B10 are numeric values (that persons guess)
D1 = lookup value (some number)
Try this array formula** :