Splitting one table into multiple tables

[Towaiji]

Hello everyone,

I have a massive table that has different values on multiple columns per row.

I would like to split this giant table into smaller ones depending on project phases.

For example, if the first columns shows phase one then i want the entire row to go to phase one table from the master table.

If the second cell shows phase two then I want that to go to the table of phase two

I can do this with the IF command but then I would have empty rows in my tables, how to avoid that?!

I hope i explained it well and did not complicate it. I need to do this without macros preferably.

Thanks!

Towaiji

[ChemistB]

One way.
Create a key column to the right of your data. In this column copied down

Please Login or Register  to view this content.

In the destination table, Starting in A3 (in my example) and copying right and down (create new in each phase table)
=IFERROR(INDEX(Sheet1!A$2:A$20,MATCH("Phase 1_"&ROWS($A$3:$A3),Sheet1!$D$2:$D$20,0)),"")

See example file. Questions?

[Towaiji]

Thank you for your example, it worked perfectly but in order for me to replicate it, would you please explain how to you set up your equations?

Thanks!

[ChemistB]

No problem. Do you understand how to use a basic INDEX/MATCH?

https://www.contextures.com/xlFunctions03.html