Dynamic select of columns based on values from another table

[Atson]

All,

I am looking for a solution to the below use case:

Table 1

Region    Country

Asia         India

Asia         China

Asia         HK

EMEA      UK

EMEA      SWISS

EMEA      France

Table 2

Region    India      China        HK       UK        Swiss      France

Asia             5           10          15

EMEA                                                  5             10            15

I am looking for output as below

Asia

India      China               HK

5              10                  15

EMEA

UK      Swiss           France

5           10                15

Appreciate any support on this. Thanks

[Qiu]

@Atson 
Do you think you can present your intput and output in an Excel file, which will easier to understand.

[flying008]

Hi, @Atson 

Like this ?

[Atson]

Main has the primary data.

Based on the data in the Source, only the required columns based on the specific region, columns has to be populated on the Output file

[AndrewDMerrill]

I agree with @Qiu 

Just for fun, I threw this together:

But more information will be needed about your specific use case to provide more definitive assistance, including expected input and output data.

[Qiu]

@Atson 
We can use a simple batch macro to process the data for each region then output to an Excal Sheet.
And I dont see what is the use of data in the Main file, since it seems that all the required data is already in the file "Source"

[Atson]

Thanks Qui, for the support.

Yes, but there are instances when there will be a value in the Country's field that is not part of the region. E

Eg. Region "Asia", there will be value in "UK" which it should not read.

So based on the source file, only those countries that are part of the region, should be part of the output.

[Atson]

Can this be done where we see countries based on regions?

[binuacs]

@Atson try the attached batch macro