Excel combination of letters and numbers

0
votes

I have an Excel challenge and would appreciate any support! HELP!

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..

Thanks in advance!!

Kind regards, Antonio

2
excelexcel-formula
Is it always A,B,C,D,E,F,G,H, and on... and always 1,2,3,4,5? – JNevill
The first column goes from A to let's assume infinity (in reality, it's something like AAX) and the numbers are just 1,2,3,4 and 5. Thanks!! – user3552829
Let's NOT assume it's infinity since a worksheet doesn't 5 × ∞ rows. Describing your situation in superlatives that have no meaning in fact is counterproductive to getting an intelligent focussed answer to your inquiry. – user4039065

2 Answers

2
votes

For a solution that doesn't rely on the contents of Column A or B (Which is useful, but might not fit your needs), it just happily increments as you copy down the formula down the rows:

=IF(MOD(ROW(),6)=0, "", SUBSTITUTE(ADDRESS(1, CEILING(ROW()/6, 1), 4), "1", "") &MOD(ROW(),6))

Put that in Row 1 of any column and copy down. Good times.

2
votes

If the data is as follows:

   A   B
---------
1| A | 1
2| B | 2
3| C | 3
4| D | 4
5| E | 5
6| F |  
7| G |  
8| H |  
  ... ...

And the desired output is this:

    A   B   C
 -------------
 1| A | 1 | A1
 2| B | 2 | A2
 3| C | 3 | A3
 4| D | 4 | A4
 5| E | 5 | A5
 6| F |...|  
 7| G |   | B1
 8| H |   | B2
 9|...|   | B3
10|   |   | B4
11|   |   | B5
12|   |   |
13|   |   | C1
...         ...

You can use this formula starting in C1 and auto-filling down:

=IF(INDIRECT("B"&MOD(ROW()-1,COUNT(B:B)+1)+1)<>"",INDIRECT("A"&INT(((ROW()-1)/(COUNT(B:B)+1))+1)),"")&INDIRECT("B"&MOD(ROW()-1,COUNT(B:B)+1)+1)

Note: This will have to be modified if the data does not start on row 1. But this works with any combination of letters and numbers.

Also there is no end case check here. So anything that is past H5 will result in just the numbers repeating. If an end case check is needed that can be easily added.