I will be inputing raw data (20 digit numeric code) from a barcode scanner into column A. I would like to extract the values from certain groups of those digits (first 3 digits and only those digits moved to C column, digits 4,5,6,7 copied to D column, etc.)
View 3 RepliesOn the attached worksheet, our guys are regularly inspected for performance. Column 'H' shows the average of their grading from each inspection (shown in cols L,P,T etc) What I would like to develop is either a macro button that when pressed sorts them all into order according to their average score in Col H, with the highest score at the top, or to have the sheet do this automatically as the data is entered. Obviously all of the data in the engineers row (all of his inspection history) will need to move up and down accordingly, but not get left behind, or mixed up with someone else's records.
View 4 Replies View RelatedI've got a series of values in a column as follows:
10 Mb
10 Mb
1000 Mb
1000 Mb
114 Mb
128 Mb
128 Mb
Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
because it starts with a "1". How do I tell Excel to sort by the entire
numerical value instead of the first number?
I'm recording statistics of sport and since I'm dealing with lots of players (and it's easier to add the statistics in when they're sorted by team and name rather than by highest amount of a stat) I need to have a separate section of the spreadsheet to return the 10 best in a stat.
I've found other formulas but I'm not good enough at spreadsheets to be able to manipulate formulas by adding stuff in so it's been hard to get exactly what I want My problem is that I need this to sort by goals (so most goals first) and then by Games played. Here's an example of what data I'm working with:
Ideally I'd like the list to show all these things (so #1 would be Player N), just sorted by goals and then games.
I have a range of data in columns a-g (the length of which i am unsure of). I have been manually sorting them for the values in both column a and column g, but the data in column b-f are needed for my work. would it be possible in one sheet to have column a "y" and on another column a "N" sorted and then subsequently, the numbers in column g copied and pasted along in rows after so you would have a-g copied in h-n and then for the next number o-u and so on. there are multiple of the same value in column g, and a. would it be possible to make a macro which would sort this for me
View 1 Replies View RelatedI have a dump which looks like below sample:
3
3
2
2
The actual dump will have around 200 - 500 rows of data.From the above I need to manually group them which are similar. E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.
The sum & sort did not work as sometimes the data with the same range are not in same order. simpler logic/VBA code if any, which will automatically put group numbers if the data in two rows are exact match.
E.g of the output below for the above data:
Group No.
1
3
3
2
2
I have x coordinates in column 1 with coresponding y coordinates in column 2. From my data of x,y coordinates I want to find points with similar y values. In my data I have defined groups of numbers, i.e. I have a set of numbers with y values around 30 (+-10), then a new group with y values around 60 (+-10), and so on... Sometimes the groups are not totaly separeted, there could be a few points with y values between 30 and 60. These points can be grouped with the closest group of coordinates (30 or 60). Each group of x,y coordinates could be copypasted in the columns to the right (columns 3 and up).
So what I want to do is find a group of coordinates. This group will have at least 40 individual points +-10 from a group mean. The coordinates need to be sorted out from the data and put in seperate columns.
example
1
2
3
1
2
6
4
3
6
7
In the Above range i want excel to show me
how many items are duplicate
If the duplicate item is shown i want the result to be shown in next cell
LIKE in the above example i want excel to show 1 is common , 6 is duplicated twice and therefore result should be shown in next cell
I have two sets of 2 columns. One column in one set is identical to a column in the other set, but they are arranged in different orders. I would like arrange those two columns side by (matching their numbers) so that I can find the average of the two non-identical columns.
View 2 Replies View RelatedI have Excel 2003 with named cells, for example...
cell cell name data
A1 AAA 3
A2 BBB 1
A3 CCC 2
and so on...
When I autofilter (ascending) these cells, the data in these cells sort correctly, but the cell names stay where they're at.
cell cell name data
A1 AAA 1
A2 BBB 2
A3 CCC 3
I'm not an Excel expert so I'd like to know is there any way I could get the cell names to sort with their data.
cell cell name data
A1 BBB 1
A2 CCC 2
A3 AAA 3
I have a huge set of data values given per 0,5 hour. Now I want to make a chart which shows how many hours each value has taken place.
Here is what it looks like with the arranged data as y-values. Instead of the x-axis showing the number of values, I would like it to show the above mentioned.
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
I know the formula =large but my problem is the formula for the equivalent value for it. Note: In my xcel file attached you can see there are multiple items with the same total numbers and also blanks
View 3 Replies View RelatedI am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).
View 14 Replies View RelatedI'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 RelatedI 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
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.
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 ?
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 RelatedI 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.
I use my VBA code to sort the data in certain columns. I have been doing it with the following
View 4 Replies View RelatedHow 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.
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?
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.
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
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!
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 Relatedfor 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.
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.
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.