CODEWITHSUNDEEP
excelvba
Carlos Rangel
Carlos Rangel

Reputation: 21

How to Concatenate, Increment and create new lines from concatenated date?

I am currently working in advertising and to generate complex campaigns we use a "Bulk Tool", in which we require to write the date manually and makes us lose a lot of precious time.

Here is what I am trying to do in Excel:

A form with this data:

PO Number: B2
Format: B3
Campaign Name: B4
Target Names:
Amount of Copys: B10 (Where B10 is a value between 1 and 10 for example)
Amount of Visuals: B11 (Where B11 is a value between 1 and 10 for example)

I believe the first step to automatize this procedure is to start by concatenating the data, which is done with:

=concatenate(B1,"_",B2,"_",B3,"_","T"B10,B11)

which should produce something like this:

165_ILF_TEST_T4_5

My real issue is that I actually have 4 copys and 5 visuals, which means that what I would like to be able to create automatically is this:

165_ILF_TEST_T1_1
165_ILF_TEST_T1_2
165_ILF_TEST_T1_3
165_ILF_TEST_T1_4
165_ILF_TEST_T1_5
165_ILF_TEST_T2_1
165_ILF_TEST_T2_2
165_ILF_TEST_T2_3
165_ILF_TEST_T2_4
165_ILF_TEST_T2_5

Any idea if this would require using loops within excel? I have strictly no idea how to perform it, although there's been some intensive thinking about this.

Thank you very much for your help,

Carlos

Upvotes: 2

Views: 1886

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

Place this formula in cell A1 (or any other cell in row 1) and copy it down till row 100:

=IF(ROW()>$B$4*$B$5,
   "",
   $B$1&"_"&
   $B$2&"_"&
   $B$3&"_T"&
   INT((ROW()-1)/$B$5)+1&
   "_"&MOD(ROW()-1,$B$5)+1
)

Upvotes: 1

Related Questions