VBA to incorporate additional data into a data set

Hi all,

I have some data and need to incorporate additional detail into it. There are two separate data sets to be incorporated into the main data, region and specialism data.

I've attached an example of the data I'm working with along with an example which shows what I'm looking to achieve.

If you have any queries just give me a shout.

Thanks in advance,

Snook

Cheers Olly. Now seems a good time to dip my toe into the Power Query scene so I've requested the add-in to be installed on my laptop.

I'll let you know how I get on.....

Hi Olly,

I've just looked at the output you produced based on my sample data and it has grown from ~30 rows to ~950. The actual data I'm working with is ~5500 rows and the regions and specialisms will be far more extensive than my example. As such I'm concerned that I'll blow Excel's mind! As in my resultant data set will exceed the max rows (~1m).

Any suggestions on what alternative approaches/solutions are available to me if this is the case?

Cheers,

Snook

I'm helping someone out in work but I believe that the ultimate goal is to produce some sort of pricing tool.

We'll have a dataset of all the suppliers, regions, specialisms etc and customers will be able to use the tool to query what rate they would have to pay for certain requirements, e.g. a teacher based in the North East.

The current one is Excel based and I believe that this will need to be the case for the new one. It will be used by various customers with varying IT setups but they'll all have Excel as a minimum. They're unlikely to have any associated add-ins though so it really needs to be a 'no frills' solution (VBA is ok).

Thinking out loud we may just have to split the dataset down by one of the elements (probably lot) and create separate tools.

It obviously hinges on the size of the dataset though so I'll see how the land lies once IT have installed PQ on my laptop.

Lol yeah I know what you mean. Thankfully that won't be my problem!