Extract values with the help of two criteria

Exactly te same question has been asked before on the forum ... but no idea how to find it!.

Maybe the respondent(s) can recall it!

UPDATE: it was asked by you !!

https://www.excelforum.com/excel-pro...her-sheet.html

.

Do you want in expected results sheet, all subjects and its employees, batches... are listed automatically one after one?
For example:
Finance
MrA
MrB
Marketting
MrC
MrD
...

I afraid its very hard for formula based solution.

The "basic Data" is the same as your previous post but there are 2 additions to the "Output": "Allocation of blocks" and "balance no. of blocks",

There is no explanation if/how these are derived

Plus you consolidated the "Toal Batch numbers" per department into a merged cell (which should be avoided).

You may still be looking at a VBA solution so awareness of the original post would have been helpful to avoid re-inventing the wheel.

Modification of Jindon's initial code in your previous post.

Please Login or Register  to view this content.

I don't know how D8 can be derived using formula: with great difficulty I suspect, not least as you require it sorted. With 365 it could be possibly be done. Beyond my pay scale

Re "Mismatch error": Mea Culpa! I removed the Date column from "Basic Data" to get the code to work as the earlier posts so I could reuse Jindo's code.

I will look at modifying the code to allow for added "Date" column.

Have you udpated to MS365?

147 views and no further offers of help - it would appear not, sorry.

What real "value add" is there in reformatting rather than simply sorting the "Basic Data" by "Department" then "Employee" ?

Surveys indicate up to 80% of time spent on Excel is simply for transforming data (and I suspect much of this is non-productive!)

You are expecting FAR too much from a formula. Splitting stop-separated data, merging, sorting and adding stops back in is diffcult enough with O365. It's pretty much impossible here. You might manage wth lots of helper columns, but it would still be hugely messy. Best option is VBA, I suspect (not my strong point, though).

@Glenn: A VBA solution was provide initially by Jindon in a previous post plus modification by me (of Jindon's code) to meet the request in this post.

So no idea what more the OP wants!

The OP wants a formula solution that he just isn't going to get. Probably ...

Please read post #19.