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

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit retag close merge delete

Sort by » oldest newest most voted

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.

=IFERROR(F1+MATCH(TRUE(),""<>OFFSET(Sheet1!$E$1,F1,0,ROWS(Sheet1!E$1:E$100)-F1,1),0),"")


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

=IFERROR(INDEX(Sheet1!A$1:A$100,\$F2),"")


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

more