match up column A with column B
I have a column of numbers in A, and a column of numbers in B. There
are 180 numbers in A (A1:A180) and 130 numbers in B (B1:B130). I need
Excel to find which numbers match up between columns, e.g look down
column A and find all the matching numbers in column B, then sort the
columns so the matched numbers are next to each other (so I can see
which numbers don't match between columns). (I have sorted the numbers
from lowest to highest down the column, so they are in numerical
I have no idea how to do this.
Thanks for any assistance you can provide. :)
...Unable to hide columns #2
I have a spreadsheet where on one of the sheets, if I try to hide more than
7 columns at one time, I receive the following message:
'cannot shift objects off sheet'
Can anyone help on this.
I have experienced the same thing.. Always when I try to hide rows.
As to my knowledge there is no solution. It is a bug in excel tha
happens when you have hidden rows, and delete an visible row just abov
the hidden part.
If the hidden part has formatted areas, it wont let you hide that par
I have searched everywhere, but there is no solution except for copyin
the dat...Moving rows to columns #2
I have an Excel sheet from which I need to automaticaly generate a new
worktab (is that the correct translation to English?) which does some format
editing. In the current sheet I have the data lay-out as follow:
X A1 A2 A3 A4 A5
X B1 B2 B3 B4 B5
Y C1 C2 C3 C4 C5
Y D1 D2 D3 D4 D5
I need to move that to:
X A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 etc.
Y C1 C2 C3 C4 C5 D1 D2 D3 D4 D5 etc.
Is there any way to do this? I need to keep the old format for human
editing, the sheet with the new format should be generated automaticaly
(perhaps using a macro?)?
Thank y...upsize table and form to SQL Server with Identity Column
I have a very simple form that I use to enter data for a table. I upsized
the table to SQL Server and linked it. In some underlying code, when
I try to update a column in the table that the form is using as the source
I get the familiar, "need to use dbSeeChanges in OpenRecordSet for linked
SQL server table...."
My problem is that I think the form does the OpenRecordSet in the background
when the form is opened, so I don't know how to set the dbSeeChanges option.
This is what I'm doing:
strSQL = "update employee set employee_save_date = now() where emp_id =
Me.emp_...Counting a column based on the results of 2 other columns
I am trying to count the number of occurences of something in one column depending on a couple of other columns.
Available Proof of ID
In this case I would want to total the number of entries in the Proof of ID column (both the 1's and 0's, but not the x's) where there is not an N/A in the availability column, so it would show me 2.
Anyone with any ideas??
Assuming your table is in cols A and B, data from row2 down
Try in say, C2, something like:
=SUMPRODUCT((A2:A100<>"N/A")*(...Can't insert columns into my access db
I have a db with 5 columns and i want my front end interface to be
able to insert a 6th column. i can do it on the front end but it doesn't
save to the db?
You must change table design in the back end.
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
> I have a db with 5 columns and i want my front end interface to be
> able to insert a 6th column. i can do it on the front end but it doesn't
> save to the db?
Access is not like a spreadsheet where you can insert c...Column Attributes
We have successfully reverse engineered an Entity Relationship Diagram into
Visio. The tables are there with the column names but the column attributes
are not showing up. What do we have to do to get the column attributes to
Figured it out...
Database ==> Options ==> Document ==> Table Tab...Data Types towards
bottom...ensure the radio for Show Physical is checked.
> We have successfully reverse engineered an Entity Relationship Diagram into
> Visio. The tables are there with the column names but the column attributes
> are ...Moving last info in column of worksheet to another in same workboo
I am sure this is easy, but I am not savvy with macros and programming in
excel to figure this one out.
What I need to do is to take the last date entered in a column of one
worksheet and "copy" it to into another worksheet of the same workbook. I
also need to do that with the last amount (currency formatted) of a column.
There are blank cells in the column depending on weather or not there is
information entered in that particular cell or not.
Any help would be appreciated. Thanks
The following macro will copy the last entry in column B of Sheet1 to cel...Counting depending on another column
I am trying to count the number fo weekdays (Sun-Thu) and weekends (Fri-Sat).
I have two columns. A --> Date, B --> Day of Week (DOW). They are not linked
in any way.
Countif would work easily if the sheet was setup normally. However, the DOW
is static (Sun-Sat 5 times + Sun, Mon, Tue to cover every possible monthly
combination) and the Dates move depending on the month and when it starts. So
there are always DOWs that are blank in column A.
So how do I count the true number of weekdays and weekends? Feels like it
should be something like IF column A has values, THEN co...Dragging formlas when inserting columns
When I insert a column within a large data field, all formats of the
cells to the right are included in the new column's cells. I am
looking to include not only the formats of the cells to the left but
also the formulas of the cells to left as well (as if being dragged
from left to right).
Does anyone know if this can be done?
Also, how do you prevent formulas from being 'knocked out' of place
when adding columns of information??
Any help in this area is greatly appreciated
Message posted from http://www.ExcelForum.com/
for your ...Macro for Show/Hide Column
I am trying to write a Macro to Toggle Show/Hide column D
Anybody able to help?
Here's a basic macro that will do it:
If Selection.EntireColumn.Hidden = False Then
Selection.EntireColumn.Hidden = True
Selection.EntireColumn.Hidden = False
> I am trying to write a Macro to Toggle Show/Hide column D
> Anybody able to help?
> with thanks...Andy
Simple...Database Diagram 15 ruled column limit?
I am creating a database model, before we build the database. This
means that I cannot have Visio build this for me. I am using column
shapes with connectors and just setting everything up.
It has been going fine, but I have 1 issue. I searched for table and
then for column. A shape called 15 ruled column is what I found.
This works fine unless I have a table with more than 15 columns. Then
it doesn't frame my field list after that.
Any suggestions out there? I am frustrate that I can't find another
shape, change this one to work or something. I have so much of this
done, but ...Rows & Columns on a page
I have got some spreadsheets with three columns as follows
REF PRICE SURCHARGE
The spreadsheets have got over 2000 rows. If I try to print them they will
come out on to 47 pages. To get it to print on fewer pages, on one of the
spreadsheets I have cut and pasted it so that it now looks like this,
REF PRICE SCHARGE REF PRICE SCHARGE REF PRICE SCHARGE
I did this by making the extra column headers and cutting & pasting at the
page breaks. This took me all morning and I'm dreading doing it to the other
spreadsheets. Does anyone know a quicker way I cou...easiest way to parse column and row info?
I'm trying to set up a macro to function on a user-selected range (rather
than hardcoding row/column references, or having a bunch of inputboxes to
collect the reference info)
Getting the range is easy;
Dim TempRange as Range
Set TempRange = Selection
debug.print TempRange.Address(False, False)
which gives me (in my test scenario): B6:AD1463
My macro will need to process each column independently, and for each
column, I will loop through each row and perform some actions.
I can use left/right/mid to pull apart B6:D14 into the component pieces
(B..AD and 6..1463) bu...multi-state withholding
In a recent Ernst & Young article, the increase in Withholding Tax audits is
putting pressure on organizations to find payroll software to accomodate
mult-state withholding. Today, Great Plains does not have this functionality
out of the box, and we are creating customizations to handle this.
Here is an example of a Wisconsin resident working in both Wisconsin and
California (long term projects):
The applicable tax rate that the employee should be taxed at based on total
compensation and not just compensation particular to that state. For
example, if the employee gets paid semim...How do I add 1 to a number in column b
I have a spreadsheet in which column b refers to the number of years of safe
driving. ie "22 years". How do I add 1 to each cell in column B?
If you literally wrote "22 years" into a single cell, the manipulation needed
=LEFT(B2,FIND(" ",B2)-1)+1&" years"
*Remember to click "yes" if this post helped you!*
> I have a spreadsheet in which column b refers to the number of years of safe
> driving. ie "22 years". How do I add 1 to each cell in column B?
I didn...Split text into columns
Has anyone got any code that would split a name in one cell into three
columns. I have seen somewhere how to look for the space and so split it
up! An example of my data is:
Rev Christopher Dent
I assume your names are in Col. A and you want the First Name in Col.B,
Middle Name in Col.C, and Last Name in Col.D. This will do it for you.
Dim rng As Range
For Each rng In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Range(Cells(rng.Row, rng.Column + 1), Cells(rng.Row, rng.Column +
3)) = Split(rng, " &quo...Add column to database diagram with macro
I'm trying to write a macro that will automatically add a column (with
a specific name, data type, and notes) to the selected table in the
I tried recording the macro, but nothing got added to the code.
Any ideas how I would do this?
the problem is that the database tools are add-ins and the code is closed.
<Ben.Bawden@gmail.com> wrote in message
> Hi all.
> I'm trying to write a macro that will automatically add a column (with
> a specific name, data type, and notes) to the sele...Possible to make first column float?
i have a lot of columns, each relating to a week of the year. Rather than
the user having to keep referring to the first column..is it possible to
make the first column float and stick to the left side of the window
regardless what cells are being worked on?
Yes, you can split the window vertically and then freeze the panes.
Use Window, Split first. This will split both horizontally and vertically,
so just drag the horizontal split line to the top to remove it.
Drag the vertical split line to the right of column 1 (i.e where you want
the split to be positioned)
Use Window,...Excel Column Headers
My column headers in excel are suddenly numbers instead of letters. How do I
change them back to letters?
Tools>options>general and uncheck R1C1 reference style
> My column headers in excel are suddenly numbers instead of letters. How do I
> change them back to letters?
'Tools - Options - General' and uncheck 'R1C1 Reference style'
"muggint" <firstname.lastname@example.org> schrieb im Newsbeitrag
>...Code to Color ActiveRow Columns A Through C
We have a macro with the following code:
.ColorIndex = 38
.Pattern = xlSolid
What the user really wants is whatever cell their cursor is in, for th
macro to automatically change the BackColor of Columns A B and C in th
Active row the cursor is in to ColorIndex = 38
The code attempted above only affects a specific range, whereas th
code we need, needs to be able to work ANYWHERE on the worksheet
How do we do this
Message posted from http://www.ExcelForum.com
With Cells(ActiveCell.Row, 1).Resize(1, 3).Inter...3D Stacked column
This is a simple question: I'm comparing 6 different products across 3
categories on a 3D stacked column chart. Everything is fine to this point. My
issue, one of the products has very large values in 2 ctegories out of 3 in
comparison to the other 5 products. My data values range from 1 to 30 but the
6th product ranges from 1 to 500. What's the best way to approach this, other
than not to plot the 6th product?
If u change the way u look @ things, the things u look at change.
I would try a number of other chart types. First, the sooner you get away
from 3D types, t...Last value in column...
Is there any way to find tha last value in column. I have several columns
containing values and also empty cells. I'd like to find the last (lowest)
value in every column with a formula.
Try this, will return last "value" in column A that is equal or less than
whatever 99^99 equates to (increase that value if needed). If the last cell
in column A is text, it will return the last numeric value in A.
"Django" <email@example.com> wrote in message
> Is ...matching a column of numbers to another in another spreadsheet
I have a spreadsheet of shortpayment amounts and their invoice numbers on one
spreadsheet with a column of credit amounts with their invoice numbers on
another spreadsheet. I need to match the credits written with the
kinda like this
spreadsheet 1 spreadsheet 2
inv # amount date Credit #
122334 15.00 11-25-09 675555 15.00
223345 22.00 10-20-09 754444 22.00
naturally...Macro or formula to compare columns and give a result of the odd o
User State Plate User State Plate User State Plate
jmolini OK 2MO980 mboulette OK 2MD980 jbradley OK 2MO980 1
jmolini TX 30VVF8 dfoose TX 60VVF8 awright TX 30VVF8 1
jmolini TX 4D0U558 dherrera CA 4DOU558 awright CA 4DOU558 1
I need to compare entry from three different users. The user names are in
columns L, O, R.
I need to compare M and N, to P and Q, to S and T, which ever one is
different, I need that User name inserted into column M. In my example, line
one, mboulette (P and Q) would be the one different and the user name would
need to be copied to column V (mboulette).