Combining two lists - and creating rows with data from these lists

[Vilep]

Hi

I have a list of regions with some characteristica and another list of sales persons, also with some characteristics.
Based on the sales persons, I now want to - automatically/by formula - create a new third list showing details of the assigned regions, so e.g. person ABC gets three rows (stops with row where next row Region=x)
with characteritics from both list 1 and list 2.

Is this possible?

Thank.

 

[SanjayGMusafir]

The kind of output you want is not difficult. The only challenge is that in your sample data you have 2 salesperson for Europe. This means the real data can have many such challenges to address. How do you define which salesperson is allotted where (which region)?

If that rule can be defined then we can think of ways to combine data.

Also, it is requested to submit even the sample data using XL2BB (link underneath) so that it saves effort to create a sample data and then work on a solution.

 

[Vilep]

Thanks.
I, periodically, get a list of sales persons per region (as well as list with current regions), and yes, multiple regions often have multiple salespersons.
NB: The property 4 (or other properties from sales person) may also be relevant on the lines for countries for the new list.


NNB: I tried to install XL2BB, but there where macro restrictions and the add-in currently doesn't have the functions. I'll try to uninstall and get it working.

 

[SanjayGMusafir]

multiple regions often have multiple salespersons

Understood your point. But challenge is how to identify which property is assigned to which salesperson? If you can give some idea on that rest is very simple...

We have to think the way we want excel to think or analyze.

 

[Vilep]

The list of salespersons contains the same columns (properties) for all the persons, e.g. columns Id (property 4), name of person (Property 5) and direct manager (property 6).

 

[SanjayGMusafir]

The list of salespersons contains the same columns (properties) for all the persons, e.g. columns Id (property 4), name of person (Property 5) and direct manager (property 6).

Honestly with the given information I won't be able to help. I didn't get the answer about how to tell excel that where to assign which sales person.

Hope it catches eye of some expert who may be able to help you.

 

[Vilep]

Maybe you wanted this info:
In the list of sales persons there is a column Region. This column relates to the column Region/Country in the Region table.
So when same value (e.g. Europe) and Region=X in "the region list", then create records for this region and countries combining data from the two lists.