Combination of letter and numbers in a column

Hi guys,

I'm new to this forum but I'm quite desperate for this so I'll go straight ahead to the point!

I have two columns in Excel. The first one contains a sequence of letters and the second one contains 5 numbers (1,2,3,4 and 5). So it kind of looks like this:

A 1
B 2
C 3
D 4
E 5
F
G
H
...

I want to be able to combine every letter in the first column with each of the numbers and output this into a single column. In addition, I need a blank row in between the series. In other words, I need this:

A1
A2
A3
A4
A5

B1
B2
B3
B4
B5

and so on.. I've been thinking about this for the better part of today but still can't think of anything..

I would appreciate any ideas!!

Thank you!
Kind regards,
Antonio

The concatenate formula will solve your first problem.

Please Login or Register  to view this content.

I don't know how to put spaces in without using VBA.

The concatenation is the easy bit, it's the sequence A1 to A5, B1 to B5, C1 to C5 in a single column output that I'm struggling with.

Attachment 394996
You could use a pivot table to do it. If you make the pivot table and then go to report layout you can change it to outline, or tabular form. Either one will organize the results and put a space between each section. If you don't want to keep it in a pivot table for everyone else you can always copy and paste the values out.

in column C ... =IF(MOD(ROW(),6)<>0,INDIRECT("A"&(INT(ROW()/6)+1))&MOD(ROW(),6),"") copy down

Formula:

Please Login or Register  to view this content.