Formula to identify a keyword in all cells of a column
I have a spreadsheet with about 5,000 rows andjust one column - column A. I
would like to record a formula in column b where for every cell in column A
that contains the word "web" somewhere in the description (the description
could have up to 10 words), it will place the number "1" in column B. Can
this be done?
I assume that it is some sort of if/then statement, but when I try
=IF(A2="*web*",1,0) with the wild card "*" it does not seem to work. Any
"HTC" <HTC@discu...export report to Excel results in 2 rows of column headers
Here's my issue. I have to export data to excel for uploading to a vendor's
system. The vendor required that several of the columns have IDENTICAL
headings (don't ask - i know that is stupid), and one of the column headings
has to contain a % symbol at the end (also stupid).
I can't do this with exporting a query, so I tried using a report. If I make
text boxes for the desired duplicate column headings, it works just fine. The
problem is that on row 2 of the exported data, the actual column headings
from the query are also exported. Can this be avoided?
Sample...Find duplicates in a column
I have 6000 emails addresses in a col and wish to know if there are any
How can I do this please.
I have Excel 2002.
Assuming your emails are in A1 to A6000, in B1 enter
copy down B1 all the way to B6000
Apply a conditional format of red pattern on cells B1 to B6000 where Cell is
greater than 1 and all duplicates will be shown in red.
> I have 6000 emails addresses in a col and wish to know if there are any
> How can I do this please.
> I have Excel 2002.
...row and col ref are all num how can I get it back to letters?
in excel 2007 my column and row references are all numbers all of a sudden.
How can I get the column references back to letters?
To turn R1C1 referencing on or off in Excel 2007:
1. Click the Office Button.
2. Click Excel Options.
3. Switch to the Formulas tab.
4. Click the R1C1 reference style check box. A filled box will enable R1C1,
a cleared box will disable it.
5. Click OK.
> in excel 2007 my column and row references are all numbers all of a sudden.
> How can I get the column references back to letters?
...Change list data
Is there a way of quickly turning some data in this format:
Into this format:
Thanks in advance anyone - David
Fill down the columns C and D as far as you need:
C = INDIRECT("B" & ROW()*2-1)
D = INDIRECT("B" & ROW()*2)
Thanks Bucky - David
...Changing Registered Users Name
The present user of the Microsoft Office 2000 is not the
same as the installed user. When ever we send an EXCEL
spreadsheet to OUTLOOK, the sender is always the
installed user, not the current user. How do we change
the installed user name to the present user name?
This might work:
Tools>Options>General Tab>User name
>The present user of the Microsoft Office 2000 is not the
>same as the installed user. When ever we send an EXCEL
>spreadsheet to OUTLOOK, the sender is always the
>installed user, not the current use...Vista Printer On Network Changes IP Address
I have two ethernet hardwired network printers on my home net.
Why do the IP adresses change and what can I do to to prevent this or at
least find an easy way to fix it when it does.
This is not an user freindly ergonomic situation MS.
You have to read the printer instruction manual.
Older printers in particular that have built in ethernet ports assign their
own address within a specified range, which may not coincide with what your
router wants to supply. You may be allowed to choose a number within the
range but you cannot change the range. You also need to find out how the
...creating chart with 2 stacked columns and 2 lines
cost revenue % cost revenue %
A 2,608 3,377 77.22% 2,615 3,331 78.49%
B 2,549 3,596 70.90% 3,069 3,793 80.90%
C 2,307 2,912 79.20% 2,365 2,769 85.40%
D 1,916 2,180 87.90% 2,028 2,201 92.12%
E 1,659 2,413 68.76% 1,441 2,159 66.76%
From the above table, I wanna have cost and revenue on the same stacked
column but separate stack for year 2005 and 2004 and have % as a line
(should be 2 stacked 5 stacked columns with 2 lines). I've tried it by
setting the cost as a secondary axis and set it as a stacked column but the
problem is it needs to use the same...Column-line chart on two axes
How do I do this in Excel 2007. There used to be a custom chart in 2003 – is
there still in 2007?
Make a column chart
Select one of the data series in the chart; change Chart Type to Line
Then format it to have secondary axis
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"Mary Ann" <MaryAnn@discussions.microsoft.com> wrote in message
> How do I do this in Excel 2007. There used to be a custom chart in 2003 -
> there still in 2007?
Thank...SBS 2003 Backup
again a quicki one:
I have scheduled backup set to be written on external HDD, with
replace option so I have Small Business Server Backup.bkf, Small
Business Server Backup (2).bkf... and so on until external hard is
filled up, and then it shoul replace oldest .bkf...
I exchanged external hard (they are held in safe) with other, cleared
it, and now I have Small Business Server Backup.bkf growing on daily
basis, (some 15 GB per day), so it is in append mode?????
is there a optionswitch to add to command line to set it again to
Are you using the SBS 2003 Ba...change field name in table in closed data base
is possible to change field name in (table) in closed data base from current
change fieldname (playername) to (PlayerN)
i hope you can solve this problem with easy code
...How do I change a cell so I can have the data it contains in seve.
Hello! I have a spreadsheet in Excel and have a lot of data in one of the
columns. How can I make each row taller, and change format so I can have
several rows of data in one cell?
If by >>make each row taller, and change format so I can have several
rows of data in one cell? you mean "make all the data in a single row
visible", then right-click the cell in question, select Format Cells,
the Alignment tab, and check the Wrap Text box.
...VBA to merge worksheets (only populated rows)
I have some basic code that we use to pull together data from several
worksheets within a single workbook. It should only pull in rows that have
data in column A and ignore the two rows used for headings from each of the
other sheet to avoid them being repeated all the way down this new page when
they are coppied across.
For the most part it works, however we noticed it seems to randomly add some
extra rows to the bottom of the consolidated list. Originally these were
blank but we now find that they are actually some of the rows from the very
first worksheet to be pulled in where n...Changing the background color of my Word Screen
I don't know how messed this up, but the background of my Word Screen used to
be purple. Somehow now it is gray. I tried looking under "options" to see
how I could change this back, but there is nothing under options that would
accomplish this? Can anyone help? Also, if it helps..I am working in page
Sorry, I posted in the wrong place
> I don't know how messed this up, but the background of my Word Screen used to
> be purple. Somehow now it is gray. I tried looking under "options" to see
> how I could chan...Outlook profile Exchange Server name changed
...I change a % in a cell all values in the columns dont adjust 2 new
You're not giving us much to help you but will try.
Press F9, if that works , goto >Tools>Options>Calculation Tab>Set
calculation to Automatic not Manual.
If that don't work, show us your formula, maybe a bit of your layout
anything will help to help you.
"neety" <firstname.lastname@example.org> wrote in message
...Comparing two columns #4
I have 2 documents with 2 columns each. One document is old and one i
They both have columns named "NAME" and "ID"
The documents don't have exactly the same names in them, but mostly do
What I need to do is to see if an ID is missing for a NAME in the ne
document, and if it is missing and if the old document has this ID fo
the given NAME, then put the ID from the old file into the new file.
have to check for all NAMES in the new document.
theintern's Profile: h...Compare column of number
Is there a way to compare numbers in a column and return the numbers once.
a b c d e f
300 300 310 312 400 411
Source data assumed running in A1 down
In B1: =IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))
Copy B1 down to cover the max expected extent of source data, say down to
Then in C1:
Copy C1 across to cover the max expected number of unique items, ...Create two Data validation lists that are linked(Both change toget
I would like to create two or more validation lists of the same range of
data. Currently, I have a number of graphs on different worksheets that can
be updated by choosing from a drop down list (data validation list).
Basically, that list is referenced in a lookup function which gathers
information into a range that then updates the graphs. Right now, I have to
click back and forth between many sheets in order to to change the drop down
menu. It would be ideal that if each worksheet with a graph had the exact
same drop down menu that when updated, updates each of the o...search column for duplicate value during data entry
I'm entering data one row at a time. When I enter a value in column A, I need
to know if that value has been entered (in Column A) before. Column A needs
to be unique values.
What is the best way to verify uniqueness, as I enter a new value?
Should I use a module or can a formula check for?
I'd prefer to have a Warning box pop up indicating "Value already entered in
this column", so I'm assuming a module (VB?) is the way to go.
If you have any suggestions, I'd be happy to read.
The easiest way to do this is to use a conditional format. ...Compare 1 cell to column of cells returning adjacent cells info?
I want to compare the value in one cell to another column of cells in another
work sheet and once the matching value is found I wan to return a different
cells value that is on the same line as the matching cell. Example Sheet 1
value is 34 in B6, so I want it to search Sheet 3 colum J for the value 34
which is found on row 351. When a match is found the value returned needs to
be from colum O so this would be O351. Does anybody know how to setup this
You are describing the VLOOKUP() function
"Mr. Fine"...How to add 100 years to a column of dates
I imported a faxed spreadsheet into OmniPage and directed it to output
the data into an Excel spreadsheet.
It did a good job, except that the dates in the date column were all
changed into early twentieth century dates.
Instead of adding 100 years to each date one-by-one, can I add 100 years
to the entire column with one command?
In a staging column, enter formula:
When finished, Copy that column and Paste Special Values over original
"Albert Einstein" <email@example.com> a �crit dans le message...Changing angle of trend line.
How do I set a trend line not as an average through my points but as a slope
that I have determined?
Draw it yourself. If you know the endpoints, put their coordinates into a
Copy this range, select the chart, use Paste Special to add the data as a
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
"smrtie17" <firstname.lastname@example.org> wrote in message
> How do I set a trend line not as a...Adding alternate columns
I work on a schedule where I record ticket count and ticket sales for
each show, listed chronologically.
Column A = week ending date
Column B = # Tickets sold for April 5 show
Column C = Ticket Revenue for April 5 show
Column D = # Tickets sold for April 9 show
Column E = Ticket Revenue for April 9 show
Column F = # Tickets sold for April 12 show
Column G = Ticket Revenue for April 12 show
Column H = Total # Tickets sold for all shows
Column I = Total Ticket Revenue for all shows
In the Totals columns, the Total Tickets formula is "+B4+D4+F4", and
the Total Revenue f...allow user changing of default value
Hi, i have a field in a form which needs to have a default value,
however, i would like the end user to be able to change this value
easily, how can this be done? is it a simple case of setting the field
to find its default value from a table which the user can input their
desired default values? Or is some vba required?
On Jun 6, 9:33 am, salmanjavah...@yahoo.co.uk wrote:
> Hi, i have a field in a form which needs to have a default value,
> however, i would like the end user to be able to change this value
> easily, how can this be done? is it a simple case of setting the ...