separation of data

Unfortunately, your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Yes. Please don't do cross-post without referencing it. I have spent some time, on a nice sunny morning, getting an answer for you. I just hope that you do not have an answer elsewhere, otherwise I will have wasted my time completely.

This array formula will do what you need. Hopefully you can see how to adapt it to add on other sheets. You should consider using a Named Range for column A in each monthly sheet to make the formula more adaptable when extra rows are added in.

Formula:

=IFERROR(IFERROR(IFERROR(

INDEX(jan!A:A,SMALL(IF(jan!$A$2:$A$5="a",ROW(jan!$A$2:$A$5)),ROWS($1:1))),

INDEX(feb!A:A,SMALL(IF(feb!$A$2:$A$5="a",ROW(feb!$A$2:$A$5)),ROWS($1:1)-(SUM(--(jan!$A$2:$A$5="a")))))),

INDEX(mar!A:A,SMALL(IF(mar!$A$2:$A$5="a",ROW(mar!$A$2:$A$5)),ROWS($1:1)-(SUM(--(jan!$A$2:$A$5="a"))+SUM(--(feb!$A$2:$A$5="a")))))),

"")

copied across and down

NOTE: This is an array formula. Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

You need to go and provide a link to this thread on the other forum where you have posted the same query, so that nobody ends up wasting their time.

Yes.

However, I have split it up (ALT-ENTER) so that you can see how it builds up. How many sheets/rows do you have? A VBA solution might be needed: in which case, I'm not going to be of any help!!

I am... At least I didn't waste my time !!!

It's a lot... You would need to be very careful setting it up. It would be SOOOO easy to leave out a ) or two. However, I do know that others have got it working on workbooks with 12 sheets (one per month). It might be easier, however, to assemble ALL your data onto one sheet (hidden, if needed) and then do a simpler index-match.