Sort Records Within Data/Range Blocks

Apr 19, 2008

I have around 50,000 records in a data file within which groups of records are contained in blocks delineated by date/time. e.g.;

A B C D E

10/03/2008 13:15 a 100.0
10/03/2008 13:15 b 2.8
10/03/2008 13:15 c 50.5
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:25 75.2 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 3.5

etc

As you can see, while the file is ordered by date/time in Column A, the values in column E are randomly ordered.

My objective is to maintain date time order but sort the values in Column E into an ascending order within each block. So, for example, using the details above I would want it to look like;

A B C D E

10/03/2008 13:15 b 2.8
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:15 c 50.5
10/03/2008 13:15 a 100.0
10/03/2008 13:25 3.5 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 75.2

Note that there is data in columns B to D which would need to be sorted in line with Column E. So, all records in the block, cols A to E are sorted to give the above order for each time block.

My problem is that I'm not sure whether a basic sort can do the job or whether a macro is needed. I'm using Excel 2003.

View 4 Replies


ADVERTISEMENT

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

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

Sort Multiple File Data & Copy Matching Records To New File

Feb 13, 2008

This is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.

Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar

View 3 Replies View Related

How Many Records To Sort Without Crashes

Oct 3, 2009

I have a column of data that is an 850,000+ list of phone numbers. I need to peridoically insert another 10,000+ at a time and see if there are duplicates. Using conditional formatting to highlight the duplicates with a colored cell, and then sorting those records to the top is what I want to do, but it's too much for Excel to handle, and it hangs. It looks like I need to break up the 850k records into smaller chunks. Anyone know what an average pc with Excel 07 can handle? It's a 2.8ghz w/2gb ram.

View 6 Replies View Related

Sort Records In Spreadsheet

May 10, 2007

I am using vb6 and I need to Sort records in spreadsheet based on items in column 7. Actually, I want to sort the spreadsheet before executing the count process below. Note: the code below works perfectly, I just need to sort the spreadsheet before counting.

Selection.Subtotal groupBy:=7, Function:=xlCount, TotalList:=Array(7), _
PageBreak:=False, SummaryBelowData:=True

In otherwords, I want to sort the records based on the 7 column before executing the above routine.

View 2 Replies View Related

Sort Records That Are 3 Lines By 18 Columns

Mar 1, 2008

I have data records that are 3 rows long by 18 columns (A thru R) wide. People's 1st names only exists in the 1st row, 2nd column (B) of each record. The 1st record starts on row 5.

I need a VBA procedure that when run, it will sort the data alphabetically by the 1st row, 2nd column (B) of each data record. The 3 line data records looks like this starting on row 5:

________A__|_____B___|____C____|___D___|___E___|___F___|___G___|___H___etc
1st row _18_|_John____|_730-6853_, etc
2nd row ____|_Doe____|_238-4835_, etc
3rd row_____|_Sales___|_328-4893_, etc
1st row _32_|_Jack____|_684-3812_, etc
2nd row ____|_Jones___|_232-4954_, etc
3rd row_____|_MFG____|_238-5355_, etc
1st row _18_|_Jimmy___|_485-2294_, etc
2nd row ____|_Smith___|_223-9110_, etc
3rd row_____|_Maint___|_368-6381_, etc

If only above 3 records exists, when sorted Jack's record starts on row 3, Jimmy's record starts on row 6, John's record starts on row 9, etc

We can have as many as 15 records (3 lines long) that goes thru row 49.

View 9 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

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

Data Sort For Variable Range

Apr 6, 2009

I have a recorded macro to sort Data with 3 sort fields but has a Fixed range. The Data will increase each week so I need to modify to account for growth.

View 2 Replies View Related

Sort Dynamic Data Range

Apr 8, 2014

How might the code below be changed to handle any number of rows? Right now it's set (from a recorded macro) to go down through row 237, but I need it to sort anything from just a few to maybe a thousand.

Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C2:C237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("K2:K237"), _

[code]...

View 5 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

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

Clubbing Blocks Of Data For Two Entities In A Column Chart

Mar 11, 2009

Clubbing blocks of Data for two entities in a Column Chart

I need to show graphically the comparison between two LOAN amounts for a Housing LOAn..

I am almost done with the MATH part part of it, however need to compare the results Graphically...

I have two Blocks of DATA primarily: ...

View 9 Replies View Related

National Insurance Number Validation On Blocks Of Data

Nov 1, 2007

I have a spreadsheet where data is pasted on from spreadsheet supplied by various external companies. One of the main problems we're having when validating the data is an incorrect NI Number and I'm hoping to find a way of getting this automated.

What I would like to do is, when a block of text is pasted into our template, a block of code to look through the selection and check column S for a valid format NI no (eg AB123456C). I have some code (below) that works when changing an individual cell, but nothing that works when pasting in blocks of text.

Private Sub Worksheet_Change(ByVal Target As Range)

stringvalue = Target.Value

statuschange = 0

Application.EnableEvents = False

'Check for text only cells
If Target.Column = 19 Then

View 9 Replies View Related

Fill Series That Combines Data From 2 Sheets In Blocks Of 10

Jun 5, 2006

I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.

Part 1:

I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.

=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)..................

View 6 Replies View Related

VBA - Copy Data To New Range Then Sort And Remove Duplicate

Jul 4, 2014

I want some example of VBA that could copy data (A1:B15) to new range (D1:E15) then sort and remove duplicate.

SWS
Central Pinklao FLG

RMA Trading
Thonglor 13

SWS
IT Square

[Code] ......

View 1 Replies View Related

Dynamic Names Pointing To Blank-Delimited Blocks Of Data

Nov 10, 2012

I have a column of data that is blank-delimited into five blocks. The size of each block will vary, since the data comes from a web query. A blank (empty) cell tells me where a block ends and the next one begins.

I would like to set up five dynamic names (e.g. Block_1, Block_2 and so on), each of which will reference the corresponding segment of data.

What would be an elegant way of defining the dynamic names?

View 6 Replies View Related

Sort In Ascending Sequence, A Range Defined As RTrainRow Which Is A Row Of Text Data

May 14, 2007

I want to do is sort in ascending sequence, a range defined as rTrainRow which is a row of text data. Each cell in the row can contain space, blank, one Alphabetic character or a special character such as $, %, (, +, etc.

View 2 Replies View Related

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View 3 Replies View Related

VBA Code To Sort Data - Run-time Error When No Data To Sort

Mar 4, 2010

I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.

Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer

zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

View 9 Replies View Related

Getting Top 10 Records From A Range

Jun 17, 2014

I am looking for a formula to give me the top10 records from a range.

Please take a look : Top_10.xlsx‎

View 6 Replies View Related

Find Duplicate Records Based On Multiple Columns But Keep Records

Aug 10, 2014

I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).

Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.

View 7 Replies View Related

VBA To Rearrange 11000 Records Into 550 Rows (20 Records Combined Into Single Row)

Apr 25, 2014

Book1 and Book2 are workbooks that I have modified in order to protect private information.

Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).

Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.

Book1.xls
Book2.xls

View 5 Replies View Related

Count Records Within Specified Time Range?

Jan 16, 2013

I need to count the number of records having hospital admissions within one year prior to the index admission. The list of all admissions is in one worksheet, the index admission in another worksheet. They can be matched on ID number.

View 3 Replies View Related

Excel 2013 :: Averaging Rows Of Data Within Larger Blocks Of Datasets - Skipping Rows

Jul 16, 2014

I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.

Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.

I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.

Is there a formula/script for me to skip the correct number of rows to the next data block?

I have attached my spreadsheet to this thread. I am using Excel 2013

FeS_Kinetics.xlsx

View 1 Replies View Related

Count Unique Records In A Filtered Range

Jan 21, 2009

Is it possible to count the unique entries in a range based on the results of a filter that has been applied? I basically have a column with 2000+ cells that contain some matching values and I only want to count the unique entries. This will need to be a dynamic count as well as the filter criteria can and will change all the time.

View 14 Replies View Related

Function To Sort A Range By The Values In A Specific Column And Return The Range

Mar 25, 2009

I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.

My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer

For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function

View 9 Replies View Related

Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

View 9 Replies View Related







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