How can I combine data that is in 2 rows to 1 row
I have a file that has only 1 data point (name of an institution) in row 1 column 1 and then multiple data points (city of the institution and other results) in row 2 column 1-7. I would like to combine the data in consecutive rows into 1 so I can work (sort results in different columns) with the data. As you can imagine the file has 2000 rows but the data addresses only 1000 institutions.
I Would appreciate any help
albert explained :
> I have a file that has only 1 data point (name of an institution) in row 1
> column 1 and then multiple data points...Lookup value off by one row
Any help you can provide on this would be appreciated. I have a worksheet
that identifies how far a number is from target. I created the lookup below
to coorespond to the productivity increase for next year.
C4 is -10.9, in the lookupValues tab -10.9 is in row 93 column A as is the
desired result return of 5.6 in column b, however the lookup function is
The issue may be that the data and the lookupValues tab numbers are not
exactly the same. -10.90001 and -10.9004 may display the same, but w...Adding the # of rows, not the contents...
On my sub-totals row, most of the columns are just added together for a sum
figure. However, can I have a particular column tally how many columns it is
counting? Specifically, I have a column for customer's names. I just want
the subtotal bar to display how many customers there are in the column above
Try changing the 'sum' function in the subtotal bar to 'count'. Or add a
formula, for example if the customers are listed in Column A, then in any
empty cell enter the formula =count(A:A)
"jacob farino" <jfarino@mind...Colored cells are showing white on screen, want to see color?
I have sheet cells that I know are colored, but they show white on my system,
and print grayscale, while showing their true colors on other office
Does this article help
Changes to fill color and fill pattern are not displayed in Office programs
"dean" <firstname.lastname@example.org> wrote in message
> I have sheet cells that I know are colored, but they show white on my
> and print grayscale, while showing ...How do I rearrange (switch) the information in my columns to rows
Copy the data out of Publisher and into Excel. You can swap it out there
with the Paste Special (transpose) command.
MVP Microsoft [Publisher]
How to ask a question
"larrey" <email@example.com> wrote in message
The dirty way in Publisher, high-light the table, format menu, table, cell
properties tab, check *rotate text within AutoShape by 90 degrees* Rotate the
entire table 90 degrees.
Mary Sauer MSFT MVP
http://office.m...Deselect one of many non-adjacent rows selected
If you are going through a worksheet and selecting several rows to, for
example, delete them and you accidentally select a row you didn't mean to, is
there a way to deselect just that one row? I noticed that CTRL clicking on
the row again does not do the trick.
Very annoying feature.
You can add a couple of Chip Pearson macros to your Personal.xls that will
allow the unchecking of a mistake.
Assign to a button or to the right-click menu. See this thread for the macros
and instructions for adding an item to the right-click menu.
Gord D...hiding rows #3
I want to find a quick way to hide various rows in a spreadsheet that contain
zero values. i.e. below example i want to hide the whole row 'oranges'
I have quite a lot of rows that may need hiding and any help would be
Microsoft MVP Excel
"karl middleton" <karl firstname.lastname@example.org> wrote in message
>I w...EXCEL:printing row headings at the top of every page.
I have looked at the EXCEL help and am not able to get row
headings at the top of every page. I see the sheet tab
and I see where I am "supposed" to enter the rows to be
printed at the top of every page. HOWEVER these cells
are "grayed out" i.e. inaccessible. Is there something
else I need to do before I can access this function? It
seems simple enough, but I am not able to enter rows to be
Are the sheets grouped? as this will cause that to happen, in which case you
need to ungroup and set the print area.
Ken.............Can you add color to a graphic?
Im use to InDesign mainly and it has a way to colorize a grayscale or bitmap
file on the document without having to manually do it in photoshop or some
other program. Does Publisher have a simillar feature?
answering my own question...right click on image, FORMAT
> Im use to InDesign mainly and it has a way to colorize a grayscale or bitmap
> file on the document without having to manually do it in photoshop or some
> other program. Does Publisher have a simillar feature?
...Inserting alternate blank rows in Excel
I have several spreadsheets with between 2,000 and 3,000 rows of data. I'm
using them to import data into some accounting software. The format required
by the software is that each row of data is separated by a blank row. Short
of individually inserting several thousand rows, does anyone know another way
of changing the layout of the spreadsheet?
Well, you could add a helper column and enter 1, 3, 5, and copy down to
the bottom of what you've got. You could then put 2,4,6,8 ... in blank rows
until you have the max # of what you need. Then sort by the helper column.
Make su...Spot Color separations not printing correctly
I am trying to print seperations for a two color project
( black + one pantone color). When I print on my InkJet
and Laser printer the seperations are screwed up. When I
took the file to the printer for preflight they were
OK ?? Anybody know why ?
"Screwed up" how? How was job preflighted?
"Stuart F." <email@example.com> wrote in message
> I am trying to print seperations for a two color project
> ( black + one pantone color). When I print on my InkJet
> and Laser printer the seperations are screwed up...visio hyperlink to excel row
I have a drawing of a Dell rack with individual Dell server box pics in it.
I figured out how to link one server's picture (using insert hyperlink) to
open a spreadsheet containing server stats. What my boss wants is for each
individual server's picture, when clicked, to open to its row of data in said
excel file. Does anyone have a clue as to how this is done, either thru
hyperlink, macro, vba, vbs, or sql script for each server? It would be
helpful if the solution could find the server name in the spreadsheet then go
right to that row.
Thanks very much from a 1st time vis...macro to delete multiple rows
I have recorded, then modified, the following macro, which unprotects the
sheet, aks if you are sure you want to delete the row, deletes the current
row, copies down a cell in column A that numbers the row, and protects the
sheet. Works fine, but if the user wants to delete not only the current row,
but multiple rows below it also, the macro must be repeated. How can I
accomplish deleting multiple rows, as indicated by the user? For example,
rows 18 thru 20, or 29 thru 33, etc, or of course, just the current row,
with the message box prompting the user for the number of rows to delete.
Thanks...Can one adjust Format Row Autofit to ensure WYSIWYG when printing
Does anyone know if you can adjust Format Rows Autofit to ensure WYSIWYG when
printing. I am continually frustrated that when I print after using the
autofit function that I get text cut in half and underlines missing. Autofit
does not seem to work properly. Can it be set to slightly overcompensate
Hi Gray, this problem only happens when you're working in a zoo
different from 100%; so if you're working with 115% and autofit th
rows they won't show up properly when you print (they will be cutted)
Also there is a problem you have to consider is when you use Bold o...filter on the rows
Is there any possibility to make a filter on the rows
shlomop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=973
View this thread: http://www.excelforum.com/showthread.php?threadid=46709
As far as I know, no. Filters can only be applied to columns. You ma
need to transpose your data to apply a filter.
I'm sure if someone knows of a way (probably via VBA) they will post
Can anyone tell me why
Cells.Interior.ColorIndex = Int(Rnd * 56) + 1
works ok using the forms toolbar with a button click but
does not work if you use the control box toolbar.
What does "not work" actually mean? What version of Excel are you
Open the Properties window for the control button and change the
"TakeFocusOnClick" property to False.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"maria" <firstname.lastname@example.org> wrote in message
news:07e...Changing text/color after a time period?
I hope some of the brilliant Excel people here can help me out. What I
am looking to do, is enter a date in a certain cell (if it helps
conceptualize, the date that I ship something to someone). Is it
possible to have that cell with the "ship date" in it, either change
colors, or change the font size, or something, after 30 days have gone
by? My reasoning is that the product I ship out, has to be back to me
in 30 days, or a penalty will incur to the other party. I just want
these fields that have exceeded the 30 day mark, to stick out, so I can
easily identify them. Thanks for any h...Formula Bar font color
I don't know how I did this, but the information in the formula bar is
showing up in white font... Please help! ... I want to change it back to
Try this (Think it's a windows setting)
Right-click on any empty space in the desktop > choose Properties
Select Appearance tab > Advanced button
(in Win XP)
In the Advanced Appearance dialog:
Click on "Item" dropdown menu > choose Message Box
Under "Color" for Font (in the line *below* "Item")
Choose "Black" color > OK > Apply
(and don't do i...hide a row if the values in the cell are zero
Can sombody help
I need to hide a row if the values in the cell are zero
I think the easiest way to do this is to select your range and apply
Then you can use the dropdown and do: does not equal 0.
> Can sombody help
> I need to hide a row if the values in the cell are zero
...Is there a limit on selecting individual rows in excel?
I am using VBA logic to set a variable holding a series of rows to be
deleted. When I use this on small worksheets no problem. But in larger
worksheets when the logic goes to "rangevariable.select" I get an error.
There are approx 168 rows selected in this occurance.
Just a guess...
You have to have the worksheet that contains that range selected before you can
select that range.
application.goto rangvariable, scroll:=t...Autofilter header row
My experience has been that the first row is where the controls (down arrow)
appear when autofilter is invoked. But I've now been sent a spreadsheet (just
unformatted data in a large table) where the autofilter chooses the second
row. I've compared the new spreadsheet with previous versions and don't see
any difference in layout, etc.
What can be causing this problem? Is there something "invisible" that tells
Excel which is the header row for the data to be filtered?
If I select row 1 and then click autofilter, the controls do appear on row 1
but otherwi...Help: Macro that runs various if statements, inserting rows etc
I am a newcomer to Macros and excel programming and was hoping someone
could assist / provide the code to do the following:
I have a +50,000 line spreadsheet, that needs to be formatted. Given
the size I would like to automate this. There are 3 key formatting
changes required and detailed below:
n = 1
1. If Cell A(n) not equal Cell A(n+1) then
insert a new row below A(n), make Cell F(insertedrow)=1, make
Cell G(insertedrow)=A(n+1), make Cell H(insertedrow)=B(n+1)
2. If Cell C(n) not equal Cell C(n+1) then
insert a new row below C(n), make Cell F(insertedrow)=2, make
Cell G(inserte...IF number is grather than 100 color the cell red
I did this before but I can't remember how I did it. I want to have a
column of numbers and if they are greater than a certain value ie: 120
, I want the sell to have the color red.
Any ideas how to do this in Excel 2007?
Take a look at format|conditional formatting.
Ron Boetger wrote:
> I did this before but I can't remember how I did it. I want to have a
> column of numbers and if they are greater than a certain value ie: 120
> , I want the sell to have the color red.
> Any ideas how to do this in Excel 2007?
...Row & Colomns
I need ONE worksheet with 100 rows & 20 colomns only.
Is there a way to have this?
I don't want to use "hide" option for row and colomns. Is this possible?
you could set row heights beyond 101 to 0 and column widths beyond Col
T to 0, but why not hide them??
Private Sub Worksheet_Activate()
Columns("U:IV").Hidden = True
Rows("101:65536").Hidden = True
"Khalil Handal" <email@example.com> ha scritto nel messaggio
news:OnTTVGPOFHA.164@TK2MSFTNGP...Reseting the last row/column
I have a spreadsheet of data, lets say 35,000 rows long. So when I do
a [Ctrl] + [End], it goes to the cell at row 35,000 (and the last
column of the data).
Now lets say that I run a macro that deletes 5,000 rows. So now the
last row is 30,000. But if I do a [Ctrl] + [End] again, it still goes
to row 35,000.
Is there any way to reset the spread sheet so it knows that the last
row/column of data has changed since row/column deletions?
Thanks for any help anyone can provide,
Here is an article from Msft on the topic: