breaking up data across multiple worksheets

[paintballlovr]

Wondering if someone might be able to help with a problem.

In the attached sheet, I have data on worksheet titled "Components". I would like to copy the data rows to their matching titled worksheet (i.e. all of the rows that have CARMICM show up on the sheet with the same name) automatically. Ideally, each titled worksheet would only show it's matches and wouldn't display any errors on lines that don't have a match.

I have manually copied CARMICM data from Component to CARMICM to show what I'm hoping it would look like, but want to automate instead of a user having to do it.

[martindwilson]

id probably do it like this using a data sheet with some countifs and index match on each sheet , i've done it for FREEMAJ2

[paintballlovr]

awesome, thanks for taking a look so quickly!

At the risk of causing my brain to explode, can you try to explain the code somewhat? I ask because in an attempt to understand what it was doing, I cut the FREEMAJ2 records from their original spot and put them at the bottom of the list and the FREEMAJ2 sheet changed. In my specific application, the Component worksheet is going to be generated daily and records may be different. With sorting, I should be able to assure that the order is CARMICM, FREEMAJ2 and NORMANS, but I can't say that I will always have the same number of records, therefore next time the first FREEMAJ2 may be at row 58 instead of 16.

[martindwilson]

it wont matter where they are.
the columns on data sheet eg col c counts each instance of say FREEMAJ2
ive randomised the list on first sheet to give a clearer picture. look at the data column c again .as it counts it sees the first instance of FREEMAJ2 in row 3 of components this does not increase until it sees the second instance in row 6 then the third in row 12 the index/match function looks for 1 and returns the corresponding columns across, then as it is dragged down it looks for 2 then 3 and so on.(match finds the first value only so it always matches the first 1, then the first 2 and so on ignoring the rest)
i put an if(countif( around the index match function
basically its used to count the total number of say FREEMAJ2 in this case 12, if the result of row(x) the formula is in is greater than 12 it returns blank .
note row(a1) is used to get 1 ,try it in a cell on its own and drag down =row(a1) it will show 1 as its dragged down it will change to row(a2) ie 2 then row(a3) ie 3
it saves having to change the formula and manually putting in 1 then 2 then 3.....
you can use any reference eg row(z1) that would still =1

[paintballlovr]

Thank you very much. Everything is working just as expected! Sorry I didn't have a chance to say so before now.