+ Reply to Thread
Results 1 to 6 of 6

Sorting arrays by year

  1. #1
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Sorting arrays by year

    I have a workbook with about 10 worksheets. Each worksheet represents a different piece of equipment. We routinely test each piece of equipment for microbial contamination. Each sample is coded to match the result to a specific piece of equipment. We have over 4000 data points from 3 different years: 2014, 2015, 2016. All data for all equipment is posted on one main spreadsheet. Based upon the sample code (a 22 character value) the result is copied onto the respective equipment's spreadsheet. Because of this, there are many blanks in the results column on each sheet. I use the COUNT function to determine the number of results by year. I want to have columns on each sheet that lists the individual results by year for the specific piece of equipment without blanks. We perform statistical analysis of the data by year to establish trends. I have managed to get all values in one column without blanks. However, I cannot get the array to list values by specific year. I have tried the following formulas:

    =LARGE(IF(YEAR(b7:b5000)=2015,C7:C5000,{1;2;3;4;5}))
    =IF(YEAR(B7:B5000)=2015,LARGE(C7:C5000,{1;2;3;4;5}))
    =LARGE(C7:C5000,IF(YEAR(B7:B5000)=2015,{1;2;3;4;5}))

    There are actually many more data points for each year, but I wanted to keep the formulas short for this purposes.

    What I get is a column of the top 5 largest values regardless of year. Sometimes I get a #NUM! error; sometimes I get a FALSE error;

    The date in column B is in the format dd/mm/yyyy, formatted as a date (I think it is referred to as a Short Date); The values in column C are formatted as General; I have a cell that contains the year, formatted as General, but for this purpose I typed in the date. I select the formula and press CTRL+Shift+Enter. That generates the top values from column C correctly, but regardless of year. When I change 2015 with 2016 I get the same values, even though the data for 2016 is quite different.

    Another issue: it appears that the results are dependent on how the dates are sorted on the Main page. If sorted newest to oldest (year 2016 is first) then I get the top values when I enter 2016 into the formula, but I get the #NUM! error for 2015. If I sort date oldest to newest, the exact reverse happens: 2016 now has the #NUM! error, but 2015 produces the top 5 largest values regardless of year.

    I figure it has to do with the format or syntax of the YEAR function and where it is positioned in the formula, or these functions (I have tried SMALL and LARGE) cannot accommodate sorting by year.

    This is very frustrating. Any suggestion?

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,543

    Re: Sorting arrays by year

    Since you have already developed a mechanism for getting the data from the main sheet to the individual sheets, I'd suggest that you upload a sample of one of the individual equipment worksheets with six data points from each year so that we can see if we can develop a formula(s) that will separate the top five values by year. To attach a sample workbook click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (the paperclip icon does NOT work).
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Sorting arrays by year

    Some forewarning: The spreadsheets are quite large, and as I said in the original post, there are more than 5 data points. The objective is to include all values for each year for the following reasons:
    Determine and display the outliers on the top of the spreadsheet. Because of the number of data points, 2016 may not start until row 2000, and an outlier may appear in row 3500. The point of combining all the values in one contiguous column is so that a reviewer does not have to scroll through 3000 rows to see where the outlier is. If there were a way to search for the outlier and list the cell address of the value and the corresponding date right at the top. But here goes.
    I had to delete several rows to shrink the size of the file to be below limits for uploading. I hope I did this right - first time.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,543

    Re: Sorting arrays by year

    Paste the following array entered formula* into Q6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *While the cell is still in edit mode press Ctrl, Shift and Enter simultaneously.
    After the curly brackets have appeared around the formula and the value of the largest data point (100 for the year 2015) has been displayed you can then copy the formula down.
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Sorting arrays by year

    I think I see how the formula works. You had the LARGE formula look for the largest value (associated with "1" in column P) then the second largest number (associated with "2" in column P) and so forth. Before I read all your instructions, I highlighted all the cells first, entered the formula, then pressed CTRL+SHIFT+ENTER and all cells had the value 100. I then read all the instructions to select the first cell in the column, enter the formula, hit C+S+E then copied the formula down it worked fine. I then tried it with changing the date reference from 2015 to 2016 and all values look legitimate and no errors.

    Thanks.
    Vince T.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,543

    Re: Sorting arrays by year

    Glad to hear that the formula does what you want. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post in the thread. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [Solved]Sorting two arrays
    By zaoth in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2014, 09:49 AM
  2. [SOLVED] Sorting through arrays of data
    By engineernoob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-17-2013, 06:09 AM
  3. Sorting by Date--Need help when sorting by 2-digit year
    By Farris_TN in forum Excel General
    Replies: 3
    Last Post: 06-20-2013, 11:20 AM
  4. VBA arrays in a range: sorting
    By zeno1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2012, 02:28 PM
  5. Sorting arrays using VBA?
    By gryffin13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2012, 02:41 PM
  6. joining and sorting two arrays
    By Paul134 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2008, 11:18 PM
  7. [SOLVED] referencing/sorting arrays
    By Tim Kredlo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-17-2005, 11:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1