Sorting Data Using Macros
Apr 9, 2008
Unsure how to start this off, any help would be great.
I have a spreadsheet with raw data in it, and am looking for a way to sort the raw data into separate sheets within excel using a Macro / VBA.
Here are the different columns of data I would like to copy into a different sheet.
Man Ord No.
Product No.
Operation Description
There is a Work Centre column I would like to use this column as a way to select the different rows of data.
So if there are five different rows of data for ACT#01 in the Work Centre column I would like it to copy the rows of data, delete all the columns that are not needed that’s all of them except for the above three, then add three new columns below.
Add a column called “Run Time
Add a column called “Resrce Name”
Add a column called “Available”
Work Centre will become the name of the sheet in Excel
The titles of the columns will have to be added in also, via the macro.
See the example excel file that I have started to make by hand, this is just one part of the process that am looking are automating there are other parts as well but just getting started for now.
View 14 Replies
ADVERTISEMENT
Feb 18, 2014
I have a large excel file with the following columns:
unique ID | Date | Time | D or N | Open | High | Low | Close | Volume
I would like to figure out how to create a table showing:
Date | Highest High of the Day | Time the High occurred | Lowest Low of Day | Time Low Occurred
I think this can be done in a macro where I sort first by the Date column then By the High column but I can't figure out how to get the rest.
View 2 Replies
View Related
May 30, 2014
I have a macros set up to sort a group of figures. I would like to add an IF function to weed out certain items that do not fit the criteria and therefore would not be sorted but left at the bottom of the sheet.
View 5 Replies
View Related
Sep 2, 2006
I am attempting with a simple spreadsheet he can use on the golf course on a Treo (so no macros I believe can be used). I am want him to be able put the order next to a list of people's names (the same 8 play) and then a dynamic sort will take place listing who goes first. Everything works great except it one or more people do not play then the sort lists them as "N/A". Instead of "N/A" I want it to show up as a blank.
View 2 Replies
View Related
Jan 27, 2014
I have attached an example spreadsheet with two tabs. The first tab (Matrix) shows raw data scores - there are 28 measures in Column A and Participants in Row 1. There are always 28 measures but participants can vary. What I need to happen is to sort each measure in a Lineup tab i.e. rank all the participants listed in the Matrix tab from lowest score to highest score for each of the 28 measures. Obviously this can be done manually but a VBA/macro solution will be so much faster and easier. Unfortunately I have to keep the data in this format so the macro will need to transpose the data to get into the formt in the Lineup tab.
View 9 Replies
View Related
Dec 20, 2012
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies
View Related
Jan 30, 2014
I am trying to create a "Master Sheet" where I enter in the column data and after I have entered my data for each row, I can select the button which toggles the macro to run. I have it built to build new sheets as new clients are obtained. My problem is after I have a sheet that has client's data I cannot get new data to add itself below the data that is already there. I want each client's sheet to keep adding rows as more data comes in. My current macro is :
[Code] .....
Attached File : Data Entry Macro.xlsx
View 3 Replies
View Related
Oct 26, 2013
I copy/paste the data from this website [URL] ........
How I can sort all the data based upon the "Date"? The date data is not recognized when I copy/paste.
View 1 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Sep 26, 2009
I need to have cell data on sheet 1 to populate cells / rows on sheet 2 when the data is sorted on Sheet 1.
So let me explain: I have workloads on each row in Sheet 1. Column A has a list of people that I assign to each workload. After assigning a name in Column A to each row, I sort Column A by the user to print out only their workloads.
Currently, we have a paper worksheet where they manually write in their workload. I have replicated this worksheet on Sheet 2 and was wondering if I could use the data in Sheet 1 after being sorted by user, to populate the worksheet on Sheet 2 ?
View 10 Replies
View Related
Jul 23, 2012
I have a spreadsheet where I'm copying data from one tab to another and also copying values down in certain columns. I have a "chunk" of data already on the tab I'm working on and want to copy an additional "chunk" below it. I want to fill a whole column with the same data but only in my second "chunk". So in this instance...it's an account number and all of the rows in the first chunk have account number 42243 and in the second chunk, I want it to be 78300. The account numbers will always be the same but the number of rows will not be. So for the first chunk, I was able to use the following code...
Code:
'Copies account number 42243 into column C for all lines
MyCount = Application.CountA(Range("a:A"))
Range("C2", "C" & MyCount).Value = 42243
Range("C1").Select
And right now, I have the following in for the second chunk. But obviously what this is doing is putting 78300 in the account field for the entire column, not just the second part.
Code:
'Copies account number 78300 into column C for all lines
MyCount = Application.CountA(Range("A:A"))
Range("C2", "C" & MyCount).Value = 78300
Range("C1").Select
View 5 Replies
View Related
Jan 12, 2008
I have five columns of data A to E. In each row there can be data in either 2 3,4 or 5 of the columns. What I want to do is essentially condense the data. So in columns, say, G,H,I J,K if there is data in column A then It goes into column g, if not it asks if there is any data in column b and so on. Then for H it does the same but one on. I have attached an example to show what I would like to happen.
View 9 Replies
View Related
Mar 14, 2009
I am encountering numerous problems compiling a macro to sort some pretty messy data. Hoping some of you could take a look and offer some suggestions....
The Data I need sorting is shown in the 'DATARAW' worksheet, this comes in every day and shows positions for each traded period. Usually 48 periods which I have highlighted although the data download could somtimes consist of 46 or 50 periods.
Sheet 1 (DATA RAW) contains data which I download daily.
Sheet 2 (Data Sort) is how I need it to look after sorted (I only need the data in DATARAW replacing, not another worksheet creating).
So for example:
Where column A = SP8 and the adjacent column B = 1 I would need:
All values which = ECN to concatenate the value in B to the reference in column B directly adjacent to the value next to it, until the folowing rows are presented:
So for instance the first value reported would be 131964, second 131979 etc... until the following text is shown in the rows below
IDD
CAT
ACE
ACT
ACE
ACT
When these appear I need the entire rows to be deleted.
Then basically the same for the next period where SP8 appears in column A and 2 appears in Column B.
also, the final result should contain no formulas so it can be referenced by lookups etc... also, all numeric colums should be stored as number so excel doesn't get confused.
This will basically repeat itself until the end of the file showing the same split as aove but with ZZZ as a footer. Which I also need to delete. :-S
IDD
CAT
ACE
ACT
ACE
ACT
ZZZ
Rows containing the values below also need deleting.
AAA
NOH
DSI
AED
I've used colours in the attachment but the actual file doesn't have any.
View 11 Replies
View Related
Jun 30, 2009
I use my VBA code to sort the data in certain columns. I have been doing it with the following
View 4 Replies
View Related
Nov 16, 2006
How do I sort this data, such that I just have individual numbers in a row.
******** ******************** ************************************************************************>Microsoft Excel - Neil.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutU1291=
UVWX1291COAL AT 5.82-5.86; 24.16-24.20; 24.64-25.77; 26.30-28.31FALSEFALSEFALSE1292COAL AT 23.52-23.57; 26.12-26.16; 44.58-45.57;46.10-47.85FALSEFALSEFALSE1293COAL AT 23.24-23.27;5.62-25.67; 42.12-43.05; 43.63-45.57FALSEFALSEFALSE1294COAL AT 39.16-39.24; 59.10-59.90; 60.47-62.40FALSEFALSEFALSE1295COAL AT 15.77-15.82; 22-22.06; 39.41-40.52; 40.75-42.80FALSEFALSEFALSE1296COAL AT 14.03-14.08; 16.48-16.53; 34.19-35.32; 35.76-37.81FALSEFALSEFALSE1297COAL AT 15.92-15.97; 35.81-36.85; 37.33-39.33;FALSEFALSEFALSE1298COAL AT 37.30-37.35; 58.09-58.93; 59.41-
The problem also arises, when I have data like this as well:
******** ******************** ************************************************************************>Microsoft Excel - Neil.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutU1325=
UVWX1325APPROX DATE. COAL SEAMS: COAL SEAM 220FT6IN-221FT1IN; ALFRED SEAM 306FT5IN-310FT6IN.ALFRED [UTRECHT (COAL)]93.3994.6401326APPROX. DATE. COAL SEAMS: 47FT9IN-48FT; 185FT10IN-185FT11IN; 217FT4IN-219FT7IN; ALFRED 294FT6IN-296FT4IN; GUS SEAM 312FT5IN-312FT11IN; DUNDAS SEAM 323FT4IN-323FT10IN.056.6456.6701327APPROX. DATE. COAL SEAMS: ALFRED 224FT1IN-228FT1IN; GUS 250FT9IN-251FT8IN.GUS [UTRECHT (COAL)]76.4376.7101328APPROX. DATE. COAL SEAMS: ALFRED 224FT1IN-228FT1IN; GUS 250FT9IN-251FT8IN.FALSEFALSEFALSEKZN Coal Boreholes with Coal In
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Those numbers with Feet and inches, I want it to be converted into meters as well.
View 9 Replies
View Related
Jun 21, 2007
I am importing data from access into a worksheet, and I see the data on my screen in the cells. On Worksheet_Activate, I then want to import the data and then call 2 macros which look at the cells I have imported, and if they are not empty, then manipulate or use the data in another cell. These subs all work as they should, as I can test them by hitting F8 or by running the import sub, then the subs which manipulate that data seperately. When I run them all on activate, the vba works as if the cells are empty, not doing the data manipulation, thought I see they are not empty with my own eyes. Is there some kind of update call I must make? I have tried to add Application.Calculation = xlCalculationAutomatic
View 3 Replies
View Related
Aug 14, 2012
I am trying to create a macro where users can rearange data in a table. I used the macro recorder to sort the data in colmn C in descending order and this is the code that I got:
VB:
ActiveWorkbook.Worksheets("By Client").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("By Client").Sort.SortFields.Add Key:=Range( _
"C3:C176"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("By Client").Sort
[Code]...
This works for the table as it is but the range may change as more rows are added to the table. I want it to start at row three as the first two rows are headers but in future tables the bottom row may not be 176. I dont want to include all rows as row 177 contains totals that I dont want to be included in the sort. what I should put as the range so that this macro will work regardless of how many rows are in the table?
View 3 Replies
View Related
Mar 19, 2014
I attached document, in that document I made formula in Pressure cell which is INDEX MATCH MATCH to get the result.
But what I wanted is, when I type 10.4 in Temperature cell, the Pressure cell will show the result which is 1261.24 directly without I have to fill in two temperature cell to make it 10.4.
View 11 Replies
View Related
Apr 24, 2014
I have sorted my data by three layers. First by Budget Center, then Invoice, and then Account. I am having trouble writing a formula that will total the amounts by account with respect to its invoice and budget center.
excel forum2.xlsx
View 4 Replies
View Related
May 15, 2014
I have A spreadsheet.
The spreadsheet contains 8 worksheets, of which I am only concerned with 7 as the 8th merely presents a graph.
The first 6 worksheets all contain raw data exported from BMC Remedy indicating the names of users who logged incidents to have their passwords reset or their AD Accounts unlocked over a 6 month period, hence 6 worksheets (per month basis).
The 7th worksheet contains raw data from another application known as FIM (A MS based interface which allows users to reset their own passwords) indicating all users who are registered to utilize this functionality.
The goal of my exercise is to consolidate the data and sort it to such an extent where I can ascertain which users phoned the service desk to log an incident for a password reset, even though they are registered to do it themselves via FIM ... I have been going through the list manually and it took me 10 solid hours to go through 265 ... ... ... ... I have 3'900 to go through!
View 1 Replies
View Related
Aug 14, 2014
I have an issue with some data that I need to sort into several columns. Basically, I have a column that has data listed in each cell like this; A 567 T 1 D3. What I want to be able to do is sort that data into several columns. I am not sure how to do this at all. I have attached my data to be looked at. I have already started the process but manually, and I don't feel like having to do this manually as this is very time consuming considering I am trying to sort the data into ~1927 rows and 5 columns.
View 2 Replies
View Related
Sep 22, 2008
for example:
Column A.
@yahoo.com
@hotmail.com
@live.com
@msn.com
@yahoo.com
@hotmail.com
@live.com
@msn.com
how can i separate all datas by domain without having to cut and paste them manually.
View 7 Replies
View Related
Nov 24, 2008
I need to data sort several rows of a worksheet, followed by a different selection of rows, followed by another selection, etc, etc. The first selection is done by highlighting the rows and then following the data sort menu. \
However, if I try to highlight a second selection and use the repeat command, excel repeats the action on the first selection, not the second. As a result, I have to use the data sort menu again. There must be a repeat option which acts on the new selection.
View 12 Replies
View Related
Jul 3, 2009
Every week I export a payroll report from my payroll provider (sample attached). The report is sorted by employee name. I need this report to be categorized by department and show a subtotal for each department. The payroll provider does not provide a means to put departments in the system.
I'd like to find a way to automate this as much as possible. The final report has to have a column that reflects each employee's department and the report must be sorted by dept with a subtotal for each dept. I tried to do this using a second worksheet that lists each employee and the dept they work in and linking it to the payroll summary using =IF; that obviously did not work.
Does anyone have any suggestions as to how this can be accomplished? The process would need to be repeated for many clients each week, so I'd like to keep it as simple as possible.
View 7 Replies
View Related
Aug 5, 2009
I need help with either a formula or macro for sorting data into specific columns. I need the entries under the headings Ar,Bj... to be sorted into the correct columns. To add to the problem, the data may not be exactly the same as the heading.
Attached is an simple example of a spreadsheet where the top is the original and the bottom is what I need the final outcome to be. The data is pasted from a different spreadsheet and will be changing each time.
View 12 Replies
View Related
Dec 10, 2009
sorting a worksheet where the data is within a single cell.
For example;
Cell G1: "American Express Settlement 090914 0000013"
I would like to sort the entire worksheet using this cell and being able to sort by 0000013 and not American Express.
View 9 Replies
View Related
Jan 21, 2013
I am trying to sort alpha numeric data like so -
PA1
PA2
PB1
PB2
PB10
When I sort, the alphabet part is sorted right, but the number part is is not what I want -
PB10 is right below PB1-
PB1
PB10
PB2....
View 2 Replies
View Related
Jun 27, 2013
I'm looking for a opinion on what might be the best way to organize some data for sorting later.
I work for a company and we have a lot of equipment. I have a spreadsheet with each piece of equipment in a row. Column A has the equipment name with all the following columns having info about the equipment. One example would be voltage, some equipment runs at 120v and some runs at 208v, some runs either or and some runs at other odd ball voltages, although this is rare. I am trying to determine what the best way to enter data like this would be. Would I be better off entering one column labeled voltage and filling in the voltage, but this becomes problematic when a piece of equipment can run multiple voltages. The other though is to do multiple columns, one labeled 120v, one 208v, one 480v or whatever and go from there. Than put a "X" or Yes or something in the columns that apply.
I have this same scenario for multiple variables like connectors, hardware accessories and things like that. I am trying to think this through before entering all my data of what could be the negative effects of this.
Would it be possible/make sense to do a radio box?
My end result is hopefully to be able to say I want a piece of equipment that meets multiple scenarios like runs 120v, this accessory and this connector. Ideally setting up some sort of easy sort method in my column headers.
View 2 Replies
View Related
Jan 22, 2014
I've attached the spreadsheet I want to use. As you can see there are three sets of "bootcamper name" and "time" columns
I wanted to know how I can sort by name or time if I were fill in data that started from A7 and B7 all the way down to A43 and B43 then restarts at C7 and D7 all the way down to let's sat C30 and D30
If I wanted to list all the data alphabetically ( or by time ) from A7 and B7 all the way down to C30 and D30, how can I do that?
Terrible Tuesday.xlsx‎
View 5 Replies
View Related
Mar 19, 2014
I have a continuously growing list of data that I need to sort onto another sheet automatically. The sorting data is always in column A but sometimes is not always in the same format; however the sorting data is always the first 6 digits of the data in the cell. When i add the new data to the first sheet I would like the data to be automatically sorted onto the second sheet with 1 row in between each group of data.
View 2 Replies
View Related
