counting common data and further split with pre-determined ratio by country with a ID code

[shivanand2011]

I have a sample file attached where the column description as follows -

column-A: a unique group of data, when counted it gives value of 78

column-B: Identifier for specific ID of group to map the reference table on column F to H.

column-C: is the results should appear when dragged or any automated way to populate the result.



the method or rule user story as follows -

coulum-A cells should be counted and outcome counted number is referenced with Identifier code. next step the identifier code has split ratio of 10% to UK and 90% to US. It should split that total count value and populate the column-C with each split value as result with country name.

[jason.b75]

Please attach your sample as a workbook, not a zip file.

If your workbook is too big to attach without compressing, then try using a smaller data sample. If you have 5000 rows of data, then cut it down to around 200 rows, that should be enough to demonstrate what you are trying to do.

[shivanand2011]

Thanks for your reply, please find the attached compressed format of sample file for simulation.

[jason.b75]

If it is only ever 2 countries, in the format shown then you could simplify this formula, but on the assumption that the task is going to be more complex, see if this does what you need.

Enter the formula into C2, then fill down.

Please Login or Register  to view this content.

edit:-

The simple version, if it will do what you need.

Please Login or Register  to view this content.

[shivanand2011]

Hello Jason,

Very kind of you to reply to this message. Let me thank you and appreciate your kind support with this challenge.

The problem is much complex with different combination/set of data to work through. I have attached elaborate scenario with multiple concatenate groups coming and further that need to be matched with similar #Identifier coder as well.
could we have some solution for this? I have attached the scenario file for your reference.

kind regards,

[jason.b75]

A couple of facts to establish.

First of all, there are a couple of things that suggest that this is an assignment question, i.e. related to a college / university course / qualtification. In which case we would need to see the full question to be certain that any advice given to you is relevant to any specified mehods that you need to use.

Will there ever be more than 2 countries / results in any single row of the split ratio group?

How should decimal portions of the 'Result to' be rounded?

There may be other points that will need to be clarified later.

[shivanand2011]

Hello Jason,

this is an project assignment for my graduate course I am working on. We need to distribute the activities based on certain countries by given ratio.
Yes, there will be multiple countries as well, as more than 2, perhaps not more than 3, 4, 5(maximum is 5).

the round off can we done based on decimal value greater than or equal to 0.8 as forward value, less than that value can be previous value.

Thanks for your swift reply.

[jason.b75]

Given that it is a course assignment, there are some basic guidelines that we follow, as detailed in the canned (scripted) reply that I have posted below.

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.