Match a list of Actions to a list of Systems to show how many link to an individual Action/System

[spikelovesmetal]

I have two sets of data - one is an 'Action' list and has numbers identifying them from 'A1' to 'A30'. Th other is a 'System' list and has number identifying them from 'S1' to 'S290'.

The idea is that there will be a specific set of Actions such as 'A5 - take the top off a bottle' and this will reference several System requirements that are needed in order to perform the action - 'S2 - align object', 'S12 - identify target', 'S40 - dispose of unneeded parts' (this would be the bottle cap).

My original Excel worksheet looks like this (but much bigger!):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Action[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]System[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD="align: center"]A1[/TD]
[TD="align: center"]Find which box is the lightest[/TD]
[TD="align: center"]S1[/TD]
[TD="align: center"]Identify object[/TD]
[/TR]
[TR]
[TD="align: center"]A2[/TD]
[TD="align: center"]Identify a square from a circle[/TD]
[TD="align: center"]S2[/TD]
[TD="align: center"]Align object[/TD]
[/TR]
[TR]
[TD="align: center"]A3[/TD]
[TD="align: center"]Measure the width of a book[/TD]
[TD="align: center"]S3[/TD]
[TD="align: center"]Measure object - height[/TD]
[/TR]
</tbody>[/TABLE]

The idea is that there are 30 actions that a tool needs to perform. There are a lot of systems (290) that the tool can use but they must be able to use the right systems to complete the action.

So far I have a list of the Systems and have linked the Actions I think will need to use that system in order to be fulfilled:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]System[/TD]
[TD]Description[/TD]
[TD]Action[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]S40[/TD]
[TD]Dispose of unneeded parts[/TD]
[TD]A5[/TD]
[TD]Take the top off a bottle[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A19[/TD]
[TD]Unwrap a sweet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A24[/TD]
[TD]Take a tissue out of a box[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to do almost the opposite now: to show on a worksheet each Action laid out 1-30, and for each of those actions I want to show each System that the action requires in order to work:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Action[/TD]
[TD]Description[/TD]
[TD]System[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Find which box is lightest[/TD]
[TD]S1[/TD]
[TD]Identify object[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S2[/TD]
[TD]Align object
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S12[/TD]
[TD]Identify number
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S6[/TD]
[TD]Measure weight[/TD]
[/TR]
</tbody>[/TABLE]

Is there any way of automating my current workbook so that for every 'A1' I can show a list of the system numbers needed to make it happen? My ultimate aim would be to be able to identify which systems were needed for a single action, and to show how many times a system is used to give an idea of the most important ones.

Thanks for your help!