Transposing In Blocks Of 10?

May 19, 2014

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]....

View 8 Replies


ADVERTISEMENT

Add Names To Blocks

Oct 9, 2008

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.

View 7 Replies View Related

Numbering Blocks Of Data.

Nov 2, 2009

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.

View 3 Replies View Related

Stacking Of Columns In Blocks

Apr 19, 2012

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

View 7 Replies View Related

Increase Date By One In Blocks Of Four?

Jul 8, 2014

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.

View 6 Replies View Related

Calculating Time Blocks

Apr 11, 2009

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.

View 9 Replies View Related

Commenting Out Blocks Of Code

Apr 11, 2006

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 Related

Remove Blocks Of Rows

Apr 27, 2006

I 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.

View 9 Replies View Related

Linking Blocks Of Cells Across Sheets

May 1, 2014

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.

View 2 Replies View Related

Locking Specific Cell Blocks

May 7, 2014

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 Related

Copying Blocks Of Rows Into New Worksheets

Jul 1, 2009

What 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.

View 4 Replies View Related

Counting Blocks Of Highlighted Cells

Feb 18, 2010

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

View 9 Replies View Related

Sort Info In Column Into 50 Row Blocks

Jul 20, 2007

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.......................

View 9 Replies View Related

Working With Time: Blocks Every Whole Hour

Aug 1, 2007

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.

View 9 Replies View Related

Counting Blocks Of Highlighted Cells

Feb 18, 2010

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

View 9 Replies View Related

Summing Uneven Blocks Of Rows

Apr 21, 2006

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.

View 9 Replies View Related

Insert Rows Between Blocks Of Information

May 11, 2006

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

View 3 Replies View Related

Compare Blocks Matrix Of Data

Aug 29, 2006

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?

View 9 Replies View Related

Copy Data Range Blocks

Jan 8, 2008

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

View 5 Replies View Related

Sort Range Data Blocks

May 9, 2008

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..............................

View 2 Replies View Related

Transposing The Data

Aug 7, 2009

I have a user form which I would like info entered into various text boxes to be assembled in a way which can be copy and pasted. See the attached example to see the results I am looking for.

View 2 Replies View Related

Transposing A Data Set

Nov 18, 2009

I have been trying to transpose a data set. Transpose formula is not working with this data set. How should i proceed ?.

View 4 Replies View Related

Transposing An Array

Nov 18, 2009

I want to transpose the data set. I am using the following formula

HTML
INDEX(Dataset!$B$2:$Y$13366, MATCH(B$1, Dataset!$A$2:$A$13366, 0), MATCH($A2, Dataset!$B$1:$Y$1, 0))

But there seems some problem with the reference of year (I want the range will change From A2:A15 then it will start from A17:A31 like this until my row ends at 13366).

View 4 Replies View Related

Transposing Links

Nov 10, 2005

I have very large sections of data which I need to transpose as links.
Problem is that the copy/paste links function cannot be followed up with the
copy (the linked cells)/transpose option, without having the transposition
screw the links up.

View 9 Replies View Related

Picking Blocks Of Data To Copy From One Sheet To Another

Jul 22, 2014

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?

View 13 Replies View Related

Dynamically Adding Row Blocks Based On Cell Value

Apr 2, 2014

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‎

View 2 Replies View Related

Split Address Blocks Across Multiple Cells

Feb 20, 2006

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.

View 13 Replies View Related

Large Dataset - COUNTIFS On Blocks Of Text

Nov 3, 2011

I have a large dataset. under the heading Theme there are blocks of text, ie not single words. i have created a simplified version below

Sample table:

StateThemeVictoriatradeNew South Walestrade, ownershipNew South WalestradeVictoriaownership,
test textVictoriatest text, tradeVictoriatrade, ownershipVictoriaownership, trade

I want to count, for example the number of times the words 'ownership' OR 'trade' appear for Victoria.

The formula i am using is doubling up on the last 2 entries.

This is what I have:

=SUM(COUNTIFS(A2:A8,{"Victoria"},B2:B8, {"*ownership*";"*trade*"}))

View 9 Replies View Related

How To Copy Blocks Of Formulas Or Conditional Formatting

Sep 11, 2007

Lets say I have a block of cells (lets say A1:E5) that all reference the value in the upper left hand cell (A1). Could be a formula by itself or one embedded within a conditional format. I reproduced the formula and formats in the A1:E5 block by using the absolute cell reference $A$1.

I now want to reproduce that entire block (including the referenced cell A1) multiple times on the sheet, but the catch is that each new block must reference the cell in IT'S OWN upper left hand corner. (e.g. a block located at F10:J15 must use as it's reference F10, not A1).

How can I quickly reproduce those blocks? I have done a search and replace to eliminate the $ symbols within the formulas to make them relative, but that doesn't seem to work within conditional formats.

The issue right now is I am trying to apply conditional formats to a sheet I already constructed. I don't want to overight the formulas in the UL corner, so I am trying to copy and paste the conditional formatting only. Can't seem to figure out how.

View 9 Replies View Related

Move Blocks Of Data In 1 Column To Rows

Nov 15, 2006

Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.

For Example:

Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved