find and replace column names based on other input file

[Kutay]

Hi All,

I get weekly raw data and separate  files for vlookup. The input file have more than 700 columns and looks like this:

userID	validityOfinterestAffinity_1	validityOfinterestAffinity_2	UDF_cookieMatches__array_1__4__8__10__14__24__12__20__90__128__130__140___20_1	UDF_cookieMatches__array_1__4__8__10__14__24__12__20__90__128__130__140___20_0	UDF_cookieMatches__array_1__4__8__10__14__24__12__20__90__128__130__140___1_0	UDF_cookieMatches__array_1__4__8__10__14__24__12__20__90__128__130__140___1_1
12345	0.2				z14c473e2-3acf-41d7-a34b-384b555e80c6	t42t473e2-3acf-41d7-a54z-384b555e80c6
56789	0.12453	1	a4c873e2-3acf-41d7-a34b-384b754e70a0			k12c463f2-3acf-22d7-a34b-384b655e30c8
11222	0.567568342			b4c473e2-3acf-41d7-a34b-384b555e80b3

validityOfinterestAffinity columns are going up to 700 and udf is up to 20. None of the columns are fix, which means I might get one week only validityOfinterestAffitinity_1 and validityOfinterestAffitinity_500 the other week from validityOfinterestAffitinity_253 to validityOfinterestAffitinity_293 same for the udf columns.

Vlookup files are also dynamic and the definitions of those numbers might change from one week to another as well.

Vlookup file for validityOfinterestaffinity looks like this 

Taxonomy ID	Taxanomy
1	cars
2	autoparts
3	music

...

Vlookup file for UDF looks like this 

Partner ID	Partner
1	xandr
2	google
20	adscale

...

desired output that I am looking for is sth like this 

userID	validityOfinterestAffinity_cars	validityOfinterestAffinity_autoparts	adscale_1	adscale_0	xandr_0	xandr_1
12345	0.2				z14c473e2-3acf-41d7-a34b-384b555e80c6	t42t473e2-3acf-41d7-a54z-384b555e80c6
56789	0.12453	1	a4c873e2-3acf-41d7-a34b-384b754e70a0			k12c463f2-3acf-22d7-a34b-384b655e30c8
11222	0.567568342			b4c473e2-3acf-41d7-a34b-384b555e80b3

Thank you so much for your help!

Best,

Kutay

[wwatson]

Not knowing your data very well I would suggest this could be a job for the dynamic rename tool found in the developer toolbar:

[ImadZidan · Accepted solution]

Hello,

I can see what you are trying to do. However, according to the data provided, the UDF configuration is slightly different. I followed the pattern provided.

Well, have a look, it will hopefully give you an idea or a way forward.

I hope it helps.

[DavidP · Accepted solution]

Hi @Kutay 

This is how I would do it based on the data you provided