Create a table from other table depending on a value

Hi jordimasague,

Thanks for the gold star!

To simplify, both tables are on the same Sheet. When you get this to work, move (Cut and Paste) one table to another Sheet. The formulas will automatically adjust, if necessary.

This works best if the tables have 1 Header Row and no Header Columns. Then it is easy to Fill the formula in ORDER LIST::A2 to the right and down.

Duplicate the LIST table and rename it as ORDER LIST.

Enter this formula in ORDER LIST::A2 (and Fill Right and Fill Down)

=LIST::A2

Now apply a Filter to ORDER LIST

When you type YES for broccoli in the LIST table,

Regards,

Ian.

This is doable using just formulas. I just did this for a Cub Scout packs Pinewood derby where you dont know the order the kids check in but need to break them apart quickly into their grade levels (or ranks).

I cant do it in numbers from where i am (I only have excel at hand), but the basic formula looks like this for the first cell, in a hidden column, shows the row number of the first cell with a YES in it. (this cell is in my cell F1 btw)

=MATCH("Yes",$C$1:$C$8,0)

Followed in the cells below with:

=IFERROR(MATCH("Yes",OFFSET(C1,F1,0,10,1),0)+F1,"")

So we find the next match, add the offset of the last row and that gives the second row Yes was found on

then in my column G I use offset to move from a known point, using the row offset to tell the formula what text to grab.

=OFFSET($B$1,F1-1,0,1,1)

you subtract one because you dont want to -offset- the number of rows, but offset TO that row.

Hope you can see what I was doing here. If not maybe someone that has numbers at hand and can do some screenshots can help us both out.

Jason

Hi jordimasague,

Method 1. Sort.

Sort by the buy column

Select all the YES rows, copy and paste into a new, blank table.

Method 2. Filter.

Happy Numbering!

Regards,

Ian.