Excel Columns - concatenate rows of each column

Question

I have an Excel workbook with 6 worksheets. I need to read each worksheet and perform the same process on the data and produce 6 Excel worksheets as output.

I have a few columns: Market, Plant, Email etc.  The number of rows in each column vary.  I would like to have a comma separated list of the market names in single quotes so I can use them in a SQL SELECT query IN clause.

So, if there are 3 markets in column A, cells A2, A3 and A4 = Market1, Market2 and Market3 respectively I want the value 'Market1','Market2','Market3' so I can use it in a SQL query.

And same for Plant in column B; there could be 10 plants listed in it and so on.

Market       Plant      Email

----------------------------------
Market1     Plant1    Email1
Market2     Plant2    Email2
Market3     Plant3   Email3
                  Plant4   Email4
                  Plant5   Email5
                  Plant6
                  Plant7
                  Plant8
                  Plant9
                  Plant10

Please help.

Regards,

Indrani

DavidP · Accepted Answer

Hi @dindrani

There's 2 parts to this: art one is to load all the sheets and part 2 is to create the content for your SQL statement. I guess part 3 would be to write the output to an excel file, but let's focus on 1 and 2. If all the sheets have the same column structure, you can use a dynamic input tool as shown here. If not you'll have to use a batch macro - let me know if that's the case.

Sample workflow attached. Please have a look and let me know if you need help to adjust it to your use case.