Data Sort By Header
Jul 19, 2007
what is the code to sort a spreadsheet in VBA code by HEADER rows?
i have 2 rows i want to sort by: first by Column D row 1, then by column C row 1.
Cells.Select
Range("C1").Activate
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("C2").Select
selects all cells, sorts, then de-selects.
View 3 Replies
ADVERTISEMENT
Apr 30, 2008
I have two header rows on a sheet that I need to sort starting on Row 3, Column A.
The sheet is A-K and rows 1 and 2 are headers
Row 1 has merged cells of A-F which says "Information" and then G-K " Tracking Section"
Row 2 is broken up into individual sub-headers in columns, A-K each one having a bit more info for instance:
Under the "Information" merged header in row 1 there is
A2 Name
B2 Work Center
C2 C/O date
D2 Due CC
E2 Due MX
F2 Date Received
and G-K in row 2 has different sub-headings.
I have tried to use my normal code, which is great for one header, but it ends up sorting the sub-header as I call it...
View 5 Replies
View Related
Oct 7, 2009
In the worksheet "Batting and Pitching Register", I'd like to be able to sort the data in the two charts (APBA Batting Register and APBA Pitching Register) by simply clicking on the cells in the respective header rows.
I've set up ranges (Batting and Pitching), but have had no luck moving beyond that.
View 9 Replies
View Related
Jul 9, 2014
I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.
My Source excel file looks like:
ABC A-101 B - 202
DEF B-203 C - 100
The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.
The output I want is:
A B C D
ABC 101 202
DEF 203 100
There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.
In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc
View 1 Replies
View Related
Jan 17, 2010
Attached is an example page. My goal is to be able to click on one of the header cells, and have it sort that column from high to low, and if I click that same cell have it sort from low to high. I know how to do this by making a command button, but, I want to do it by clicking on the cell itself. I just dont know how to do the onclick to execute the code.
View 3 Replies
View Related
Apr 14, 2008
Came across a spreadsheet that my firm created, where you can click on the column header and it automatically sorts ascending order for the column without messing up the other columns. Then if you double click it again, it will sort in reverse order.
View 9 Replies
View Related
Jul 25, 2007
I have a spreadsheet which contains a list of chemicals in Column A, and a list of numbers in Column B. The chemicals are separated into groups. I am trying to create a macro which sorts the chemicals in their groups by column B, so that the lowest number is at the top. I have 8 groups, and the number of lines in each group may vary, so I need to make the code dynamic.
I have attempted it. The way I thought it could be done was to search for the first group header, then save the address. Do the same for the next group header, then offset that by -1 row and 1 column, then sort using these two addresses as the range. The first part of the code works, where I find the address for both group headers, but everything after that doesn't work.
Sub Sort()
Dim CNMT(8) As String
Dim j As Integer
Dim fromRow As String
Dim toRow As String
Dim rng1 As Range
Dim rng2 As Range
CNMT(1) = "TPH Fractions"
CNMT(2) = "BTEX & MTBE"
CNMT(3) = "PAHs"
CNMT(4) = "VOCs"
CNMT(5) = "SVOCs"
CNMT(6) = "Metals"
CNMT(7) = "Inorganics"
CNMT(8) = "Pesticides"
For j = 1 To 8..................
View 2 Replies
View Related
Feb 25, 2009
I would like to create a list covering multiple columns.
I would like to have 5 columns which will contain the same value/ (word).
If I select,that value from the list, I would like to have each row the word is
found be displayed regardless of which row it is found.
View 7 Replies
View Related
Aug 6, 2008
In row 1 I want to have the names of servers, so we would have A C D E
Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.
Then B would have under it Start, End, Data, Time
And so on for C D ...
I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be
A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.
I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.
View 9 Replies
View Related
Apr 3, 2014
I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.
i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2
(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4
test.bmp
View 2 Replies
View Related
May 7, 2014
find macros to sort fixed ranges but not an entire column with existing header rows from 1 to 4.
Added to that is the need to keep the code compatible with Excel 2003 even though I have Excel 2010 at office (it's a file that needs to be "openable" in both versions so the vb code needs to be in XL 03.
Column currently starts at C5 but goes down to C47, at this point. But it's a growing list. Some of the rows are blank at the bottom, too.
View 2 Replies
View Related
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
Jun 23, 2008
Is there a way to link a header or a portion of a header to a specific cell?
View 14 Replies
View Related
Jul 4, 2014
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
View 1 Replies
View Related
Aug 3, 2014
I have attached the excel sheet here for reference.
I would like to sum up the values across rows, so long as they have the terms "FQ1", "FQ2", "FQ3" or "FQ4" in the first row.
In other words, for the row "Total Revenue", I would like to sum up the quarterly values, using VBA, since the number of quarters increase with time.
How should I code up a VBA for that?
View 4 Replies
View Related
Nov 18, 2011
I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.
in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.
In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.
View 4 Replies
View Related
Dec 12, 2008
I have a spreadsheet template whose Left and Center Print Headers are set to static values.
I would like to pass a single cell's data to the Right Print Header whenever I print the given spreadsheet.
I would also like the Right Print Header to refresh itself each time I print the sheet so that any possible change in the referenced cell will be accounted for.
View 6 Replies
View Related
Apr 17, 2009
I need a bit of VBA code which will run automatically when a csv file is opened. The code needs to move anything after column F onto the next row and repeat until there is no more data. The data is generated by an external program but is put on the same row (not sure why it does this). I have included an example of the csv file (the data in it is garbage so ignore it) for you to look at. the header row which needs inserting needs to be:
A1=Status
B1=Job No.
C1=Date
D1=Name
E1=Room
F1=Description
Hope this is enough information for you. bear in mind the amount of data being generated could be quite large. it has to be run automatically in order to be used in seamless mail merge.
View 2 Replies
View Related
Nov 15, 2012
On sheet1 I got a lot of columns with data, starting from row 8, which is the header.
On sheet2 I'd like to retrieve the filtered data, based on the value mentioned in cell A1 (on sheet2)
So if for example cell A1 on sheet2 shows "Peter", the code should filter the data on sheet1 with "peter" in the header.
The filtering should be based on cell color (red in this case)
The filtered data should then be copied to sheet2 starting from A3
View 3 Replies
View Related
May 19, 2009
I have managed to open an HTML file from IE and start importing some data from the HTML source to an excel table.
The problem is that I need to get access to some information in a function that is in the HTML header and I do not know the syntax to use.
I am defining the HTML document using ".Document.body.innerHTML". But I need to find the syntax that is equivalent to ".Document.head.innerHTML".
With HTMLdoc
.Visible = True
.Navigate PageString
Do Until .ReadyState = 4: DoEvents: Loop
End With
View 9 Replies
View Related
Jan 31, 2014
I am trying to match headers of different files and copy data.If the one of the header is not matched then a new column with the non matching header should be created and the column has to be copied
Ex: file1.xlsx has AA BB CC as header
file2.xlsx has AA BB DD EE as header
file3.xlsx should contain AA BB CC DD EE i.e data from file1.xlsx and file2.xlsx
I have more than 300 files how can I copy all the files to one excel file by matching headers ?
View 1 Replies
View Related
May 13, 2014
I have a list of S/N with Pass/Fail result next to it. A specific S/N can appear several times in the list:
SN...Result
-------------
111...FAIL
111...FAIL
111...FAIL
111...PASS
222...PASS
333...FAIL
333...FAIL
When creating a pivot table of SN vs. Result (count) I get:
****|FAIL...PASS
-------------------
111..|...3.....1
222..|..........1
333..|...2
I want to get the FAIL count of SN in which PASS > 0 (First two rows)
BUT I can not figure out how I can filter based on the values of the pivot table itself (rather than the values of the original list). When I try to use 'Result' field as report filter, I can not get it as a Column label (and vice-versa). In this case I get:
I get:
****|PASS
------------
111 | ...1
222 | ...1
Desired:
.......|..FAIL..PASS
-------------------
111 |...3...1
222 |........1
Desired (alternative):
......|..FAIL
-------------
111 |...3
222 |...0
View 1 Replies
View Related
Dec 3, 2012
I'm using Excel 2007. I know how to protect specific cells within a worksheet, but I have not been able to find any information online pertaining to locking or protecting inserted header/footer data, even if I protect the worksheet.
I'm not concerned about setting specific VBA commands to ensure the header/footer prints, but rather preventing other users from being able to edit the data upon distribution of the worksheet.
View 3 Replies
View Related
Dec 24, 2012
Can I create a PivotTable with two columns of data for the same Column header?
I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.
View 2 Replies
View Related
Apr 20, 2012
I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.
I'm using Excel 2007
The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)
The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .
It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1
View 2 Replies
View Related
Apr 11, 2014
I am a macro newbie and I think this is beyond me.
I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.
So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.
There must be a way to do this that is easier than searching the internet all afternoon again
View 3 Replies
View Related
Oct 18, 2013
I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.
I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.
View 14 Replies
View Related
Oct 7, 2013
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
May 22, 2012
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
View 6 Replies
View Related
