How to sort rows to separate sheets based on information in certain columns

okay so what Im working on is a tool to sort my magic the gather card library to help with deck building and lots of other stuff.

  • the first sheet is where I want to just dump in all the information from the card like card name, color , power, etc
  • I want to sort the cards onto separate sheets based on the cards color
  • A card color can be a single color or any combination of the 5 color types or even colorless
  • Right now I have six columns that represent the color options White, Blue, Black, Red, Green, Colorless
  • Because of the number of cards out there I want to be able to just put a “X” in the applicable column and move on to the next card
  • So if row 1 has an “X” in column 2, then row 1 belongs on sheet 3

does anyone know a formula to make that happen?

I’d recommend a helper column on each of the sheets that would be used to find the row of the next none blank cell in that sheets colour column.

I use column F for the example. Cell F1 is set to 1, then F2 should have the following formula which is then copied as far down the F column as needed by your data size. Example also assumes Sheet1 column E indicates the color for this sheet.


Then the cell A2 should contain the following formula, which is then copied to the rows and columns that will contain the filtered data.


Edited to correct the first formula, which I’d copied from a different sort function.