Sorting or aligning columns
I am trying to sort two columns so the data in both of them line up. For
example column a will have a, b, c, d, e, f and so on in it. Column B will
have a, c,e, f, but missing some of the letters that are in a. How do I line
up both of these columns so a lines up with a and b lines up with b and so
on. Where there is not a match in both it leave a blank in the second column.
Thank You For Your Help
I saved this from a previous post.
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
...Return Value in Column 2 Q
I have a table which contains 2 columns and 30 rows of Data
Col1 = Sales
Col2 = ManHours
I wish to "lookup" a value that is input into A1 (which will be a sales
value) and Return the appropriate ManHours associated with these sales. My
only problem is that my value in A1 will probably not match exactly what is
For example Col1 increments in 100's, so it will show sales of 9,000, 9,100,
Opposite these Sales values will be Manhours, say, 50, 55, 58 etc
Cell A1 might contain 9,040, thus the value I want returned is 55. Likewise
if A1 = 9,025 this should al...Formula to print a datum in color contingent on value.
I need a formula that will change the displayed color of a value based on
For example: under 200=yellow, over 200=red.
Also, can I add input from a third column?
For example: under 200 and Column D=No, then yellow.
under 200 and column D=yes, then green.
Try conditional formatting for the cells
Click on /scroll over cells you need to format
> I need a formula that will change the displayed color of a value based on
> For example: under 200=yellow, over 200=red.
> Also, can I add input from a thi...how to get data automatic moves to next column?
I am using a barcode scanner to read in barcode numbers into excel. But
everytime after I scan a barcode, excel moves to the cell in the next row.
What I want is moves down to the next column.
Anyone with information please help.
Thankyou very much!
Maybe there is way of setting up Excel and/or the barcode scanner such that
the values go to successive columns rather than rows. However, the following
formula may be a workaround for your current setup.
If the scanned data are going to, say A1, A2, A3, ......, and you want them
to go to A1, B1, C1 ...., enter the following formula...how to totalize only column value from certain accounting -> Currency format
For a sheet with invoices I need to totalize column values from a
different currency (cell format Accounting). How to perform? All cells
can have any currency USD or EUR.
Column A Column B:
Total USD 5,100.00
Total EUR 3,765.00
similar for euros
Gary''s Student - gsnu2007c
"AA Arens" wrote:
> For a sheet with invoices I need to totalize column values from a
> different currency (cell...How to replace column letter in refferences with a function using the old column letter?
How to replace column letter(s) (or column numbers) in refferences with a
result of a function using the old column letter(s) (or column numbers)?
I think you mean
where A1 holds the letter in this instance.
"Dmitry Kopnichev" <email@example.comDelete> wrote in message
> How to replace column letter(s) (or column numbers) in refferences with a
> result of a function using the old column letter(s) (or column numbers)?
Thanks for your reply.
How to change...comparing values
I would like to compare values in a excel spreadsheet. I want to have
it compare two columns that would be in scrambled order. For example:
If anything in column A equals anything in column B then place a
check in column C. Here is an example of what it could look like. I
appreciate any suggestions.
2 3 X
3 4 X
Put this formula in C1 and copy down
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)...Delete data from one column only
I have a table with 8 columns lets say (A-H) there is data in these columns
consisting of 128+ rows. All I need to do is delete the information in column
"A" only while keeping the data stored in columns ( B-H). I would like to do
this by adding a button on the form is possible.
First things first ... BACKUP!
Now, create a new query in which you update the value of that field/column
(by the way, Access is NOT a spreadsheet -- you'll want to stop thinking
about "columns A-H"!).
You can add a command button to your form that runs that update query.
I'm trying to find the average of bulletins used each Sunday. I'm keeping
track of how many I produce each Sunday and how many are left.
Week Bulletin Amt. Bulletins left
Week 1 100 34
Week 2 100 17
Week 3 110 20
I'm keeping track for the year (52 weeks)
What kind of formula would I use?
You would add another column titled "Bulletins Used", calculated with:
Then average column D, as in:
=average(d2:d4...How do I incorporate 2 columns into 1 column
My issue is that I have 2 columns of data, Column A and Column B. What
I would like to do is combine these 2 columns into 1 that would result
in something like this:
There are 1000+ entries in each column and I have tried creating a 3rd
column plugging into the cells the following, hoping to create the
ability to click and drag and fill the rest of the relative values:
However, when I click and drag, I am not able to keep the pattern
going. It always seems to revert to something like this after I try to
click and drag the fill box:...How do I reposition a column on an excel spreadsheet
I want to move column J with data over to become column B. How to do this?
Select Col J. Right click>Cut
Select ColB. Right click>Insert Cut Cells
> I want to move column J with data over to become column B. How to do this?
Jacob's was is a good way of doing it. Here is another. Highlight the column.
Hold down the Shift Key and Drag it to column B and Drop it. Note that when
you are holding the shift key and drag you get an I Beam to indicate where
you are about to drop the row. If you do not hold down the Shift key...Coping formulas to new workbook
Is there a way to copy formulas from one workbook and them paste it to
another workbook without it having a link in the formula to the previous
This is a formula I copied but all I need is the last part that said
=INVENTORY$d$177 not the link to another workbook
='C:\Documents and Settings\Desktop\SONIC REPORTS\IDEALS\aug ideals\[XDQ
IDEALS 2005 rev0805.xls]INVENTORY'!$D$177
I like to do this:
Select all the cells.
what: =...INDEX/MATCH Formula?
Hi, I need help with a formula. My data looks like this:
Column A Column B
And I need it to end up like this:
Column A Column B
So, in other words, the numbers in Column B need to end up in the same
row as their corresponding numbers in Column A. To do it manually
will take forever as the real data is thousands of rows long. Can
this be done with an INDEX/MATCH formula of some kind? I=92ve tried a
few different things on my own but I clearly don=92t know what I=92m
doing. Any help would be greatly appreciated.
Can you plea...Excel
I need to mix some columns like this:
Suppose A1:A3 = 1,2,3 and B1:B3 = a,b,c
How can I get C1:C6 = 1,a,2,b,3,c ?
Is it possible just with formulae (no macro) ?
Any help would be appreciated
Message posted from http://www.ExcelForum.com
try this in C1 =A1&","&A2
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"GML >" <<GML.16ot1c@...Cell Formats in formulas
I have a worksheet set up with formulas to automatically copy values from
another cell as it is changed. Is there a way to include the format of the
reference cell (font, color) when referencing it in a formula. Example: when
I change to a different symbol font in the referenced cell, the referencing
cells only change the value, not the font, so I don't end up with the correct
symbols in the referencing cell.
No, it's not possible to do that using formulas, only format formulas can do
are number formats and only using the text function
"C. Lewis"...Formula referencing other file show formula not result
Operating System: Mac OS X 10.5 (Leopard)
I have a formula in a cell that selects data from another spreadsheet. Both cells are text and often the formula shows in the receiving spreadsheet not the value. I have both cell formated as "text". In older versions of excel, you could never have an "=" sign start in cell without excel always defaulting that as a formula. Now in 2008 it does not work that way any more. <br><br>Along the same line I have a formula in a cell that selects data from another spreadsheet. S...cell displays formula instead of value #2
Excel 2003 SP2
I have a new blank workbook,
and on a new blank worksheet
I format columns A - D as Text.
A1 = [This_]
B1 = [is_]
C1 = [text.]
D1 = [=CONCATENATE(A1, B1, C1)]
D1 now displays [=CONCATENATE(A1 ,B1, C1)]
instead if the expected [This_is_Text.]
I have hours in this :((
D1 should have been formatted as "general". Format it, re-type formula?
"Jeff Higgins" <firstname.lastname@example.org> wrote in message
> Excel 2003 SP2
> I have a new blank workbook,
> a...Extract AutoFilter Column Values?
I have a column of road names, with various repititions of the same
name. I want to extract each individual road name to a separate column
(exactly the same as the Data>Filter>Autofilter command). The column
has about 500 records of 50 unique road names, and its these unique
road names that I want?? Please help, its greatly appreciated!!
dwayneh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21211
View this thread: http://www.excelforum.com/show...printing formula definitions
I've got some fairly elaborate formula definitions and I'd
like to be able to print the worksheet to show the
definition and not the output of the formula.
CTRL+` (that's the key to the left of "1" on a standard
keyboard) toggles the view between formulae and results.
You can then print to your heart's content!
>I've got some fairly elaborate formula definitions and
I'd like to be able to print the worksheet to show the
>definition and not the output of the formula.
Choose Tools/Option...How do I transpose multiple rows into columns?
I have data organized (in Office 2007) as such:
and so on
I want to tranpose it so it appears in columns as:
A B C
D E F
and so on.
Is there a way to transpose multiple rows so the information is stacked into
Thanks a million!
Copy > PasteSpecial > Transpose.........as many times as you need......can be
set to a macro if done frequently..
Vaya con Dios,
> I have data organized (in Office 2007) as such:
> and so on
> I want to tranpose it so...Return Corresponding Value Based on Comparing Two Sheets of Data
Can someone please help me find a formula (or two) for this example. If you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!
I'm trying to figure out a formula (or two) that will help me auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
"8888" row to return blank, since it does not exist in Sheet 1.
8765 ...Physical inventory formula problems
I am having a problem with the physical inventory feature. This is the example
I pull a Item Value List report with only supplier as the filter. The total
on this report is as follows QTY: -5 EXTENDED COST - $10 (these are
Now I go to the physical inventory and select NEW then ONLY ITEMS FROM
SELECTED SUPPLIERS and I choose the same supplier I choose on the ITEM VALUE
LIST I choose to update maching item and replace existing items. I am
importing 10 of the item that had a -5 when I started. Now when I calulate
now expected should read what my ITEM VALUE LIST started wi...Add a column to look up records webpage dialog
When in the account record and wanting to add "An Existing" contact, I click
on contacts and then I click the add existing contact. If I type Jim Smith,
I get 10, but I want to also display the state in the available records list,
how does one add that column. I tried making state recommended and required
on the contact record, but that does not do the trick. I remember doing that
years ago in 3.0, but I don't recall how to do that in 4.0?
Go to customise entities, choose Contact, go to "forms and views" and
customise the "<Contac...Formula to count no. of months between 2 dates
What is the formula to display the number of months between 2 dates?
Have a look at this site:
„Melissa” ezt írta:
> What is the formula to display the number of months between 2 dates?
On 23 Mar, 08:27, Melissa <Meli...@discussions.microsoft.com> wrote:
> What is the formula to display the number of months between 2 dates?
I forgot to mention I am using Excel 2000.
I us...view worksheet formulas
Is there a way to view only the worksheet formulas inside a worksheet, or
print them out? I know how to do this for pivot tables and pivot charts, but
what about a normal worksheet?
Microsoft MVP - Excel
"bill_morgan_3333" <email@example.com> wrote in
> Is there a way to view only the worksheet formulas inside a worksheet, or
> print t...