Grouping variable length values
I have a field called contractnum which contains a variable-length
string from 8 -17
characters. My difficulty is that when I sort them, I would like to
group them based on on the section of digits just before the last.
The 6 entries below would be grouped as described. My problem is
differnet lengths in various entries.
The last digit in some entries may represent a change to the
original. For example,
123 and P123555 both represent original documents and the last digits
represent changes to those.
123 (=original number)
123 1 (a modification to the original 123)
123 2 (a second mod...Building Chart based on Column Value
I would like my chart to show rows where "COLUMN C" = "TEAM1". Then I
can change "TEAM1" to "TEAM2" and only chart rows where "COLUMN C" =
Does anyone know how to do this?
There is a sample here http://www.edferrero.com/ReportWorkbook.zip
(Might be a bit more than what you want, but the techniques are the same)
>I would like my chart to show rows where "COLUMN C" = "TEAM1". Then I
> can change "TEAM1" to "TEAM2" a...Find value in a column and insert rows above
The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y
Columns will always be U through X and will always be sorted in this order.
I need to find the first Y in each column and insert 2 rows above that row.
On the blank row above the first Y, I need to highlight in yellow and put
title in the first cell, such as New, Old, Existing, Deleted.
Any help would be greatly appreciated.
Thanks for your time,
If desired, send your file to my address below. I will only look if:
1. You send a copy of this ...Can you expand/collapse columns based on a reportitem/field values
I have a report that is driven by accounting periods. The columns are 1, 2,
3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown
is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want
to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So
the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible?
Thanks in advance
I created the following dataset:
SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3
UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr...SQL to get the lowest unique value from table
I'm looking for some SQL to get the lowest unique value in a table:
3 is the lowest unique value. I would need to return the ID.
"Lee" <email@example.com> wrote in message
> I'm looking for some SQL to get the lowest unique value in a table:
> ID BID
> 1 1
> 2 1
> 3 1
> 4 2
> 5 2
> 6 2
...Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that
keyboard shortcuts used in 97 do not exist in 2000.
Does anyone know of a KEYBOARD shortcut to modify, expand
and or create data ranges in the dialog box for entering
the range for the pivot. I have many data 'lists' which
are added to each month, and when I want to update the
corresponding pivot table, I used to simply hit "shift" +
"end" + "down arrow" to automatically go to the last
populated cell in the list. In 2000, I either have to
enter in the cell addy's manually or use the mouse...Column A to change font color if column B is not empty
I'd like to have column A of a sheet to automatically
turn to red font when a cell in column B is entered,ie,
the Cell A3 will change from red to black when B3 is
entered and B3 is a date.
Can it be done?
Thanks & Regards,
- select cells A3:A10
- goto 'Format - Conditional format'
- enter the formula
- choose a format
> I'd like to have column A of a sheet to automatically
> turn to red font when a cell in column B is entered,ie,
> the Cell ...Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below:
Column A Column B
I want to poplulate a column (for exampel column A on a new sheet),
where the values in column A will be repeated as many times as the
value in column B
Thus the first 24 rows will say Apples, the next 36 rows will Pears,
I need a formula that recogonizes that when it gets to row 25 that it
should no longer need to copy Apples, but then switch to pears.
This might sound like an unusual request but if I can grasp a way to
do this I can create the table I need and populate the rest...=?utf-8?B?V2FudCB0byByZXBsYWNlIHRoZSBzcGVjaWFsIGNoYXIgKOKAoikgd2hpbGUgcmVhZGluZyB4bWwgYnkgWG1sVGV4dFJlYWRlciBDbGFzcw==?=
i am using XmlTextReader class to read xml from http link ,now my problem is
that i want to replace the special characters(•) from xml before reading
its tag, coz it is giving the error in XmlTextReader reader.Read() method.
Please suggest any solutions to this.
Thanks in advance,
> i am using XmlTextReader class to read xml from http link ,now my problem is
> that i want to replace the special characters(•) from xml before reading
> its tag, coz it is giving the error in XmlTextReader reader.Read() method.
> Please suggest any solu...=?Utf-8?B?5LiL6LyJ5YWN6LK755qE5Y+N55eF5q+SIDMxNTk=?=
http://antiviruscn.blogspot.com/2010/01/blog-post...Calculating greatest number in a range
This is a little tricky to explain, but hopefully will make sense.
A series of data is produced that monitors concurrent users on a
system. This is returned every five minutes of every day.
This is on a work book which has a month for each tab.
Data typically comes in the following, simple form:
Wed Nov 26 10:50:01 GMT 2008 0
Wed Nov 26 10:55:00 GMT 2008 0
Wed Nov 26 11:00:00 GMT 2008 1
Wed Nov 26 11:05:00 GMT 2008 1
Wed Nov 26 11:10:00 GMT 2008 1
Wed Nov 26 11:15:00 GMT 2008 1
Wed Nov 26 11:20:00 GMT 2008 1
Essentially, I need a way of differentiating between indivi...Item Transaction Entry
We currently have two companies and would like to know the best way to
transfer inventory from one to the other.
Currently we use Transactions-->Inventory-->Item Transaction and enter a (-)
Qty to get the item out of Compay A, and do the same (+) Qty in Company B to
get the item into the Inventory of Company B.
We follow up with a report (using a batch name to mark these transactions)
to do Intracompany transactions to match the totals. Without purchasing a
3rd party software to handle advanced intercompany transactions, what is the
best practice at this point.
Ric...#N/B errro with vertical lookup
I have this formula:
J2 through K8 is:
Everything works just fine except when the value of B is 1, then I get
#N/B in the corresponding cell
Waht to do?
I'm "guessing" that VERT.LOOKUP is the equivalent of VLOOKUP in ENGLISH. If
so, then the arguments should be the same.
"Jean-Paul De Winter" <firstname.lastname@example.org> wrote in message
...non vba way to print non continuous ranges #2
Thanks Myrna, I didn't know about the hide columns facility.
It makes what I want to do a lot easier
DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223
View this thread: http://www.excelforum.com/showthread.php?threadid=27196
...converting checkboxes to a text value
I have an existing database that employs checkboxes. I'd like to create a
report that displays those boxes as "yes/no" text values, so that I can
export them to an .rtf format, and have them actually show up.
I'm a novice, so any help is greatly appreciated.
The purpose of this is to be able to email specific pages of the report.
I'm also open to other ideas for making this possible, if I'm going about
this the wrong way.
Thanks in advance.
> I have an existing database that employs checkboxes. I'd like to
> create a report that...combining duplicate row values?
XP Pro SP3
I have several rows of data as:
1 gl 24 36 cw1 north 1st
1 g2 24 36 cw2 north 1st
1 gl 24 36 cw1 north 1st
1 g2 24 36 cw2 north 1st
1 gl 24 36 cw1 north 1st
that I would like to combine (preferably into a separate worksheet) as:
3 gl 24 36 cw1 north 1st
2 g2 24 36 cw2 north 1st
based on rows 1, 3, and 5 and rows 2 and 4 having duplicate data. Is
there a way to do this?
1) Use the options in advanced filter to copy unique records to a location
in the same worksheet. (Advanced filter does not copy to alternate sheet).
2) Move the records to the required sh...Can I sort by Range Name?
I have a complicated spreadsheet that contains what I'll call "su
heads" and I need to sort by these. For example, I have this sort o
Owner Name Pets Pet Age
How could I sort by the Owner Name, and retain the informatio
organization under each name? Currently, all I get it is this result:
Owner Name Pets Pet Age
Randy...updating a reg_binary registry value
I can update a string in the registry. But Im in a situation where I have to
take a string, convert it to a byte, and update a reg_binary in the
registry, and so far my research and testing have been pretty frustrating.
What is the simplest/most effect way to convert the string to a byte, and
can I just use the SetValue to make the update, like I do with the string
registry data type?
Any help would be appreciated!.
...Go to certain sheets based on cell value
I have created a drop down list with the numbers 1,2,3. If the user
chooses 1 and presses enter, he/she is taken to 'AOA Only Analysis'
sheet, and so on for the other two. Any and all help on this is
Instead of a drop-down, why not put your three entries in three cells? They
can have anything in them, such as "AOA Only Analysis." Now set up
hyperlinks (right-click the cell, and select "hyperlink") and set up the
hyperlink with "Place in this document."
mvpearl omitthisword at verizo...How to update values in message boxes from within VBA?
I have a macro (in VB) as shown down below invoked by F11 - I created it
with record macro. It allows me to type in a name for an invoice and
the invoice number.
When I first press F11 it invokes two consecutive
message boxes to accept input to update two fields on the invoice.
The first message box comes up with the current value of the field from the
invoice template (Client Name).
I change the name in the this first message box and press OK on the message
The second message box comes up with the current value of the field from the
invoice templat...Asking Excel Solver to use binaries, but selects other values
I am trying to get a linear program solved using solver. The values that
Solver needs to enter are contrained as binary, but excel still try to find
values that are neither 0 or 1.
I assume that when you say it is selecting values that are neither 0 or 1,
they are values that are numerically close to 0 or 1 (such as 0.99998). If
this is the case, you may want to consider the following approach:
1. Have the Solver constrain your decision variables as binary.
2. Have another set of cells which uses the decision variable to produce a
result. When the underlying decision variabl...The instance name must be the same as computer name.
I keep getting this error when trying to install MS CRM 3.0 and I cannot get
around it! I only have a default instance of a SQL server and it is
registered as the same name as the server??
Please help very frustrating
>I keep getting this error when trying to install MS CRM 3.0 and I cannot
> around it! I only have a default instance of a SQL server and it is
> registered as the same name as the server??
CRM must be installed on an SQL Server that has been installed as a default
So if the computer name is SCHILLER the SQL-S...Cell Value vs Status Box Value
The cell value is not the same as the vlaue in the status box, e.g.,
status box shows 6147; cell value shows 64147 (format was with commas).
Message posted from http://www.ExcelForum.com/
awarner1 <<email@example.com>> wrote in
> The cell value is not the same as the vlaue in the status box, e.g.,
> status box shows 6147; cell value shows 64147 (format was with commas).
> Any suggestions/ideas?
What is the number format of the cell?
My email address has an extra @ (spell ...Subtotal of values in cells containing formula
for eg. I have column A with each cells displayed of a value, by is actually
generated by formula. How can I use SUBTOTAL in this case to obtain the
subtotal of value when I use my filter.
To clarify, my cell does not contain whole values like 2,500. It contains:
My SUBTOTAL could not work in the above instance.
Thanks to advise!! =)
change range to fit your needs, the above formula will bring the total
despite you using a filter or not
&g...Creating a variable range in Visual Basic
I've created a macro that will filter specific data from a sheet and copy
that filtered data to another sheet for a "watchlist". There are multiple
sheets to be filtered, and I'd like to be able to have the copied information
paste in at the end of the previous data on the "watchlist" sheet. I'm not
really good with Visual Basic, but can usually muddle my way through. Is
there code I can use to make the range variable, so the info will paste where
I need it to?
Add this near the beginning of your existing macro.
Dim lr As Long, sh2 As Worksh...