Sorting Two Ranges In Different Order And Combining Result
Oct 9, 2009
I am wanting to display cricket bowling figures - the best figures for each player as the season progresses.
I track runs and wickets each week like this:
Runs 23 25 18 35
Wickets 1 3 2 3
Sorry can't post attachments. Top row is A1:E1. Bottom row is A2:B2.
This shows the bowling figures for 4 weeks.
I need to sort the Wickets range and find the highest number. When there are two or more cells returned with the same value (as there are here (3)), we then need to find the lowest value in the runs range for these two matches.
The result needs to be displayed as follows:
(number of wickets hyphen number of runs)
In this example 3 wickets was found twice. 25 runs were conceded one week and 35 runs another. So the result needs to be: 3-25
When there is only one cell with the highest number of wickets, the number of runs for that week is chosen without any need to check anything else.
e.g.
Runs 23 34 20
Wickets 1 3 2
would return: 3-34
View 9 Replies
ADVERTISEMENT
Mar 1, 2007
I have three columns of data I need to continually order in the same order. DIMM High, DIMM Low, CPU High, CPU Low, Freq High. The second column has the dates these were run and the column after that has the time run. The first column must list the tests in the same order, even if one tests was run twice, so say CPU High was run twice before the other tests were run, I still need the test column to list the other four test and then run the Dimm High again.
DIMM_High1/22/2007 14:52:55
DIMM_Low 1/22/2007 15:54:12
CPU_High 1/22/2007 16:36:43
CPU_High 1/22/2007 16:37:00
CPU_Low 1/22/2007 18:05:48
CPU_Low 1/22/2007 23:05:48
FREQ_High 1/22/2007 22:55:51
FREQ_High 1/22/2007 16:55:51
The problem is, Excel wants to list them by their name first. How can I get the list to appear as below:
DIMM_High1/22/2007 14:52:55
DIMM_Low 1/22/2007 15:54:12
CPU_High 1/22/2007 16:36:43
CPU_Low 1/22/2007 18:05:48
FREQ_High 1/22/2007 16:55:51
CPU_High 1/22/2007 16:37:00
CPU_Low 1/22/2007 18:15:48
FREQ_High 1/22/2007 16:59:51
View 8 Replies
View Related
Jan 16, 2009
I have 6 columns and would like to combine them. If the cell is blank the result should not leave a space.
- Prefix
- First Name
- Last Name
- Middle Initial
- Last Name
- Suffix
Example: Mr. Henry J. Weeks, III
Example: Henry Weeks
Example: Mr. Weeks
Example: Henry J. Weeks, III
View 5 Replies
View Related
Jul 19, 2013
I have a column that has something like:
7
5
3
6
4
9
3
11
(continues)
How do I make it to be the order of bigger number to smaller number?
I tried Data > sort Z-A, but then some of the numbers disappear and become "--"
View 2 Replies
View Related
Feb 21, 2014
So i have a column full of first and surnames (both names in one cell). Is there a fast way to arrange them in alphabetical order? (surnames)
View 2 Replies
View Related
Oct 7, 2008
I have the following two ranges in the same spreadsheet: A1:A1000 and B1:B1000. Both contain data that I would like to manipulate. Let's assume I would like to add "1" to every cell in the two ranges. This is what I have so far:
Sub Test2()
Dim wb As Workbook
Dim ws1, ws2 As Worksheet
Dim rng1a, rng1b, rng_union As Range
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("testsheet")
Set ws2 = wb.Worksheets("result")
ws1.Select
Set rng1 = Range(Cells(1, 1), Cells(1000, 1))
Set rng2 = Range(Cells(1, 2), Cells(1000, 2))
Set rng_union = Union(rng1, rng2)...................
Function ADDONE(rng)
n = rng.Cells.Count
ReDim rng_add(1 To n)
For i = 1 To n
If rng(i) = "" Then GoTo NextIndex
rng_add(i) = rng(i) + 1.............
End Function
I have to somehow combine the two ranges because the data needs to be manipulated simultaneously. Instead of having the range of results returned to me (see worksheetfunction.transpose), I could have calculated the variance, average etc... of the results, too. I'm using the transpose function to show that the operations in function ADDONE are only conducted for the first range of cells (rng1) that I have joined with the union method. I presume that Union might not be the correct method but I don't know ehich other method to use.
View 9 Replies
View Related
Oct 8, 2013
I have a file having 9000 rows with S.No. 1,2,3,4 etc. ,in ascending order.It has downloaded from available system in the company.
Can it be possible the file be in the format of desired sheet ( attach file ) through macro working or excel formulaes which is best suitable for the achieving?
For more clarification, refer the attach file of download sheet and Desired Sheet.
View 4 Replies
View Related
Nov 24, 2008
I have a spreadsheet saved with one worksheet with all the results on it and 130 worksheets with calculations on them, each with its' own named tab along the bar at the bottom of the page. What I'd like to know is if it is possible to sort the tabs into alphabetical order so I don't have to roam through up to 130 to find the tab (and it's corresponding worksheet) I'm looking for.
View 2 Replies
View Related
Feb 7, 2011
I have excel 2007, I have a spread sheet with approx 80 worksheets. The document grew over the year of 2010 and is not in alphabetical order. I am starting this new year wanting to have the tabs in alphabetical order. I looked in the DATA sort option...but looks like its is worksheet specific. How to make the tabs sort in alphabetical order...
View 5 Replies
View Related
Feb 28, 2012
I need to sort a horizontal bar graph, with the Y Axis in alphabetical order.
View 6 Replies
View Related
Dec 29, 2012
I 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 Related
Oct 25, 2011
Find the sorting method of dates in excel 2010. I have dates in one column and i want to sort it, but I am not able to sort in ascending or descending order.
View 1 Replies
View Related
Feb 23, 2012
The order of entries in my chart legend make no sense to me. Why is it not matching the order of the source data?
View 1 Replies
View Related
Jul 1, 2013
I wanna sort these numbers in descending order and related to each others in excel 2010.
H
G
F
E
D
C
B
A
1383.99
1533.954
831.197
1533.954
1533.954
1383.99
700
1533.954
1
[Code] .........
About descending and related to each others, I mean for example : #700 in column A,B,C,D,E,F,G,H places in the same row and the same happen to #1533.954 and others. About the numbers that are The One ( like 549.894), I need to put them in its column and a new row with empty cells in its other columns. In the end i want a table like this:
H
G
F
E
D
C
B
A
1533.954
1533.954
1533.954
[Code] .........
View 5 Replies
View Related
Aug 27, 2013
How to rearrange slices of pie chart in descending order without sorting the data?
View 1 Replies
View Related
Oct 18, 2007
I have a page of planetary symbols (astrology) in 3 columns (A,B,C ) in date order, that I'd like to copy across on the same line (date) ,to a designated column on the same sheet, in order of the ColD symbol it won't show here properly as there's no astro font, just tried it.
eg
A B C D E F
date Mercury 180 Venus Mercury180Venus
" Venus 60 Mars Venus 60 Mars
" Jupiter 30 Venus Jupiter 30 Venus
here, we have Venus copied to same line in ColE
Mars " " " ColF
how to sort the columns, Cols B,C & D themselves could be resorted instead into the order of the ColD symbol-- so I'd have colA, date-- the B,C,D sorted by D, into about 8 Cols to the right
View 9 Replies
View Related
May 19, 2014
Here is a code for me to print the names in ascending order even if values are of same means it is showing one name only instead of the other name
means if values are A=1 B=2 C=3 D=4 E=2
it has to show like this A B E C D
But the result is coming like this A B B C D
Formula which i used
=OFFSET(B$6,MATCH(SMALL(C$6:C$18,ROW()-ROW(C$6)+1),C$6:C$18,0)-1,0)test.xls
Here i am attaching the sample file for your reference
View 10 Replies
View Related
Jun 3, 2008
I have a table setup in sheet1 and I want the user to be able to choose how this data is sorted. Ie based on a number of column headings.
I have a validation list that lets the user pick on of the following options:
Club
Seasons
Pld
W
D
L
GF
GA
GD
Pts
Titles Won
Runners-up
The answer is given in sheet2 cell C3
What I want is some code that will sort the range B10:N51 by the column heading result given above.
View 9 Replies
View Related
Jun 13, 2009
I have forced the tab order of sheet. Unlike the example with the link, I have used named ranges instead of cell references in the array to order the tab sequence. (the named range are single cells and merged cells)
Force Tab Order Of Cells
Just like the author of the link, the sequencing works great but if I choose click on another cell (that is in the pre-determined sequence) it then takes me to the next pre-determined cell int he sequence.
I have tried the code the "shg" has suggested for merged cells to no avail. Maybe it has something to do with the name ranges.
Dim aTabOrd As Variant
Dim iTab As Long
Dim nTab As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iNew As Long
If IsEmpty(aTabOrd) Then
View 3 Replies
View Related
Mar 23, 2009
I have a spreadsheet that I have developed at work to track sales related data. As part of the reporting for this data, it is sorted by date. I have been asked to capture some additional related data and due to the structure of the spreadsheet(that has been in use several months with no issues) I can not put the additional data in adjacent columns without doing a complete redesign of the format.
I can place the data several columns away.... The data "in the middle" is static and is used for other calculations and should not be sorted. Is there a way to sort both ranges of data WITHOUT disturbing the columns separating them and keep the proper relationship with the data?
See attached for a very simple example. In this data set the "original" data was in range A2:E11 the "New" data is in Range I2:M11. It would make life simple if I could do something like this (but it sorts everything in between)
View 2 Replies
View Related
Feb 6, 2014
In the attached workbook I have hard-pasted data in columns A:M that's designated as an Excel Table to make it dynamic.
In the adjacent columns O:AA I have formulae to extract data from A:M based on the "Frequency" stipulated in cell AD1,
I would like to be able easily to sort the data in the variable sized range in columns O to AA in descending order of the calculated values in column Z.
Best way to do this so that the formula don't get jumbled up or the blank cells in column Z end up at the top of the sorted data?
View 2 Replies
View Related
Apr 28, 2014
Cells in row C have numbers (number of days between date a and date b)
I want to group the days so I can run a usable pivot table as follows:
Up to 7 days between update date and today (ie 1 week) 8-14 days between update date and today (ie 2 weeks) 15-21 days (ie 3 weeks) 4 weeks +
My attempt is as follows but only give two results and not 4?
=IF(OR(C2<=7),"1-7days",IF(OR(C2>=8,C2<=14),"8-14days",IF(OR(C2<=21,C2>=15),"15-21days","Over4weeks")))
View 3 Replies
View Related
Apr 30, 2013
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
View 3 Replies
View Related
Apr 21, 2013
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
View 4 Replies
View Related
Aug 30, 2006
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
View 7 Replies
View Related
Nov 29, 2008
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
View 9 Replies
View Related
Mar 1, 2008
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
View 3 Replies
View Related
Jun 10, 2014
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
View 3 Replies
View Related
Apr 25, 2014
I have excel result sheet which contains students information. i.e. name, subjects and their corresponding marks, grade, percentage etc. So from that sheet I want to print result cards for each students separately from the data (result sheet).
View 4 Replies
View Related
Dec 4, 2009
I have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
View 4 Replies
View Related