Excel - Export Data from Column A into a text file based on Column B

Question

Apologies in advance for my inability. I honestly have no idea how to handle this! I've only just started doing VBA, and only for specific tasks. So far Google and StackOverflow has provided what I was looking for, but I cannot find anything that explains what I am trying to do.

Any advice would be greatly appreciated!

I am looking to populate a number of SQL scripts using data from Excel.

In one column there is a list of usernames. In an adjacent column, there is a list of locations.

I have a template of a SQL script, and I need a TXT file generated for each location, with the relevant users inserted.

For example, if this were the data set: Table Example

And this is the script template:

LOCATIONA

update USERTABLE set ACCOUNT_LOCKED = '1' where USERID in (%LOCATIONA_USERS%);

LOCATIONB

update USERTABLE set ACCOUNT_LOCKED = '1' where USERID in (%LOCATIONB_USERS%);

The output would need to be like this:

LOCATIONA

update USERTABLE set ACCOUNT_LOCKED = '1' where USERID in ('User1','User2','User3','User4','User5');

LOCATIONB

update USERTABLE set ACCOUNT_LOCKED = '1' where USERID in ('User6','User7','User8','User9','User10','User11','User12');

It could either export to a single file, but with a separate script for each location, or a separate file per location.

I imagine for someone who knows, this is probably really simple! However I am not that person!

Thanks in advance!

Answer 1

I have created a similar dataset, with some formulas. You can inspire yourself on this in order to get what you need:

• Column A contains the names of the locations.
• Column B contains the names of the users.
• Column C filters on "LocationA", the formula is shown in C1.
• Column D filters on "LocationB", the formula (shown in D1) is similar to the one in C1.
• A TextJoin based formula is used for collecting the list of users, both for "LocationA" and "LocationB", the formula (=TEXTJOIN(",",TRUE,C2:C6)) is shown preceeding the values: