Moving data from many columns to a single column
I have Excel 2007, I have a spreadsheet with 38 columns with product names
in an average of
50 rows in each column, one name per cell.
I need to put all of these products into one column.
How can I do that automatically.
Give a try to :
Dim c As Range, Sh As Worksheet
Set Sh = ActiveSheet
For i = 1 To 38
For Each c In Range(.Cells(1, i), .Cells(65536, i).End(xlUp))
Range("A65536").End(xlUp).Offset(1) = c
>...Changing rows by Columns
I have a query. I need to show the result of this query changing rows by
columns in the table of my report.
How I can to make it in SSRS 2005?
Best regards and thanks!!
I would T-SQL , a new PIVOT command does that
"julito" <firstname.lastname@example.org> wrote in message
> I have a query. I need to show the result of this query changing rows by
> columns in the table of my report.
> How I can to make it in SSRS 2005?
> Best regards and thank...How to delete rows automatically with values zero out?
I have a spreadsheet with hundreds of rows. Many of them were entered
at the beginning and then were reversed out. I got this spreadsheet
from our ERP system. It is just like this:
Part# Quantity Value
013-001 1 $1.00
013-001 -1 $1.00-
013-002 5 $25.00
013-007 3 $120.00
013-007 -3 $120.00-
022-001 12 $17.00
041-009 7 $251.00
041-009 -7 $251.00-
052-061 10 $30.00
How can I delete those rows autometically with the quantity were zero
out. I don't want them show up on my spr...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...How to set the column title in a listbox from Excel
any help here?
You can do it two ways.
1.) Include a header in your RowSource Range.
2.) You can put Lablels just above your ListBox to label your ListBox columns.
Hope this helps! If so, let me know, click "YES" below.
"V Hoang" wrote:
> any help here?
If ColumnHeads is set to true in the properties window, then the header row
should not be included in the RowSource or ListFill range. VBA will
automatically include the row above the ListFill range as a header if the
ColumnHeads property is set to true.
"Ryan H&q...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 ...Modify Outlook Default Columns for New Folder??
When I create a new folder to store e-mail, one of the default columns
is the "Size" column. I prefer not to have this column in my folders.
I know I can right click and remove this column.
Is there a way to modify my settings so that this column will never
appear when I create a new folder in Outlook?
View, Arrange by, if Ol2003], Current View, Define views... reset the view
(usually Messages) and modify it.
Diane Poremsky [MVP - Outlook]
Author, Teach Yourself Outlook 2003 in 24 Hours
Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide)
Author, Google and Othe...calendar issues with column headings
I have users that display multipe calendars at once. We are using OL 2003
with Exchange server 2003. When they have 5 or more calendars open, the
column heading gets truncated. This is not a problem except that the column
header is being center justified instead of left justified. When this
happens, the name of the calendar becomes unreadable due to the names being
similar in the middle of the calendar name. I found a setting in the views
to change this for email column headers, but not for calendars. In OL2007,
the calendar column heading is left justified. Is there an easy way ...Parsing thru columns to find the last number
I have six columns of data and am unsure how to get to the final replacement
of an item. See, col A is the itemcode, then b,c,d,e,f contain item codes
that have replaced the col a item. Not all items have been replaced five
times, some none, some only two, in this case col b would be blank and or col
C would be blank. My goal is to find the last item in the columns that were
replaced and put it in a column on it's own. So the outcome will be colA is
replaced by Col G.
The data looks like this.
A B C D E F G
z k o p i So this was replaced four times and the end res...Grouping columns
I have a spreadsheet where the columns of data are in logical groups.
I have 5 columns of data for the month of February, followed by the same 5
columns but for March etc.
What I want to do is than when the user scrolls right or left the whole of
one month moves and not part of it.
is there some way to group the columns so that they will scroll as a unit?
You can use one unused row, like row 1, and merge the 5 cells across for
each month..........then use that row to do your scrolling.......
Vaya con Dios,
"Jonatha...Automatic email of reports
Can reports be generated and sent automatically to given
mail ids on a regular basis ?
via Crystal Reports, not an option with current out of box version v1.2.
You can try externally controlling it via SDK approach.
San Francisco, CA
> Hi all
> Can reports be generated and sent automatically to given
> mail ids on a regular basis ?
> Pls advice
...how do you keep a column in date form
i'm making a spreedsheet in office excel, in column A I have a list of dates.
I would type in 4/5 for an date and excel would automatic change the date to
4-Apr. I like this way, but when I got to cell 26, the change will not
happen. I type in 4/5 and I get just the number 1 in the cell, for the rest
of the cells down, can someone help me.
Copy the cell that has the format you like and paste format it to the cells
that you want to have the date format.
"s2m2" <email@example.com> wrote in message
news:F350F90F-24B9-4B79-BE39-20AB0414015D@microsoft.com......how do automatically change entire row color when cell changes
is it possible to change the color of the row cell1 is in when cell1 changes
to a specified text?
See Help under Conditional Formatting
remove CAPS in email address
"IOE" <IOE@discussions.microsoft.com> wrote in message
> is it possible to change the color of the row cell1 is in when cell1
> to a specified text?
- select the row (e.g. row 1)
- goto 'Format - Conditional Format'
- enter the following formula
=$A1="T...Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a
particular question extend from Q6 to Z505. I need to count the people who
did not answer the question (that is, the people, entered in rows 6 to 505
that left columns Q through Z blank). I'm not sure how to do this. Can
someone offer a suggestion?
You can use a formula like this in Column AA
copy down till AA505
You can use a simple Sum formula now to count the empty ones
Regards Ron de Bruin
"crossingboston" <...HIDDEN COLUMNS #3
SOMEHOW I'VE HIDDEN COLUMNS A AND B, AND NOW I CAN'T UNHIDE THEM. HELP!
Click on the header for column C, highlighting the entire column. Without
releasing the mouse, drag until you're over the column of row headers (on my
version of Excel, in Office 2003, I see the tool tip change to '3C'). Now
you should be able to Format > Column > Unhide.
> SOMEHOW I'VE HIDDEN COLUMNS A AND B, AND NOW I CAN'T UNHIDE THEM. HELP!
If you select the sheet then go to window and unhide it will restore the
"acp20770&q...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 ...Adding and subtracting from a column.
I have a stock list with a "quantity" column.
What I would like to do is add the new stock to the existing quantity
column or subtract requisitioned items from the quantity column.
I guess it is a "self totaling" column I need to make.
As a separate exercise I would like to make a new column for orders.
Insert a quantity on that column for any of the items ( rows) I want
to order and then print it out... BUT I only want to print the rows
that have quantities in that column ( i.e. the actual items I am
ordering ). This column is not linked to the above column.
Could an...How do I automatically plot different ranges of data in different.
I need to chart parameters that are a function of three variables in the 2002
version of Excel. I have used the x and y axis to represent 2 variables on an
'xy chart', but require an automatic method to represent the value of the
third variable. I have tried the 'bubble-plot', but cannot get sufficient
resolution between the data. Is their any way that I may plot the third
variable in different colours/shades, that automatically changes for
different ranges of the the third variable?
THis might help you:
&q...Bridge or Up/Down floating column chart
Might anyone know of an example to a bridge or floating
bar chart that I could download and learn from - I need
to build a template in excel for lots of Power point
I have a book with a picture of one but no examples.
Thanks for the time
Try either of these tutorial pages:
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
> Might anyone know of an example to a ...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...Automatic display positive or negative
Dear All, pls help me.
when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
, if B1 is positive or negative, I want the entire column C.
C1 : I type $52, it display for me $52 , because B1 is positive
C2 : I type $52, it display for me ($52) , because B2 is negative
B1 5 $52
B2 (3) ($52)
It is possible ?
Thank you for your help.
This can only be done with VBA and the code below will do it for you. To use
the code, open the workbook and go to the sheet you want this to work on and
Right-click o...How to succesfully sort column of formulas?
I am trying to sort a column of formulas that consist of cel
Cell 1 holds: =D10
Cell 2 holds: =D11
Cell 3 holds: =D12
When I try to sort the column of formulas, the formulas change cel
references to other cells and mess up the data.
How can I sort a column of cells of which all of them contain formula
and have the cells keep the references to the right place
Message posted from http://www.ExcelForum.com
If you write your formulae with absolute references rather tha
relative references, you can sort them. Formulae should look ...Default Public View has records but columns are blank
A custom entity has a default list view but the entries in the list do not
show any values. There are a number of lines in the list with an icon only.
If I try to delete one the message says select an entry before attempting
operation. I cannot select the lijne either. Have created many views before
and never seen this. Other views are ok?
Try publishing the entity and see if this resolves the problem.
> A custom entity has a default list view but the entries in the list do not
> show any values. There are a number of lines in the...selecting the 2nd column of a combo box in a report?
I have a combo box that uses data from a table with the fields (setupid, physician setup, md_PIN). the bound column = 1. The physicians sign on using their name (signing_md) and PIN (signing_md_pin). thanks to advice from this website I have...If signing_md_pin.value = dlookup("md_pin". "[setup table]","[setupid] =" [signing_md] then ... in the event procedure. When I try to get the physician name on the report (signing_md) it returns the physician number (setupid) instead of the name. How do I get the 2nd column of the combo box to the report?Thank you....Row amd column heading
I have received one excel file in which rows number (1,2...) and column name
(A,B,...) are in some big font 18, or maybe more. I couldn't find option to
make them standard.
Thanks in advance, Emilija
On you toolbar you should have a font box and next to it
is the size of the font. Select the row and change the fontsize
to 10 for instance.
Help, Answer Wizard
Change font size
From the topics show, select the topic that matches
"Change the font or font size"
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http...