VBA to automate lookup formula based on 2 changing criteria.

[SteynBS]

Hi Guys,

I am in the process of automating a sheet for convenience, yes, but to save time as the preparation is a pain.

Firstly I have data in my "Generic" sheet Column A that I want in my "ATTYP" sheet as a header. I am doing this with =TOROW(UNIQUE(GENERIC[SITE_REP]),0,FALSE)

So I get my headers, copy and paste the values and convert the range to a table. This I can manage with a VBA, but. Now I have to put in my INDEX MATCH formula. The 1st condition is G1 (Header) to match with "Generic" sheet Column A. The Second Condition is "ATTYP" Sheet C2 to match with the "GENERIC" Sheet Column C - the formula looks something like this =INDEX(GENERIC[SATNR],MATCH(1,(GENERIC[SITE_REP]=Table3[[#Headers],[S011]])*(GENERIC[SATNR]=ATTYP[@LEFT]),0))
My problem is the 1st condition, the header will never be the same value, and headers G:K will either be less or more. So I need this formula to adapt according to the number of columns there will be and the value that will be in row one for the corresponding column. This formula has to repeat in each column that will have a header.

Hope it makes sense, and any assistance will be highly appreciated.

 

[jdellasala]

Forget VBA and Formulas. Pull the first table into Power Query, change it as needed and then load the transformed table. Once done you can simply hit Refresh All to update the transformed table with new data. Power Query is easy to use (MUCH easier than VBA) and well worth the time to learn. Great playlists on it can be found here and here.

 

[SteynBS]

Hi

Forget VBA and Formulas. Pull the first table into Power Query, change it as needed and then load the transformed table. Once done you can simply hit Refresh All to update the transformed table with new data. Power Query is easy to use (MUCH easier than VBA) and well worth the time to learn. Great playlists on it can be found here and here.

Hi,

My first table in the screenshot is pulled in via Power Query. Much of the data in the workbook comes in through Power Query. The second table is purely a process of elimination to select specific sets of data via the formula I demonstrated.