Concatenate Cells that do not line up 1

[bmacbmac]

Hello,

I am trying to build a boiler plate script and would like names from a list inserted. My text/script is several lines long in excel. My spreadsheet looks like this:

A B
1 NAME1 SCRIPT TEXT1
2 NAME2 SCRIPT TEXT2
3 NAME3 =concatenate(SCRIPT TEXT3, A1)
4 NAME4 SCRIPT TEXT4
5 NAME5 SCRIPT TEXT5
6 NAME6
7 NAME7 SCRIPT TEXT1
8 NAME8 SCRIPT TEXT2
9 NAME9 =concatenate(SCRIPT TEXT3, A2)
10 NAME10 SCRIPT TEXT4
11 NAME11 SCRIPT TEXT5
12 NAME12

I am looking for a way I can copy all 5 lines of my script one after the other where my concatenate script will grab each name. I'd like the concatenate to grab A1, then A2, then A3, then A4 each time I copy it.

As it is now, if I copy, my concatenate will use A1, then A9, then A13, etc. And all of the names in between are missed.

Does anyone know of a way I can do this?

My script is about 40 lines long and I have a list of approximately 1000 names.

Thanks!

 

[SkipVought]

Hi,

Clear as mud.

Please post a sample of the data from column A, that illustrates ALL the issues that you are faced with and the RESULT you expect in column B

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bmacbmac]

Sure.. here you go. Please note the subname in line 4 of my script in Col B pulling from Col A. My script is much longer than this, but the sample should give a general idea. Thanks for your time.

Col A Col B
1 BLACK CROOK UPDATE TBLDOCUMENT
2 BLACK DIAMOND SET SZCOMMENT = 1
3 BLACK EAGLE WHERE TITYPE = 1
4 BLACKBIRD AND SUBNAME = BLACK CROOK
5 BLACKHAWK
6 BLUE BELL T7-R6 UPDATE TBLDOCUMENT
7 BLUE BUCKET SET SZCOMMENT = 1
8 BLUE ROCK WHERE TITYPE = 1
9 BLUE ROCK 3 AND SUBNAME = BLACK DIAMOND
10 BOSTON
11 BRUISER UPDATE TBLDOCUMENT
12 BUFFALO SET SZCOMMENT = 1
13 BULL DOG WHERE TITYPE = 1
14 CATHERINE AND SUBNAME = BLACK EAGLE
15 CAVANAH
16 CENTERVILLE T6-R5 UPDATE TBLDOCUMENT
17 CENTERVILLE T7-R5 SET SZCOMMENT = 1
18 CHAMPION WHERE TITYPE = 1
19 CHARLOTTE AND SUBNAME = BLACKBIRD
20 CHENEY
21 CHICAGO UPDATE TBLDOCUMENT
22 CHIEFTAN SET SZCOMMENT = 1
23 CHIHUAHUA WHERE TITYPE = 1
24 CHRISTMAS AND SUBNAME = BLACKHAWK
25 COLUMBIA
26 COMEBACK 1 UPDATE TBLDOCUMENT
27 COMEBACK 10 SET SZCOMMENT = 1
28 COMEBACK 11 WHERE TITYPE = 1
29 COMEBACK 12 AND SUBNAME = BLUE BELL T7-R6
30 COMEBACK 14
31 COMEBACK 16 UPDATE TBLDOCUMENT
32 COMEBACK 3 SET SZCOMMENT = 1
33 COMEBACK 6 WHERE TITYPE = 1
34 COMEBACK 7 AND SUBNAME = BLUE BUCKET
35 COMEBACK 8
36 CONFEDERATE
37 CROCKER
38 CROWN POINT 1

 

[bmacbmac]

sorry, I forgot to post the issues I am faced with:

I start the script on B1 as follows:
B1 UPDATE TBLDOCUMENT
B2 SET SZCOMMENT = 1
B3 WHERE TITYPE = 1
B4 CONCATENATE("AND SUBNAME = ", A1)

The problem is that if I copy the script and paste it several times it pastes as:

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A6)

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A10)

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A15)


I would like it to paste as:

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A2)

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A3)

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A4)

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A5)

UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
CONCATENATE("AND SUBNAME = ", A6)

Or find a way to insert each line from Col A into the 4th row of text in column B.

 

[straybullet]

Use an absolute reference such as
B4 CONCATENATE("AND SUBNAME = ", $A$1)

You should be able to use Edit -> Fill -> Series rather than copy/paste. Just experiment with it a little.

Let them hate - so long as they fear... Lucius Accius

 

[SkipVought]

All in ONE CELL
[tt]
B1:
="UPDATE TBLDOCUMENT
SET SZCOMMENT = 1
WHERE TITYPE = 1
AND SUBNAME = '"&A1&"'"
[/tt]
Note the ' delimiters around the cell reference STRING

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bmacbmac]

No luck with the Fill option. I'll keep trying.

Thanks for the info Skip, however, as mentioned earlier my script is about 40 lines long and exceeds the 255 character limit in one cell.

 

[SkipVought]

"as mentioned earlier ... exceeds the 255 character limit in one cell."

That significant particular was NOT mentioned earlier.

Let's start from the top.

What's the Business Case for this requirement?

What are you trying to accomplish?



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bmacbmac]

Sorry, I mentioned that my script was 40 lines long, not that it exceeded 255 characters. My apologies.

The application is for a SQL script I have to run against 1000 names in my database (Col A in the sample above). The script is pretty lengthy and the name in column A is referenced a few times within.

I'd like to run this all as one big script so I don't have to run each one seperately.

 

[SkipVought]

I do this almost every day in my line of work. However, I use VBA and a named range for the list, along with a sheet with a QueryTable using MS Query via Data > Import External Data....

If you would care to pursue a VBA solution, please repost in Forum707

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

There are more questions.

Do you return data for only ONE list item at a time or ALL the items in the list?

If the answer is ONE, then it can be dome without any VBA code, but a different method than you appear to be using.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bmacbmac]

Only ONE list item at a time.

 

[SkipVought]

On a SEPARATE sheet, insert a querytable to you soruce database as a PARAMETER query; that is, in the Query Grid CRITERIA for SUBNAME, enter

[enter a value]

This creates a parameter.

Edit > Return data to Excel

Put the querytable in A3, for instance.

In A1, Data > Data Valifation - LIST -- and reference the LIST Named Range on your other sheet.

This will create an in-cell drop down box.

select the querytable and Data > Import External Data > Parameters. Select A1 as the parameter cell and check the box to first the query when the cell changes.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bmacbmac]

Wow... a little out of my skill level. I think I'm just going to build a macro to copy and paste my names into a big ole' script.

Thanks for your time.

 

[SkipVought]

This is REALLY simple stuff. No VBA (or very little if you wanted to LOOP).

It is BASIC spreadsheet functionality.

Named Ranges faq68-1331

Data > Import External Data

Data > Validation

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Gavona]

Wow... a little out of my skill level.

Like many things it sounds difficult because it is new. However, now I have tried a parameter query - encouraged by Skip's explanation and with a bit of support from the help file I agree with Skip - it is quite simple stuff - simpler than VBA.

---->*

Gavin