Compare cells (advanced)
I need to find a smart solution for comparing cells in an Excel file.
To make a long story short, we usually receive excel sheets with
thousands of rows. From a certain data there we make our own
extraction from our database and put that in the same sheet, next to
the other data.
Then we need to compare some data, to make sure that it is correct.
Lets say that we are comparing names. The problem is that VERY often
the two cells containing the names are not _exactly_ the same. There
might for example be one letter misspelled in one of the cells e.g.
(Mike Brown/Mike Bruwn) or in one ...Changing system administrator policies
I needed to install Microsoft WSE 3.0 Runtime so I could use Family Tree
Maker 2010. During installation of WSE I kept getting the message:
"Windows Installer. The system administrator has set policies to prevent
I could not find anything in the Control Panel that would let me (as
administartor) change policies. I could get no answer from Microsoft
Support and gave up on them. After a lot of searching I found someone
who said they solved the problem by turning off User Account Control. I
did this and it worked.
Does anyone know a better way...System.OutOfMemoryException on Large Files)
I get XXX when I run the following code (if and only if the outcome of
transformation is beyond 20 MB)
.Transform(XmlDocument, Nothing, MyWriter, Nothing)
If I run this in .NET 2.0 Beta 2, it gets on and it's much faster as well.
If run the same on MSXML 4.0, it's works and it's much faster too.
Is this a known issue in .NET 1.1 ? Is there a way around it? or do I have
to switch to MS XML 4.0 for now?
Mohd Banat wrote:
> I get XXX when I run the ...automatic sort #2
I have a spreadsheet with columns A:G and unlimited rows.
Is there any way to automatically sort the whole sheet in alphabetical
order by, let say column G?
The problem is that users of this sheet enter data in the rows and I
need it to be sorted as I mentioned above but many of them don't know
much Excel and can't do the sort from the menu so I was thinking maybe
some kind of macro could do it but I don't know how to write it.
I should mention that I already have macro on that sheet that sets up
printing area, also with ctrl+D it deletes the current row and moves
everything up so ...Trouble with "Time and Billing Database" template
Hello, I'm new to using online templates but need a new database that this
template is set up well to use, but I'm not a expert in Access, I understand
the basics, but not the more complicated code. As I said, I like the general
setup of this template but have noticed a couple of bugs that I'm not sure
how to fix. (There's a Dlookup on the timecard report that isn't functioning
correctly and the timecard report doesn't print correctly). I just started
getting into this (to set it up for my business) and noticed these bugs.
Does anyone have any advice? Should I ...Saving files automatically
I have a macro to save a number of different versions of my file for
different budget holders. When this is run, it recognises that the file
already exists and prompts to allow it to be overwritten. Is there some VBA I
can add to accept the prompt automatically?
application.displayalerts = false
'your code to save
application.displayalerts = true
Richard Burton wrote:
> I have a macro to save a number of different versions of my file for
> different budget holders. When this is run, it recognises that the file
&g...Keep adding to one cell to carry a total in another
I want to be able to use one cell to keep adding new numbers to that will
accumulate the total in another cell.
Ex: I use A1 as the cell to input a new number that will carry a total in A2
any help would be appreciated
Try this in the sheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target <> Range("A1") Then
Application.EnableEvents = False
Range("A2").Value = Range("A1").Value + Range("A2").Value
Application.Enabl...Run-time error 91 Object variable 
I've been using this code for some time without any problems, suddenly I get
this run time error - Object variable or With block variable not set.
Appreciate your help!
Public Sub KryShtBld()
Dim MatchSht, sht As Object
For Each sht In Sheets
If sht.Range("D1") = Range("bladsoek").Value Then
Set MatchSht = sht
sht.Activate <------------- Error here
I doubt it ever worked as "sht.Activate" is outside your For each sht loop.
Su...Why isn't data that is automatically generated in my form showing up in table?
Ok, I've been messing with this problem all day (for those of you who have
been following me, I'm so sorry!). But I think I finally figured out the root
of my problem. I have created a form that allows users to enter in an
employee's clock number using a combo box and it automatically generates the
employee's name in a text box next to it by pulling information from the
Employee Data table. This data shows up fine in the form view and the
datasheet view. However, when I open the table up that this data is being
saved to (Weekly Data), the data that has been automatically...Combining 2 cells
I have a range of scores for a test in cells A10 thru A100.
In Cell A1 I have a the formula =COUNTA(A10:A100) that returns the
number or n of scores. In cell A2 I have the formula
=SUM(A10:A100)/A1 which returns the mean for the range. In cell A3 I
have the formula =STDEV(A10:A100) which returns the standard deviation
for the range. Cells A2 and A3 are formatted as numbers with 2
decimal places (ex., 1.55).
I've combined cells A2 and A3 into another cell with the formula =A2&
" / " & A3 to be able to view the mean/standard deviation in the same
cell (ex., 9.5 / 1.5)...Storing time formats in strings
Cell "A1" is formatted to accept time data in the format of:
If I run the following code:
Dim s as String
s = Range("A1").Value
The message box will display a long floating point number
instead of the time that is displayed in the cell. Is there
a way to store the time that is displayed in a cell into a
Use the Text property instead of the Value property...
s = Range("A1").Text
Rick (MVP - Excel)
"Robert Crandal" <email@example.com...create multiple rows using a variable in a cell as source
Is there a way, to create multiple rows using a variable
in a cell as source to indicate the number replicated
In Excel, I have a name and address and another field to
indicate how many "rows" I need for that record. All the
quantity of replications will be different, that is to
say, I may need one of this one, but 4 of that one, and
10 of yet another. How can I use the quantity variable
to replicate the row in the worksheet?
Ideally, I want to use a source worksheet to create a new
worksheet or better, a workbook, of the replicated rows.
Any resources or...Can I split 1 cell into 2 cells in Excel?
I am working in a spreadsheet where I frequently use the filter and sort
functions. I want to split one cell into two in one column, I don't want to
use two columns and merge the rest of the cells as this upsets the
Highlight the required cells you want to split and DATA > TEXT TO COLUMNS >
NEXT then enter the parameter that you want to split by (colon, space, etc)
> I am working in a spreadsheet where I frequently use the filter and sort
> functions. I want to split one cell into two in one column...Make Great Plains WA state Workers Comp. setup work automatically
Washington State has two requirements for Worker's compensation to work
1. Certain employees, especially many agricultural employees are paid by
2. Worker's compensation is processed by hours worked.
Because Great Plains only tracks one unit of measure per Pay Code, such as
number of hours for an hourly code or number of pieces for a piece work code,
the software does not allow you to calculate workers compensation correctly
when you are using piece work pay codes. There is no way to set up a piece
work pay code to track pieces harvested and also tra...Extract data fm cell
Hi...I have a cell that contains a first and last name [Bill Gates]. I need
to extract the first and last into two cells [Bill] [Gates]. I'm stuck, I'm
guessing the Left, Right or Mid function is involved but I'm not smart enough
to figure it out...
Help is greatly appreciated!
Have a great Friday,
If you want to use formulas, you could use something like:
(for the first name)
for the second name.
If you have a whole column of names to fix, you may want to insert a column to
the ri...Auto Filter based on cell value / drop down list
I have a spreadsheet that holds quality data for multiple products
indexed on the date and time of production. Data from this is charte
so I can see chronological trends either for all products or selecte
ones using the autofilter. This currently works by placing the graph
over the data table and setting them so they dont move with the cell
What I want to do is put the graphs on a separate sheet in the workboo
with a drop down list to select the product to chart.
How can I get the auto filter to operate based on the value selected i
the other sheet? Does this require macros or VB ...flash the cell contents in excel to draw attention
how do I do it with excel from office 2003. I am using XP prof
Continuous flashing will slow down your Excel application
considerably not to mention become quite annoying. But if
your stubborn, here's a link to some code from MVP Robert
Rosenberg that I found in the google archives:
>how do I do it with excel from office 2003. I am using
...Count a cell in month format
The above columns are C and D, and I am trying to force A1 to give me a
count when column C is in the month of March, and D = "reg". I have a macro
that filters C and D by the selected macro, so the month value will need to
change according to the macro. I tried COUNT, and other versions, but it
does...No automatic selection of telephone number
When i go to workplace, and start a new activity, i select a phonecall
The defalt remains on outgoing call, and i select a contact to call
I checked, this (exisiting)contact has a work phone number.
Why do i, after selecting my contact to call, have to fill in a phone
number manually, when this number is saved to this contact in CRM already? I
expected the CRM system to asdd this contacts number automaticcaly, as soon
as i selected the contact...
Am i missing somehting obvious?
...Insert Table in Preformatted Chart
I have a chart that I have developed that I use for reporting.
The chart is updated automatically with the necessary information.
I would like to insert unrelated data on the chart in the form of a small
table with a link to the information in the workbook as an object on top of
a corner of the chart.
Is there some way for me to do that?
Create two charts for the data and resize the small chart
and position it inside the other.
Select both charts. Select one, Hold ctr and select other.
The on the Drawing Toolbar menu select Group.
>-----Original ...Automatic updates of a Publisher file
Is there a way to automatically populate data in a Publisher file?
My company has a catalog/price list that is based in Publisher. We issue
this catalog to 3 different regions that all have different pricing. If there
is a change to a description, a price, etc., I must manually update all three
versions of the file. Is there a way that I can create a single Excel file
with three different prices, that would automatically feed into Publisher?
That way, I would only have to make the change in one place, but the changes
would be seen in the Publisher document.
...Automatic Capital letter entry
Is it possible to format a cell so that each time a lower case letter i
typed into that cell a upper case letter will appear in a cell
Example, entering Canadian postal codes, type in v2e 1m1 and have i
change to V2E 1M1 automatically. I would perfer not to use macros as
have never used them before
Message posted from http://www.ExcelForum.com
To do it in the same cell, you'll have to use an Event macro. It's easy:
Put this in the worksheet code module (right-click the worksheet tab and
choose View Code, then paste this into the VBE module that opens):
Private Sub Workshe...Cell Format 02-17-10
I have Excel 2007 and I would like to lock the formatting of a cell (wrap and
alignment) so that you can cut and past cell content and the format would
remain. Is this possible?
> I have Excel 2007 and I would like to lock the formatting of a cell (wrap and
> alignment) so that you can cut and past cell content and the format would
> remain. Is this possible?
Isn't that the default behavior?
Can you provide some simple steps of what you want to do and the result
you want to achieve?
When I cut text from an InfoPath form (xml doc) and paste into an Excel cell,...Automatic opening of OS folder
I have my OS in F:partition.whenever I boot the sytem the
foldeF;windows/system unnecessarily opens and remain in the task bar
as minimised.I manually close it every time.Why it happen.What is the
> I have my OS in F:partition.whenever I boot the sytem the
> foldeF;windows/system unnecessarily opens and remain in the task bar
> as minimised.I manually close it every time.Why it happen.What is the
Do you mean that the Windows\System32 folder opens? There is a twist in
this age old quirk that yours opens minimized, see if this helps:
...Comment becomes uneditable, cell flashes
I have a worksheet I've been using for years, which has started misbehaving.
I created a complex formula and wanted to document it with a cell comment. I
can insert a comment correctly the first time, but if I try to edit or
resize the comment, it disappears and the cell flashes repeatedly.
- Why is this happening?
- How can I work around this, and be able to edit/resize the comment?
- Should I be worried about possible corruption?
Perhaps you might want to try the "Open and repair" option (in xl2003)
File > Save as .. (give it a back-up na...