Sort multiple columns into one column
Once again I am here with yet another problem...
I have several columns of serial numbers that I need to sort into one
column. Is there anyway to just select all of the serial and sort them
automatically? Here is an example of what the spreadsheet looks like:
COLUMN 1 COLUMN 2 COLUMN 3
WBLM015780 WBLM006249 WBLM006249
WBMM025115 WFMM041685 WBLM006249
WDMM020958 WFMM074766 WBLM006249
WDMM021095 WFMM074766 WBLM006249
And I need to put them all into one column. I have hundred of serial
numbers in each column and it would be a pain just to cut and paste.
Thanks in advance!
M...average multiple columns but skip a few columns
How can I average muliple columns that are not continuous? For
example, I'd like to average A2 to Z2 but ignore the numbers in C2, F2
Thanks for the help
MS Excel MVP
"cpliu" <firstname.lastname@example.org> wrote in message
> How can I average muliple columns that are not continuous? For
> example, I'd like to average A2 to Z2 but ignore the numbers in C2, F2
> to H2?
> Thanks for the help
On Dec 5, 7:04=A0pm, "Bernie Deitri...Averaging columns #2
I tried Average, but it won't pass over the broken references either.
Since I have these columns present with no data, I have a divide b
zero error...I need the average to just ignore those columns.
I am a n00b when it comes to Excel, so forgive me if I am overlookin
GarrettSocling's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1622
View this thread: http://www.excelforum.com/showthread.php?threadid=27639
you may change your f...column A matches f places the value from g in column b what funct
I am trying to have excel enter information in cell b by finding a number in
a, matching it to a cell in f and returning information from g that that
coresponds with the number in a, example
if column A has numbers 1-10, coulmn F has numbers 1-10, column G has
information related to each number so if I place 2 in A, it searches column F
for 2, places information from column G that matches in column B
In B1 enter this formula.
Drag/copy down to B10
Gord Dibben MS Excel MVP
On Sun, 15 Nov 2009 11:28:01 -0800, Daryl <Daryl@discussion...Adding column data based on match in 2nd column
Simple problem, but got me stumped.
I have 2 columns. The first (column A starting in row 10) has members' names.
The second (column B starting in row 10) has the additions made by the
members over time. Column B has a 'header' cell that adds up the values
entered by a particular member. The header cell for column B is B9. Cell A1
contains the member's name that cell B9 has to add the values for.
I need a formula for Cell B9.
[Cell A1] Member A
[Row 9] Formula needed for cell B9
COLUMN A_______|| COLUMN B____
[Row 10] Member...Column Width
I am using Excel 2000. When I drag a field name from the Pivot Table toolbar to the "row" area of the Pivot Table, the resulting column width is often inadequate to display the contents. Of course, I am not prevented from widening the column after the fact; however, it should ideally provide a "best fit" width automatically.
Is there something I am missing here?
You can turn on the AutoFormat option, and the columns widths should adjust to fit:
Select a cell in the PivotTable
From the PivotTable toolbar, choose PivotTable>Table Options
Add a check mark to 'Auto...How to add one column to another column?
I have a worksheet with product ID and Product Color Code and I like to
combine the two columns into one. For example
Column A Column B
Combine together like this
In C1 type
and copy down
Microsoft MVP - Excel
"Rocky Lam" <RockylamATyahooDOTcom> wrote in message
>I have a worksheet with product ID...Row and column headings
I can get row and column headings to print on the first page of my Excel
worksheet, but I can only get column headings on page 2 and row headings on
not really sure what your issue is :-)
Could you re-state your problem
> I can get row and column headings to print on the first page of my
> Excel worksheet, but I can only get column headings on page 2 and row
> headings on page 3.
...Formatting Columns in Excel
I have a problem with my spreadsheet that I'm hoping someone can help
with. I have created a new spreadsheet and formatted the columns to
suit the data that I am entering. I have other data emailed to me from
another co-worker and whenever I cut and paste the data on to my master
spreadsheet I have to re-format the columns that have been just added.
My question is, is there a way to format the columns so that whenever
date is cut and pasted to my document it will automatically change the
formatting to default to the same as my master document?
By the way, I created a duplica...Text to Columns
excel 2000 / 97
I quite often use Text To Columns to convert text to numbers so that
for instance the same number doesnt show up twice in pivots just
because one instance is formatted as text and another as numeric.
Its usually very useful but I notice that sometimes it unexpectedly
moves data upwards in a column. This particulary seems to affect new
open new sheet
Enter following values
Select column A and do Data>Text to columns. The data seems to be
pasted up by one row so I now have 4 rows of data.
If you repeat these steps on the sam...Excel
I'm new to Excel and would like to change all negative numbers appearin
in a non calculated column to positive numbers. The problem result
from a bug exporting Quickbooks Pro invoices to Excel, in th
"quantities" column, and would like a quick macro to fix, versu
changing each column entry to a positive number by hand.
Message posted from http://www.ExcelForum.com
For Each c In Selection
c.Value = Abs(c.Value)
"John E. >" <<John.E..email@example.com&g...Pivot: totals in the next column
Hi all, i need your help for the below table:
ITEMS YEAR YRLY_TOT GRAND_TOT
1. ITEM1 2009 $20 $60
2. ITEM2 2009 $30 $80
3. ITEM3 2009 $30 $90
Each item has yearly totals (column YRLY_TOT). I want column GRAND_TOT to
show total of all three years for each item ( i guess the three cells shou...Create Pivot Table Data with Column "Sum" rather than "count" defa
I have a large set of data (20,000 rows and about 100 coulmns). I wish to
select about 5 columns for a pivot "rows" and about 60 columns to place in
pivot "data". Unfortunately, when I drag these columns into "data", all of
them default to "count" of column. I then have to manually change each one to
"Sum". Is there a simple way to change this default to "sum" without a 2nd
step for each field added?
If you have any non-numeric data in a field (empty cells are non-numeric!), then
excel will use Count.
But you could tak...Create a new column using information from separate columns
Another question that I posted under Access, I hope this is the right
place to ask this. I have a little bit of a problem, and I wasn't able
to find the solution anywhere.
I have column A "Title" that is linked with column B "Day of the week".
Each title can have 1, 2 or 3 days of the week (which will then be
column C, D, etc). It looks like this:
TITLE DAY1 DAY2 DAY3
Math Mon Tue
History Tue Fri Mon
English Wed Mon
How can I create a new column that lists all the "titles" that appear
on Monday and a separate column fo...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...prefill
I enter ID and data on form 1. It gets stored in table A. I input same ID on
form 2, for which I want a select number of fields (populated) from tableA to
be displayed, then enter data into new fields on form 2 for which they will
be stored in table B. I want the ID for both tables to be the same, so as the
relationship is 1to1. (The PK are the same for each table).There is no
changing what is on each form, and no making them subforms as this example is
just a super watered down verison of what I have. I need help!!! (please no
code use). How do I do this?
Message posted via Access...Sort whith 3 row column heading
I've searched the forum on sorting and can't find this problem. My shee
in Excel 2002 has three rows of data that serve as column headings.
need for others to be able to easily sort using the toolbar sort keys.
When I do this, it doesn't recognize the column headings and sorts the
too. If I choose the data range myself, it works - but the users nee
a one touch simple method. Any ideas
Message posted from http://www.ExcelForum.com
You need your column headings in one row. You can use Alt-Enter as you
enter them to break into separate lines within the cell, or set ...Prefill column
Below is a sample of the data I download to a spreadsheet. I need to write a
macro to fill in the empty cells in column A with the data from the previous
cell in the column with data. I know I didn't explain that well but in the
example below, I need TF48 to fill in Col A for rows 3,4 & 5. Then T16A
would fill in row 7 and T505 in row 9 and 10. I hope I've explained this
sufficiently. Thanks in advance for your help.
1 A B C D
2 TF48 $100.00 ABC 20091116
3 $ 50.00 DTR 20091116
4 ...After date entered, prefill another date field
I was wondering if anyone knows how to get one field to populate the same
datainto another field? In other words:
I have a date field "Funding Date" and when the user enters the funding
date, I want the txtDateFrom1 field to automatically populate with the same
date so the user doesn't have to enter it twice. If possible, please let me
know and also, please simplify answer.
Use the After Update event of the Funding Date control:
Me.txtDateFrom1 = Me.FundingDate
Dave Hargis, Microsoft Access MVP
...Sorting for column based on another column
I want to sort say column a1 to a30 which has names in it based on column b1 to
b30 which has say widget sales to put column a1 to a30 in numeric order from
high to low
So that say:
Would end up as
Thanks in advance.
click in any cell between a1 and b30 and choose Data / Sort from the menu
choose whether or not you have a header row
then in the first drop down box, choose column B (or its header)
<firstname.lastname@example.org> wrote in message
Is there a way in pivot tables after selecting hide detail for a column that
new values are also hidden?
...Table Columns/Expanding Cell Width
I was creating my first table in Word 2007. I used Insert->Tables-
When I typed in text, the column kept expanding and the text did not
wrap until it hit the end of the line and the adjacent cells were
compacted to their margins.
Then I did Table Tools->Auto Fit->Fixed Column Width and the column
width stayed the same and wrapped.
So now I create a new file and the do a table. Now the column width
stayed the same.
Not that I would want the initial behavior of columns expanding---why
does it no longer appear?
...divide column(x) by column(y) to give column(x/y) in excel?
I'm trying to divide one column by another column to give me another column
of awnsers as in B1/C1=D1, B2/C2=D2 etc... Is this possible?
In cell D1 enter the following formula:
Copy this formula down column D as far as you need. The cell references
will increment automatically.
> I'm trying to divide one column by another column to give me another column
> of awnsers as in B1/C1=D1, B2/C2=D2 etc... Is this possible?
...hiding rows / columns when printing
is it possible to hide items when printing?
Hidden rows/columns will not print. You may do it manually or with a macro
Microsoft MVP Excel
"sam" <email@example.com> wrote in message
> is it possible to hide items when printing?
...Print 1 Column in multiple Columns
I have a list of 400 names in Column A. I don't want to waste 8 pages printing out the data.
Is there a way to have the data from that column print out in multiple columns like I would if the list was in Word? I copied it to Word and selected 3 columns and would only use 3 pages. I would keep it this way, but I am going to add and delete names from the list so it is something I want to keep in place.
Thanks As Always,
Have a look here:
"Ripper" <firstname.lastname@example.org> wrote ...