concatenating different rows

[nehachoubey]

Hi, i have a sheet with 2 columns and around 100 rows. I have to concatenate them like this:
select row1col1value from row1col2value union select row2col1value from row2col2value...

the final script should generate in different sheet. plz help

 

[Rick Rothstein]

Hi, i have a sheet with 2 columns and around 100 rows. I have to concatenate them like this:
select row1col1value from row1col2value union select row2col1value from row2col2value...

the final script should generate in different sheet. plz help

It is not clear to me what you want the final output to look like. Instead of 100 rows, lets say there are only 4 rows and these are the values in the two columns...

A B
1 2
3 4
5 6
7 8

What should the concatenated output look like?

 

[nehachoubey]

the output should be like:
select 'A','B' FROM DUAL UNION
select '1','2' FROM DUAL UNION
select '3','4' FROM DUAL .. so on

 

[Rick Rothstein]

the output should be like:
select 'A','B' FROM DUAL UNION
select '1','2' FROM DUAL UNION
select '3','4' FROM DUAL .. so on

Oh, the concatenations are on a row by row basis, not all rows into a single concatenation. Give this formula a try...

="select '"&Sheet3!A1&"','"&Sheet3!B1&"' FROM DUAL UNION"

Change the sheet name and cell references to match your first row of data and then copy the formula down as needed.

 

[nehachoubey]

no its not that easy, it should be handles though macros as there are few boundations like:
1. if the column 2 has blank value, it should not be considered. eg:
col1 col2
A

SELECT 'A' FROM DUAL UNION

2. If col2 has a value like 'Mention folder name' it should replace it with *.

and i want to create a bat file for this, by clicking it i will get a text file with the scripts. is it possible?? the batch file will take the path of the excel file as input.

 

[Rick Rothstein]

no its not that easy, it should be handles though macros as there are few boundations like:
1. if the column 2 has blank value, it should not be considered. eg:
col1 col2
A

SELECT 'A' FROM DUAL UNION

2. If col2 has a value like 'Mention folder name' it should replace it with *.

and i want to create a bat file for this, by clicking it i will get a text file with the scripts. is it possible?? the batch file will take the path of the excel file as input.

We could easily handle your first point with a formula like this...

="select '"&Sheet3!A1&IF(OR(Sheet3!A1="",Sheet3!B1=""),"","','")&Sheet3!B1&"' FROM DUAL UNION"

And, of course, we can write a macro to do this as well; however, I am wondering what other surprises you might have for us like your in your second point. You never mentioned anything about replacing a phrase like "Mention folder name" with an asterisk in your previous posts... were we supposed to have guess that? What other text needs to be handled in a special way (you would have to list them for us) and what is the special way they will have to be handled (will it always be asterisks replacing them)? As for writing a BAT file... it has been many, many years since I have had to do that and I really do not remember the command structure nor syntax for BAT files any more. So, you will need to wait for someone with a better memory than I have to handle that part of your request. Sorry.

 

[nehachoubey]

I am so sorry for the missed out info.. could you plz help me with the macro. I will form the batch file..