I have a spreadsheet that track orders. I want to be able to add a bit code to my current VBA script that sorts the data based on the values in a particular column (smallest to largest).
Current table looks like this:
Order Number
Date
Quantity
Colour
Priority
548562
12-09-2012
56
VBA formula
2
[code]....
I want to add be able to sort the data, smallest to largest based on Column E (tittle Priority)
I have about 3000 of these values per column. I need to sort the data so that column B and E are the same values. But the cells to the left and right of each B and E need to sort with it. Plus where there is a missing consecutive value I need a blank cell added...
View 8 Replies View RelatedI have two DB extracts for 2012 and 2013 with customer id's that no not repeat across the year - columns A and B. I need to arrange this from:
2012
2013
270806
330011
330011
350529
350529
369916
to:
2012
2013
270806
0
330011
330011
350529
350529
0
369916
So the major idea is that the customers that are extinct in 2013 would appear as 0 in the Column B; customers that are the same would be aligned vs the previous year and new id's would appear on the separate row.
I would like to copy and paste the values from a worksheet (HS-Detail) in a file to different tabs depending on the value in column A (Regions). I have a macro that works but it pastes the formulas and it takes quiet a while to run due to the number of records. How can I alter this to paste just the values and speed up the macro?
[Code] ......
I have a data that has several columns. But I need to separate the spread sheet based on one particular column values.
View 14 Replies View RelatedI have a macro that copies three sections of data from a word document and pastes it within the Excel worksheet. The column of data is pasted in a single column (data to be space-delimited and evaluated later), and what I would like the MACRO to do is separate the three sections of data based on the starting and ending values of the section and place them in different columns.
Section 1 : starts with the word "Team"
Section 1 : ends with the word "City"
Section 2 : starts with the word "Location"
Section 2 : ends with the word " Date"
Section 3 : starts with the word "Member"
Section 3: ends with the word "Age"
So section 1 would be pasted into cell C1, section 2 pasted into G1 and section 3 pasted into J1.
Each section will have vary in number of rows between the starting/ending values, so to cut and paste the section based on the starting and ending value is vital.
I'm looking to sort one column based on the values of another.
For example, if Column A has a list of names, and column B has a list of their ages, I just want to sort Column A based on the ages, but I want the ages column to stay as it is.
Is it possible to do this without a Macro? And if it requires a macro, how can I write a macro to make this work?
I need the close% column to auto sort from highest to lowest so that I can see at a glance who the top sales person is. I have conditional formatting for the top three but I would rather them auto sort by close%, can anybody help me with this. I have attached the file,
View 9 Replies View RelatedI am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture:
Column C Column D
111111 M
111111 M
111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
I want to be able to sort the columns in a worksheet based on their cell values, eg. column of text sorts A to Z and column of numbers sorts largest to smallest. Cells are formatted as General, number or currency but the sort button only offers A to Z or Z to A.
View 2 Replies View RelatedI have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
I have spent a few days searching through forums but cannot find examples that i have been able to successfully adapt ( because they are to complicated for my limited knowledge). I have a workbook with 5 sheets, sheet1 (current), and sheet 4 (archive) are the important ones. I need a macro to
A) copy rows from "current" to "archive" ( to the 1st empty row) if column S of "current" contains "Closed" ( the word is generated by formula).
B) The paste needs to paste special values and number formats ( want to lose formula but not conditional formatting).
C) data sort "archives" based on col A - this puts the newly archived data into correct sequence.Data sort lowest number 1st
D) delete the copied rows from "current".
e) give me a count of how many rows it deleted, (I have a macro to insert rows so will run that manually to recreate the correct number of emtpy rows (with formula and formatting) to bring the current sheet back to usual size.
I tried modifying a macro by RPaulson (based on cells on one sheet to cells on another), to work with entire rows but couldn't get it to work.
Found that paste special uses PasteSpecial xlPasteValues, but , and thats about it.
I have a thread in here called "Inputing data values automatically based on data value in another column". I have determined that I need to use the VLookup function.
View 3 Replies View RelatedIn A1 of Sheet1!, I need a formula that lists and sorts all the 'unique' values from range
ECWP!$O$6:$Q$1505
In a column I have loads of numbers like this except there are 5000 of them.
3
2
0
0
0
0
2
0
3
0
0
2
0
9
how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.
3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9
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
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 RelatedI have Sheet1 "MASTER" and Sheet2 "Area1" and Sheet3 "Area2" etc...
My MASTER sheet has a list of employee names and the areas they work in. I have employees working in different areas, and I want to pull a list of employee names from the MASTER working in Area1 (sorted on the MASTER sheet) to column A on Sheet2, then pull a list of employee names working in Area2 to column A on Sheet3, etc...
I want it to do this in such a way that if I add an employee to Area1 on the MASTER data, it will populate that employee in the Area1 Sheet.
So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.
I've attached a sample sheet. I want Column A in the Area1 sheet to reflect all names that show up on the Area1 LIST on the MASTER sheet, and nothing else. I'm using VLOOKUP to pull the rest of the data from the MASTER table.
There is a new sample workbook up now. Couldn't update it sooner due to site outage. I've removed irrelevant data to improve readability and focus on what I'm trying to achieve. Again, the main issue is scraping column E from the Master, and populating a list of all employees who match certain values in Column E on the Master in Column A of the other tabs.
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
Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
I have a small challenge with the following parameter
2 columns of data, the first A contains sets of calendar dates:
A1 27-4-2013
A2 27-4-2013
A3 27-4-2013
A4 28-4-2013
A5 28-4-2013
Op to 5000 lines but normally around 140 of them for each date in April 2013
Colum B contains wind speed data from a met tower
B1 15.0
B2 5.3
B3 7.9
B4 3.2
B5 32.4
What I need to find are the Highest, Lowest and Average wind speed for a specific date.
I have a NOW -1 function in field C1 I would like to use as the parameter to search for in column A so I get the last full calendar days full data set (10min values), so writing it up it sounds something like this: Look in column A line 1:5000 and if you find any date the same as in C1 then move the content of B to a new separate column D line 1:140 and when all 140 lines are extracted for the specific date then find the Highest, Lowest and Average wind speed between those extracted data.
do Different SORT based on data entry.
I have a macro in a workbook that looks like this:
Is it possible to sort by column, but only a specific portion of the data within the field?
For example, I have a set of numbers, 5026125120. I want to sort my spreadsheet by the 3rd and 4th digit only of that set of numbers (5026125120).
I can't find a way to sort my data. It looks similar to below. And I need to sort them based on the numbers from lowest to highest.
Tom Simmon (23.2)
Jason Paige (19.1)
Jeff Johns (14.1)
How to sort a column of data based on a custom list with more than 255 characters.
I have created a named range with 40 entries and then added data validation in the cells of column D using the above named range. However, I want to be able to sort column D in the same order as the named range but the custom data sort lists are restricted to 255 characters.
I just want to filter and sort the data based on names and hours.
Here it goes:
1. Filter Column F.
2. Filter it by: UK & M UK
3. After that, filter Column E in "Ascending order"
4. Count the data that are:
-starts 1:01AM - 7:59AM and display the total count in Sheet2, if there are no data in other specific time, then display a value "0"
For example for UK and M UK:
In Sheet 2, ("F5") = 3 as there are 3 data, G5=1, H5=0 and so on and so forth..
I've attached my file : Filter and Sort.xlsx
Sort Example.xlsx
I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.
A8 = Customer
B8 = Load Time
C8 = Delivery Time
Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"
I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).
The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.
The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM
Example is attached (desired sort is reflected).
I am trying to sort a column of data in ascending order but I am not getting the results I expect. An example of the data held in the cells is:
4972-1A
4972-10A
4972-2A
4972-1B
4972-3C
When I sort it in ascending order it sorts like:
4972-10A
4972-1A
4972-1B
4972-2A
4972-3C......................
Im am looking for a way of automatically sorting the data I have in column "C" (C2:C99 to be exact).
The data represents stock codes which are a mix of text and numbers, starting with 1 letter followed by numbers.
I would prefer something I can put in the sheet tab view code section, so that when any new data is added or altered it automatically sorts the stock codes in to alphabetical then numerical order
EG:
A10
A11
B13
B15
etc....