I have an excel spreadsheet of catches of numbers and types of moths by dates. It is sorted into species down the left and date along the top. Is there an easy way to sort each date so that only the species with values in them (i.e. species that were caught on that night) are shown? At the moment, the spreadsheet shows what I want, which is an overview, but to see an individual date's catch list, I can do it by sorting by value, but it will only sort that column and not show me the species that those values represent too, rendering the sort pointless since the values are now not associated with the correct species. That sounds very confusing! In other words, how can I select two columns so that it will show only those rows that have values in them and miss out all the blank species rows, while still showing the correct species next to the correct value? Screen shot of table: So for example, if I wanted to just see the catches on 30/06/2016, I could sort just that column by value, producing a list of all rows with values in, but the species column would remain as it is in the pic - no help!! They need to be associated somehow. A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data. Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. In case your query has been correctly understood, you wish to display all column values (without blanks) on a particular date with the corresponding Species, and no other columns. Below is a solution using excel formulas: Please refer below image: Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell I3 and copy down: =IFERROR(INDEX($A$3:$A$100,SMALL(IF(OFFSET($A$3:$A$100,,MATCH(J$2,$B$2:$G$2,0))<>"",ROW($A$3:$A$100)-ROW($A$3)+1),ROWS($A$3:$A3))),"")

Enter below formula (normal, non-array) in cell J3 & copy down: =IF($I3="","",VLOOKUP($I3,$A$3:$G$100,MATCH(J$2,$A$2:$G$2,0),FALSE)) You may enter any date in cell J2, and the corresponding values will be returned / displayed. Insert an empty row between rows 1 and 2. To sort for 03/07/2016, for example, select the cell with that date (F3 after the empty row has been inserted), then click Sort > Sort Smallest to Largest (or Sort Largest to Smallest, if you prefer). That should sort the entire range, inlcluding column A (the Species column). Do not select the column when you want to sort, just the date cell. 2 additional answers Anonymous 2016-09-15T08:38:36+00:00 Many thanks Amit - although this makes no sense to me whatsoever, I'll give it a try when I have a bit more time on my hands! :) It looks as though it will be an elegant solution... 0 comments No comments Report Many thanks Amit - although this makes no sense to me whatsoever, I'll give it a try when I have a bit more time on my hands! :) It looks as though it will be an elegant solution... Anonymous 2016-09-15T08:36:35+00:00 Nice and simple..thanks! Have tried this quickly and does exactly what I want - cheers muchly! :) 0 comments No comments Report Nice and simple..thanks! Have tried this quickly and does exactly what I want - cheers muchly! :)