Extract & Categorize from Master Tables Into Multiple Tables

Hi

I'm assuming this is possible some how

I have a Master Table with Categories & Skills.

I'm looking to extract or categorize into multiple other tables

Looking for a less manual process other than filtering, copying & pasting

I think an Index type of function would work but not sure how

See example tables

Thanks

This ARRAY formula will do what you want, in the PM table. You will need to adjust PM to whatever other categories you need...
E37=IFERROR(INDEX(C:C,SMALL(IF($C$15:$C$29="PM",ROW($C$15:$C$29)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Then copy down and across as needed

Hi FDibbins

Thanks for answering.

As instructed, copied the array function in E37

In Cell D37 - Got the result as, "PM" - Not, "Skills 1" as expected

Shouldn't this function be reading both C & D columns to get the results correlated per row for the other categorized tables

Thanks

Apologies, that should have gone in C37. You can then copy it to D37.

Hi FDibbins

Still getting same results

From the Master Table or 1st Table, the intent is to go down the column & extract wanted Category, then put the "Skill" to the right of it in the desired table(s) below - Almost like Index / Match Function would do.


Cats = Categories
PM = Project Management
CAD = CAD, Data Management
D = Disasters
EQ = Engineering & Quality
L = Logistics

As seen in the example, the above categories are selected multiple times throughout the Master Table

For example, in the 2nd Table, it's supposed to show all the "PM" skills

Cats Skills
1 PM • Skills 1
2 PM • Skills 6
3 PM • Skills 11
4
5

Your array function almost works! Just needs a little tweaking somewhere.

Thanks again....

Did you enter using CTRL SHIFT ENTER, like I said? Just using ENTER on it's own, wont work with a formula like that.

Below, I have run the calcs in col E and F, to compare with your sample answers...

	B	C	D	E	F
36		Cats	Skills
37	1	PM	• Skills 1	PM	• Skills 1
38	2	PM	• Skills 6	PM	• Skills 6
39	3	PM	• Skills 11	PM	• Skills 11
40	4
41	5

E37=IFERROR(INDEX(C:C,SMALL(IF($C$15:$C$29="PM",ROW($C$15:$C$29)),ROWS($A$1:A1))),"")
ARRAY entered, like I said, then copied down, and then copy them all across

Hi FDibbins

Yes - I did you use the CTRL SHIFT ENTER, as directed

Difference is that you created or used 2 extra columns which wasn't my intent. However, I managed to get this function working within my existing example!

Function works great!!!

Thank for the assistance

Happy top help and thanks for the rep.

The only reason I used the extra columns was so you could compare my answers to yours, they cont need to be there, you could just put the same formula in B and C