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

Dynamic Processing

Accepted answers

All comments

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 ?

录制_2023_11_14_16_29_16_8.gif

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

Main.xlsx

Source.xlsx

Output.xlsx

CoG

I agree with @Qiu

Just for fun, I threw this together:

2023-11-14_02-38_Country Output.png

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

2023-11-14_02-38_Country Output.yxmd

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"

1114-Atson.yxzp

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?

binu_acs

@Atson try the attached batch macro

Region_141123.yxzp

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335