Sorting and re-sorting columns in Excel/Numbers

Discussion in 'Mac Apps and Mac App Store' started by jent, Mar 31, 2011.

  1. jent macrumors 6502a


    Mar 31, 2010
    I've been tasked with taking a list of tens of thousands of items in an Excel spreadsheet. Through the help of fellow MacRumors posters, I've learned how to use formulas to look at the numbers in two different columns and have a third column write the difference between the numbers.

    What I want to do now is eliminate all rows whose two columns have the same number. I would say about half of my thirty thousand rows have the same number in both columns, so I'm wondering if there's a way to sort by the number in the third column (which is just the difference between the first two columns), and then eliminate all the rows that show up together with "0" in the third column. After that, I want to go back to the original sorting.

    Thanks for any help!
  2. Ravenwolf40 macrumors newbie

    Mar 23, 2011
    I would think that there are multiple ways to do that.

    1. You could filter the third column on the "0"s and then highlight all of those rows and delete them, then remove the filter and only the non-0s would remain

    2. You could simply sort by the third column ascending or whatever and again select the rows with 0s and delete them leaving non-0s

    3. I find it odd that you just have 2 columns of numbers that happen to pair up perfectly, you may be simplifying the scenerio for assistance. If this is the case, then you may want to use the vlookup formula to match up column 1 and column 2 first based on certain criteria that you need, and then simply delete out the unmatched blanks using a filter. Keep in mind that Alt ; is a very powerful key combination to select only viewable data to copy and paste into a new tab in the spreadsheet if needed.

    4. I believe that there is a match formula in excel, although I have never used it personally

    That's the thing about excel, the more you use it the more tricks you learn! And there are almost always multiple ways to do accomplish the same task. I use it everyday, and when excel 2007 went to 1 million rows I was sooooo happy compared to excel 2003 65,536 rows or so. In working with cell numbers and such we often have over 1 million row database results (easy), but sometimes i luck out and it falls under the million and I can excel it instead of using access which is never fun (i HATE access most of the time as it is limited to like 2gb of data as well I believe, which is a lot in all honesty, but sometimes not enough and we need to make more than 1 LOL).

    Anyway, good luck, hope that some of this helped out :).


Share This Page