I have a single sheet that has 6 fixed columns of data in but the number of rows can vary per user per import. Users can actually insert rows as required. The seventh column has my macro buttons present. It looks and works great for about 20 rows (one screen) full. If the number of rows gets to be more than that and the user needs to scroll to modify those lower rows the macro buttons roll off the top and are not visible to access. Is there a way to keep the macro buttons in the same location (column G) and only have the data (columns A-F) scroll when required by the user? This way the macro buttons are available on the rows beyond the first 20 or so for the user to use, instead of having to scroll back up to use them. I've tried using the split and freeze functions but neither worked.
I wonder if Excel allows scrolling many columns or rows at a time. That's right, I have a worksheet with frozen panes by "D2" cell; then there are sets of 4 columns for each day of the month (for example "D:G", "H:K" and so on). I need when I scroll horizontally, that every first column of the set to snap at the edge of pane at once. In other words, is any elegant way that usual scrolling pace of one column/row at a time to be changed to 4 at a time?
If is possible to create any info about freezing panes. I mean, when is pan freezed, and rows are scrolled in lower part of table (upper data are not vissible) if is possible display/show any text about this (e.g. "The first line of table are not visible")...but only if is not the upper part of data visible..not always.
I have a worksheet where rows 22 through 52 are allotted for invoice information. Now, not every job is going to have 30 invoices, and I'd like to be able to hide 25 of those rows and have a scrollbar for that section of the page so that when a) user needs to enter invoice info they can scroll to an empty row, and b) when reviewing the data user can scroll through and see it all. The rows above 22 and below 50 are job info/totals and need to remain visible.
Early attempts to solve this conundrum resulted in a scroll bar that was capable only of changing the date entered for the first invoice (Date is the info in A22, which was the linked cell).
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
My spreadsheet has 600rows and the vertical scroll bar ends at row 459424. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete.
But the unwanted rows are still there and my vertical scroll bar is not correlated to the 600 rows.
I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB: Public Sub DelRows2() Dim Cel As Range, searchStr, FirstCell As String Dim searchRange As Range, DeleteRange As Range
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
My worksheet ends at cell AG650, and Control-End takes me there. I have deleted all columns and rows beyond that, saved, closed and re-opened.
But the scroll bar takes me to cell AG924500 -- more than 900,000 rows too far...
I tried setting the Scrollarea in VBA and got some unexpected results.
Private Sub Worksheet_Activate() ScrollArea = "A1:AG700" End Sub
The elevator bar did not shrink at all, and the visible portion of the worksheet reacted inconsistently, sometimes correctly, sometimes seeming frozen or stopping short of the 700 rows specified, which makes me think there's an Excel bug involved.
I've also tried Hiding the unused rows but get this message:
"Cannot shift objects off the sheet"
So I've gone into VBA and removed all objects using this code:
ActiveSheet.Shapes.SelectAll Selection.Cut
But with no change at all...
I've also applied several commercial 'Excel tuning' programs, to no avail (FastExcel and CodeCleaner)
I followed the instruction in the TechRepublic article "10 steps to creating a scrolling Excel chart"; [URL].... The problem is that the scroll bar stops working at a certain point and will not bring up the last 8 months of year 4 of my data. I get error messages and the chart turns blank.
I also tried a different method (again for a scroll bar and a rolling chart) as outlined in [URL].... There are a number of similar articles with almost identical formulas. Here, the first 8 month of year one are inaccessible = error messages and chart turns blank.
I have 4 years worth of temperature data. The original data table from where the Index function draws the data is 1463 rows long (minus 2 rows for the header). The "magic" number where the scroll bar stops working is 995 for the current value. From a current value of 994 onwards, the entire matrix displays error messages "Ref#".
In the second case, the magic number is 1242 which equates to August of the first year. 1241 and lower for the current value will freeze the chart.
How the scroll bar control generates and gets hung up on either of these two magic numbers is a mystery to me.
I set the control properties at the beginning, i.e. Jan 1 of the first year to: Current value =1, Minimum =1 (I also tried 0), Maximum = 1500, Incremental =5, page =30, linked cell =$E$2. What is most puzzling to me is the fact that the scroll bar does work but only for part of the data set.
The title makes it sound simpler than it is. I have 2 columns of data which I need to transpose.
The difficult bit is that Reference Number 1 may have 3 reasons (so therefore 3 rows) that need transposing into 4 columns (reference number, reason1, reason2, reason3)
Is there a way to do this?
I have attached a spreadsheet with the original table and how I need the end result to look.
See attached template I am working on. I want to calculate the number of hours worked over specific dates e.g. between 19-25 May 2014 and 26-31 May 2014 for employee "Bird, Ken" - the result is to be placed in cell D19. Tried sumproduct but without success.
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
I am trying to clean up some data, which is organized horizantally AND in rows, as per the attached screenshot.
The fact that there is data horizontally across seven week days and in rows for the weeks of the year makes it impossible for me to use the simple TRANSPOSE feature, of which I am aware...
I have quite a couple of worksheets I need to apply this operation on, so manual work is the worst solution...
I have an excel spreadsheet which contains data for customers and the last date they were seen at an appointment (along with various other bits of info).
The way the data is exported from my database package means that each customer has one row per appointment, i.e. row 1 contains john smith, 01/01/2009, row 2 contains john smith, 03/03/2009, row 3 contains john smith 01/04/2009, row 4 contains joe bloggs 12/02/2009, row 5 contains joe bloggs 27/03/2009. Some customers may have 4 or 5 appointments listed whereas another customer may only have one. I need to get the appointments all onto one row per customer so that I can calculate the number of days between appointments. I have tried to use transpose, but with 8000 rows it takes forever.
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series Antidiarrheal Agents Application Antidiarrheal Agents Generic Drug Name Antidiarrheal Agents Strength Antidiarrheal Agents Type Antidiarrheal Agents Dosage Form Antidiarrheal Agents Container Type Antidiarrheal Agents Volume Antidyskinetics Brand or Series Antidyskinetics Application Antidyskinetics Generic Drug Name Antidyskinetics Strength Antidyskinetics Type Antidyskinetics Dosage Form Antidyskinetics Container Type Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
I have a piece of equipment that I use for temperature profiling temperature controlled chambers. The equipment has up to 16 channels for logging data, although we typically only use 9. The data output from the equipment is by Text file and we convert this to Excel for manipulation into a format that can be issued as a certificate.
The test file outputs the data mainly in a single column and similarly when it is converted to excel the data remains in a single column. To enable us to present the data in a format that is presentable as a certificate we most transpose the data from each channel from a single column into an individual columns for each channel. Because I have limited knowledge of Excel, this operation is very time consuming and I'm sure there must be a quicker way to perform this operation in Excel.
I have attached a copy of the text file and the resulting Excel file with the data in it's original format and also in the final format. This is only a snapshot of the data over 30 minutes and we normally carry out measurements over 24 hours.
I have the following spreadsheet with 4 columns of data:
1000001245aMr Fred Blogs 1000001270a1 Blog Street 1000001270bNorwich 1000001270cNorfolk 1000001270eNRx x99 1000001270gThe Manager 1000001270k01603 555 555 1000001270l01603 555 555 1000001270qManager 1000001270xN 1000001270yN 1000002245aMrs Fredilina blogs 1000002270a10 Blog Street 1000002270bNorwich etc etc for about 36,000 rows.
What I need is for each unigue reference ID (column A), I need all corresponding data moved up into a single row in serperate columns, something like this:
1000001 |245|a|Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc 1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc
Each entry doesn't nessesarily have the same number of rows.
I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.
I'm using Excel 2007 and I need to extract values from rows from a data dump into columns. Here is sample data (this is dummy data, but the format is the same): ...
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name Column C: Sales Rep Number Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet ...
I have an Excel sheet that is in rows. I want to separate the data into columns. Each sample is separated by a space in the rows. So sample 1 is A1 is the name and B1 is the actual name. A2 is the description and B2 is the description. Each sample is +or- rows. Sample 1 may be 8 rows but sample 2 may be 6 rows. What type of formula or macro do I use to arrange the samples into columns instead of rows so the data will be like A1 name, A2 description, etc... and A2 will be sample 2 name, and B2 will be sample 2 description, etc...
I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?
Column A Column B Name John Doe Address 123 Maple Street City Any City State Any State Zip 12345 First Name John Last Name Doe BLANK ROW Name Jane Doe Address 123 Maple Street City Any City State Any State Zip 12345 First Name Jane Last Name Doe
I need to get this data into the following format for a mail merge: Location,secnumb1,secnumb2,secnumb3,secnumb4,secnumb5 1000001,10052950,10052960 1000002,10052980,10052990,10053000,10053010,10053020 1000004,10053530,10053540,10053550
I was thinking I would set up a sheet2 with all the unique location codes, and then do a match between the location code on sheet 2 and sheet1 and use an offset function?
way to take a long list of the following data and change it from rows to columns?
First name Last name Address City, State Zip code
First name Last name Address City, State Zip code
That is how the data is currently displayed, spaces inbetween as shown but, long list of names and addresses. There is also an occasional 4th line usually for an address. My instructions are to put data in the following format:
I have train departure times stored in rows for each date as follows:
Mon02-Oct8.399.069.4210.0910.3611.12 Tue03-Oct8.399.069.42 Each time/date is in its own cell.
I have hundreds of entries like this and what I want to do is restructure them as follows (on a new sheet). For each date and time couple I want the date in one cell and the time in the the cell to the right. Also please note that not all days have the same amount of dept. times and this also needs to be considered. e.g...........................
