I have a spreadsheet that has original data. I originally used the array formula after sorting. But my application is different now. I can not use the sort function or any macros so it has to be a function or filter. Been playing with this one for a while but cant figure it out. Please see file. Also the array wont work if there is a gap in the data. The data needs to be in the final list (green) in order and only one of each can show. Basically I want to show only one of each value or item and then find the sum of that value.
On Sheet1 I have a list of 32 people in column A (A1:A32) and on the same sheet in column B (B1:B32) is a 3 digit number representing a score. I have the 2 columns sorted together descending order based on column B scores.
On Sheet 2 in column A (A1:A32) I have the same 32 names in random order with associated information in columns B to K. I want to sort the names and associated information on Sheet 2 column A based on the order of the same names in Sheet 1 column A. Is there a formula to achieve this result?
I have a list in a column A which has data which is not in alphabetical order and can have duplicates. In need to sort this data in next column B. I need a formula for this so that the new data is dynamic. I have checked the following thread which gives me formula
I have to create a summary sheet of stock code quantities for work and i know there must be an easier way to do this then totalling each style and colour by a basic "=sum" formula.
At present the information is by style then colour break down and i want it excel to automatically calcuate the total per style number if possible
EXAMPLE: Style# DESCRIPTION COLOUR QTY
12345 Hooded Sweat Black 3 12345 Hooded Sweat Blue 11
12431 Treasured Tee Pink 9 12431 Treasured Tee Yellow 6
I'm trying to sort a table as displayed in the image below. The table consisting of items in column A, here fruits, and a column E where totals over three month is displayed.
I have entered an array formula in column H where the total of each fruit is summed and then sorted in descending order. My problem is that whenever two fruits have equal value (here, apple and pear), Excel will not manage the display of both fruits, it will just display the first twice. The formula in column I is a simple SUMIFS.
It doesn't matter if Apple or Pear is displayed first, but I need both items to be displayed in column H.
I'm attempting to sort a list of containers and their corresponding quantities (A24:B28 in the attached spreadsheet) by using a combination of an index and match formula (cells A31:A35) and a large formula (cells B31:B35) in order to have the container with the largest quantity (summed in cells B24:B28) appear below in cells A31:A35 and the corresponding quantities in cells B24:B28.
My current index and match formula works fine until I have the same quantities of different containers as I have replicated on the attached spreadsheet. When this is the case, the formula returns only one of the various container types. How do I edit the formula to allow for multiple containers with the same quantities?
I have a list with different categories and several characteristics per category. My goal is to identify the 10 strongest growing characteristics (I guess by sorting them in descending order) and then show them in this order, but organized in the respective categories. e.g.: if "Psychographic">"Demographic" and "geographic" and "behavioralistic", then put "Psychographic, including its subordinated characteristics that are included in the top 10, first. Follow this order until all Top10 members are included.
I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?
Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.
I've got large set of data(Column E) for dates from 2007 to 2013(Column B). Here dates are in chronological order.(Attachement) Now I want to match the data in to dates in Column H. Here dates are in random order. I used =INDEX(E3:E40582; MATCH(H3;B3:B40582; 0)). But it doesn't work.
how to create the database and the resulting Pivot Table. My Sales Database includes the Sales $ and GP$ for each line. In the Pivot Table I've managed to create a calculated field for GP %, no problem.
i have also managed to figure out how to express each Row as a % of the Pivot Total, also very easy.
What I need to know is how can I sort my Pivot Table High to Low using these calculated fields?
I don't understand all of it, but I observed that it works only with mp3 with tag id v1, not v2, anyway I also see another code for tag id v2 given by NateO.
But before entering in detail of what I want, is it possible to edit tag id v1 or even v2, with VBA? Because I want to complete my mp3 informations that are missing, or modify the wrong one, in excel, and then with a macro update my mp3 files...
I've found this code on the forum to get my mp3 informations :
Option Explicit
Public Type mp3Info Header As String * 3 Title As String * 30 Artist As String * 30 Album As String * 30 Year As String * 4 Comment As String * 30 Genre As Byte End Type
Sub Getmp3Info() Dim mp3ID As mp3Info Dim lngRow As Long, lngFile As Long Dim lngFileCnt As Long
With Application.FileSearch .NewSearch .Filename = "*.MP3" .LookIn = "H:Daniel" 'Change this to the folder you wish to search .SearchSubFolders = True........................
I have a sheet with fixed number of rows but variable number of columns. I have to sort this sheet on the basis of column A starting A2. i am using the following code
But it does not sort properly. To illustrate, i have 10 rows, some 4 columns wide and some 5 columns wide. Since the first row is 5 columns wide , all the rows with 5 columns populated get sorted but the rest get left out.
(There are 500+ more lines like the ones below, in groups of approximately 17 lines per page of information.)
* C.B.A. Bank - 2nd reference to branch near Mike's store. (21/03/1956) * Berwick exams results. (06/06/1877) * Berwick exams (09/05/1877) * Steps taken for erection of Berwick. (11/07/1874) * Berwick tenders for work at home. (11/07/1877) * W. was a storekeeper at Berwick. (25/07/1881) * Berwick Brass. (27/06/1877) * Commercial erected for son. (27/06/1878)
I need to do the folowing to it: (not necessarily in the following order)
- Insert into Excel - Remove brackets - Sort chronologically - Copy back into program I am using again, in same chronological order.
The desired outcome is to be able to throw into Excel all the lines and press a button, (possibly the Sort Ascending one) and have a chronological list of all entries.
I have a table which holds scores as well as other data for players. Is it possible to automatically sort a table based on their points score to see who is top of the league?
Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
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.
I created a data base that had a hyperlink to a plan of the store so we can find parts easy. With a lot of help for people here on this web site, it works very well.
My only problem now is that I sorted my Database by the hyperlink column. It made it easy to do one hyperlink and pull the fill handle down for the rest of the same hyperlink reference.
When I went to resort the database so that the parts were in alphabetical order. The sort worked well except that the hyperlink for lets say cell F87 was not sorted. Example : - F87 hyper link presort had the reference to Rack 10 self B. Post sort it had the reference to Rack 16 shelf C. But when clicked on it still took you to Rack 10 self B.
I'm trying to sort this spread sheet so that each email address will have its own column assigned to it. Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D. The problem is I've around 1200 rows of infor and the email addresses are all over the place..
I have around 1900 dates that I need to sort into order but I need the data to be displayed as DD/MM in chronological order eg 01/01, 02/01 etc.
At the moment the dates are DD/MM/YYYY in one column. Have tried sorting them into date order but have found that they're sorted by YYYY. Is there a way of sorting them DD/MM instead?????
I have an array of data that I need sorted. I did a Custom Sort. However, the data did not all sort properly (some of it did). Any ideas as to why this happened and how I can get it to sort correctly?
Im not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.
I have a column showing date DD/MM/YYYY. I want to be able to get a new column stripping out the days so that I can chart changes over a month rather than each day of the last 2 years. I have tried altering the cell format to MM-YYY although this shows the format I want when i run a pivot chart it will still create records for individual days. I then tried copying and pasting the above as text only. This results in the pivot chart sorting A-Z rather than by date.
I have information in columns A (First name),B (Last name), C (Misc), D (misc). Is there a way that someone can enter in new information at the bottom of the list and it will automatically sort by the last name once saved or closed ect.. ? I have people not very familiar with Excel and the keep messing up inserting rows and I don't want them to sort. If they could just enter information at the bottom and save it, then when someone opens it to just view it, it will be in order.
I am mapping this file on the basis of a lot of fields. But the end product has 15 currencies. My requirement is to separate the accounts on the basis of the currencies and then consolidate it on the basis of GL account. For example, first I have to find all the accounts that are in USD and then if there are 2-3 similar GL accounts, then I will consolidate on the basis of the GL accounts. Please find attached file.
in the the attached image of excel i want to see what is the bigger number in the row 49 then sorting these numbers descending and write it in C52 then make a quick equation on in each number ,and this equation is to deduct this figure from each other nine numbers and see what is the zero or above zero result and put it the cell next to this number (column E) starting from E52 example 527.7 is the maximum number so i put it in the c52 and then deduct 527.7from each number ,like the following 41.0-527.7 = negative value (doesnot count) 21.8-527.7= negative value (doesnot count) .... .... so on and we will see that the only greater than zero or equal to zero result is only 1 result so we put the figure 1 in the cell E52
