I want to stack columns in blocks. Have not been able to work out a VBA code for the same. Let me explain the need:
- There are N columns of data, for e.g., Col. A, B, C and D has Data (total of 10 rows) ... 4X10 layout
- I want to stack columns in blocks as follows:
AB
AC
AD
... 2X30 layout
I am looking for a way to that I can stack certain columns in an excel chart. For example the first column will be a total and the three next to it will be the breakdown of the first total. I want the second, third and fourth columns to stack on top of each other so that they will equal the height of the first. Is this possible? Also, I'm looking for the simplest solution because others will have to use this.
View 3 Replies View RelatedI have a question about putting data in the right order. I have this order in my excel files:
City A, City B, City C
10 20 20
20 40 50
15 30 40
45 20 10
50 10 10
20 30 40
I need to put them in order from top to bottom like this:
10
20
15
45
50
20
20
40
30
20
10
30
20
50
40
10
10
40
Is there any way to do this in a fast way. Because I have 440 cities and 13 numbers per city copying and pasting will take a long time..
I saw a post that needed a solution for moving blocks of cells in order to get fewer rows but more columns. The post is gone but I'd like to think my work wasn't in vain as the algorithm was more difficult than I thought. The problem was wanting make a 5000 X 4 grid into a 1700 X 12 grid, 55 rows and 4 columns at a time. This is sometimes done for visiblity or printing purposes to get more data going across instead of down. An example would be wanting to have more data accross on a print page and the page holds 55 lines of data.
For example, A56:D110 would be moved to E1:H55, A111:D165 to I1:L55, A166:D220 to A56:D110 etc. The code is somewhat generic so different blocks of rows and columns can be specified. To see this code work, enter the number 1 in columns A-D, then fill series -> step by one for a thousand or so rows. Then run the code.
Sub FewerRowsMoreColumns()
'Rearranges blocks of cells so the end result is more columns and fewer rows
Dim RowStop As Long
Dim ColStop As Integer
Dim RowStep As Long
Dim ColStep As Integer
Dim RowOffset As Long
Dim rw As Long
Dim col As Integer
ColStop = 12 'Enter the last column number you want the cells moved to
RowStep = 55 'Enter the number of rows you want to move at one time
ColStep = 4 'Enter the number of data columns you are starting with.....................
i think this will be simple for most of you. i need a macro that will move blocks of cells from one column into the succeding columns. say i have one column of 1000 values and i need to break that up into columns of 50.
View 6 Replies View RelatedAny way in which i can 'paste link' and then 'paste link' again without overwriting the first link. example i have a row (say A1 to H1) with varying values in Book1 Sheet1. i know how to copy and paste a link to that onto, lets say Book2 Sheet1. but now in Book3 Sheet1 i want to copy and paste a link from A1 to H1 over to Book2, but have both links open for auto updates.
View 3 Replies View RelatedI have a spreadsheet with a bunch of formulas in Cells B4:E100 in Sheet1. Many of the formulas will show up blank information in the event I do not have any data in Column A. I then have formulas which perform a similar function in Cells G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100.
What I need to do is to take the data from the last 4 sets of cells (G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100) and stack then one on top of the other into Sheet2 starting in Cell A2. I need the code however to be smart enough to realize that if there are blanks which exist in any of those sets of cells to skip over to the next set of data I which to copy and start pasting that set of information without leaving any blanks.
So far example, if I only have data between Cells A4:A25, I want the VBA code to go only through cells G4:Q25 and then start pasting without any spaces S4:AC25 and so on.
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
In a worksheet I have (not equal) blocks of data. In column A there is the projectnumber, in columns B-K there are data. Projects are devided by an empty row. All rows in some block have in column A the same value (projectnumber).
What I need is the following: I would like to add names to that blocks. The name must be equal to the projectnumber.
I have a list of numbers in Col.A which can be variable in length.The objective is to copy these numbers into Columns with 3 col gaps in between.So the objective is that these numbers are listed horizontally (with 3 col.gaps separating each nr).To achieve this I have written the script below. However, as the orig.list of p/ns in Col.A can be variable, I would like to perform the transposing in blocks of 10 numbers.The transposed end product should be listed 10 numbers in each row (with 3 col gap between)rather than in one row. how to modify my vba script to do the above?
Code:
Sub TransposeSkippingColumns()
Dim c As Range
Dim i As Long
[Code]....
I have 250000 lines of data and at the moment they are in seperate blocks of different sizes, and seperated by 5 blank lines.
For Example
112
1523
523
1523
*5 BLANK LINES*
12
23
*5 BLANK LINES*
344
4563
etc.
What I would like to do is give each block a number.
1 112
1 1523
1 523
1 1523
*5 BLANK LINES*
2 12
2 23
*5 BLANK LINES*
3 344
3 4563
The lines in between will come out eventually I just need them there as they are difineing the blocks of data.
I have a spreadsheet and I'm trying to add date to the left hand side. I want them to increase by one but there needs to be each date, in a column, 4 consecutive times.
Example
Jun 1 12p
Jun 1 4p
Jun 1 8p
Jun 1 12a
Jun 2 12p
Jun 2 4p
Jun 2 8p
Jun 2 12a
Jun 3 12p
Jun 3 4p
Jun 3 8p
Jun 3 12a
This is for a Dialysis flowsheet and I really need this to work.
I am trying to calculate the amount of time in blocks, using a pre-determined spread. For example:
AM block is anytime between 0600-1230
PM block is anytime between 1230-1830
Evening block is anytime between 1830-0000 and the Overnight block is anytime between 0000-0600
Sounds easy enough, yeah? Let me paint an example of how I want it to calculate though:
If an employee starts at 0600 and finishes at 1400, then the AM total is 6.5 and the PM total is 1.5.
If an employee starts at 1100 and finishes at 1900, then the AM total is 1.5, the PM is 6 and the Evening is 0.5.
If an employee starts at 2200 and finishes at 0800, then all of the 10 hours must report to the Overnight total, even though some of them fall within the Evening and AM blocks.
I just can't seem to get each block to show the accurate number of hours...and to bring in the rules that I require.
a way to comment out a block of code just using a begin and end tag, as opposed to using an apostrophe (') for evey line?
View 3 Replies View RelatedI have a worksheet that collects information from another using complex formulas.
In column A I have codes i.e. PRD001A, PRD001B etc. A block of codes starts at A and can go up to Z. i.e. PRD001 could start WITH PRD001A and finish with PRD001N then there is always a PRD001W and PRD001Z to finish with.
PRD002A would follow PRD001Z and might only have A, B, W & Z codes before PRD003A.
In column K is the sum of columns D:J.
What I want is a macro that will look at column A & K and if all OR some of the codes for say PRD001 are >0 don't delete any of the rows for that group of codes.
Then look at column A & K and if all of the codes for say PRD002 =0 delete all of the rows for that group of codes.
I have a master tab that has data grouped as follows
Column 1
title of a section (written once then left blank for the rest of a section)
Column 2
numerous descriptors (each in a new row)
Column 3
The response to the descriptors in column 2
I want to link entire sections from column 2 and 3 into different excel files so that if I make changes to column 2 or 3 in the master tab, the changes are made to all tabs. If there is a way, I'd like it if I add a row to the master, it is automatically added to the others.
When cross linking cells from one file to another, I sometimes lose the source link to rearranging cells on one of the other spreadsheets. Is there a way I can "lock" the source cell so that it isn't lost as a reference?
View 1 Replies View RelatedWhat I'd like to do is to have the block of rows starting from "Agent:" and ending with "Total" copied into a separate worksheet and then have the worksheet named using the agent's name. I have highlighted the data in yellow in the attached reference file. I would like to have this done for all 58 agents. I could copy and paste manually but that wouldn't be the smartest way to go about this. I figure I probably need a looping structure for this but I am not yet proficient enough to figure this out quickly. I am supposed to have this done today.
Edit: The last part of this would be to have blocks of rows from the "sections" worksheet which use identical agent names also pasted below the data from the "questions" worksheet. I have attached the workbook I am working on now cutting and pasting manually.
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up...
J F M Ap M....
acct 1
acct 2
acct 3
I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here...
2
2
2
2
33
33
33
4
4
4
4
4
4
etc.
Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no.
so that it looks like this
2
2
2
2
46empty rows here.......................
I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.
On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up... I can SEND THE SHEET IF YOU WISH....
J F M Ap M....
acct 1
acct 2
acct 3
I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.
I am attaching a sample file which best explains what I am trying to achieve.
I have used some code that Kris gave me a couple of weeks ago and changed it to insert a row between each block of information. Have I got it right?
Sub InsertRows()
Dim lRow As Long
Dim r As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Application. ScreenUpdating = False
Columns("M").Insert
[m3] = "temp1"
Range("M4:M" & lRow) = "=LEFT(A4,5)"
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(r, 13) <> Cells(r - 1, 13) Then Rows(r).Insert Shift:=xlDown
Next r
Columns("M:N").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:= _
"H:HOME imcEXCELCOUPON_COUNT5_MAYREFORECAST.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.SelectedSheets.PrintOut copies:=1
End Sub
I have the follwing data in A1:T17
3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
..................
I want to compare the first set of 20 numbers (A1:T1) with the second set (A2:T2) and check how many numbers match. ** If the matched numbers
are >=10 ** then list them to the right of cell W1. In this example, 10 numbers matched are 4,9,10,21,35,47,64,72,74,75. List them in W1:AF1.
Now compare (A1:T1) with (A3:T3). In this example, 11 numbers matched 4,9,10,21,33,41,47,57,6,72,74 list them in W2:AG2.
Now compare (A1:T1) with (A4:T4). 10 numbers matched 3,4,10,11,21,32,33,35,60,69 list them in W3:AF3.
Compare (A1:T1) with (A5:T5). Here only 5 numbers matched 9,21,64,69,75 (Which is ** less than 10 ** - does not match the criteria) DO NOT LIST
this.
Compare (A1:T1) with (A6:T6). 11 numbers matched, 3,4,7,10,21,33,37,47,57,69,75 list them in cell W4:AG4.
.................
Sub Compare()
Dim r As Integer, rr As Integer, c As Integer, cc As Integer, opc As
Integer, opr As Long
opr = 1
For r = 1 To 16
For rr = r + 1 To 17
opc = 23
For c = 1 To 20
For cc = 1 To 20
If Cells(r, c) = Cells(rr, cc) Then
Cells(opr, opc) = Cells(r, c)
opc = opc + 1
End If
Next cc
...............................
Next r
End Sub
The above code runs fine but If I increase my rows from 17 to 1200 it takes 3 1/2 hours to complete. Can it be reduced?
I would like to copy specific cells (all in one block) to another worksheet, then do a bunch of other things with the data (these steps work already), then go back to the original worksheet and copy the next block of data, repeat about 50 times, then stop at the end of the list.
I can't attach a file as I can't get it under the file size limit but the rows to move (just to any worksheet in the workbook, from there I can manipulate and move as required) each time are row 1 plus all those pertaining to each subject group. So for group 1 it would be row 1 plus rows 6 to 28 - for group 2 row 1 plus rows 29 to 51 - group 3 row 1 plus 52 to 74 etc. If I can just get looping through chunks of data and moving them around under my belt
I have a list of data (as shown below under Input), which I would like to sort them according to their block number (as shown below under Output). The hurdle is the sorting should be able to cater for missing fields. Also, note that the sector can be recurring, that is "P,P,Q,Q", and then follow by "P,P,Q,Q" again.
INPUT
Block No.SectorData
11P19
22P
33P39
41P18
52P21
63P38
75P22
85P24..............................
I have a series of spreadsheets that represent the rosters of teams in a league. At the end of the league season, some teams qualify to advance to a final event, and some do not. I start with a file of all the teams for that level of competition. This list comes from a program that creates it's output in an excel '97-2003 format (and I can't change the way the data comes out of this program). I modify the data a bit by removing data from merged cells and rename that worksheet a 'Raw Data'. In the past I have manually copied and pasted the data for the teams that advance to a sheet called 'Roster Data'. I am hoping there is a way to automate this part of the chore.
There must be 6 blank lines between the team names on the 'Roster Data' tab, but some teams have more members than others, so they are not spaced the same on the 'raw data' tab. Is there a way to select teams from the 'Raw Data' tab and have only the selected teams show on the 'Roster data' tab, AND have the spacing become uniform?
I've managed to get coding that allows me to insert single rows dynamically based on a cell value. Example is if I enter a value in cell C3 the coding inserts the same number of rows below.
I've also created a simple macro that inserts a multiple row block under the active cell, complete with formatting, text and some merged cells. Example is I run the macro and it will insert a block of 3 rows, complete with formatting, borders, text and merged cells.
My challenge is to bring these two elements together. So enter a number in a single cell which will then insert the same number of row blocks, complete with formatting etc. I've added a file to sample what it is I'm trying to achieve. I can add the coding I already have as well.
Dynamic list sample.xlsx
I have cells of block addresses that are delimited (tab I believe - there's a
small square that appears).
I need to split the addresses out into separate columns for each line of address. I tried using the Text to Columns wizard as suggested by Excel Help, but ended up with only the first line of address being posted in the cell to the right (the rest of the address was nowhere to be seen).
I'm sure I've done this before but can't for the life of me remember how I did it.
