0

In Excel, I have two columns of data that I wish to combine.

Current set of data:

+---------+---------+
| column1 | column2 |
+---------+---------+
| a       | 1       |
| b       | 2       |
| c       | 3       |
| d       | 4       |
|         | 5       |
|         | 6       |
|         | 7       |
+---------+---------+

For each value in column1, I need to assign all of the values in column2 so it looks like this:

+---------+---------+
| column1 | column2 |
+---------+---------+
| a       | 1       |
| a       | 2       |
| a       | 3       |
| a       | 4       |
| a       | 5       |
| a       | 6       |
| a       | 7       |
+---------+---------+
| b       | 1       |
| b       | 2       |
| b       | 3       |
| b       | 4       |
| b       | 5       |
| b       | 6       |
| b       | 7       |
+---------+---------+
| c       | 1       |
| c       | 2       |
| c       | 3       |
| c       | 4       |
| c       | 5       |
| c       | 6       |
| c       | 7       |
+---------+---------+
| d       | 1       |
| d       | 2       |
| d       | 3       |
| d       | 4       |
| d       | 5       |
| d       | 6       |
| d       | 7       |
+---------+---------+

How can I do this?
Do I need to find a macro/VB solution?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3582281
  • 1
  • 1
  • 2
    Maybe this will help: http://www.contextures.com/excelmsquerycartesian.html – cpt. jazz Jul 09 '14 at 20:49
  • @guitarthrower - I'd tried many different searches, but I couldn't quite get the right combination of search terms to pull up when I needed. Cartesian query was the ticket. – user3582281 Jul 09 '14 at 21:06

1 Answers1

0

Since seems unlikely to receive any other answer:

in A1: a
in B1: =MOD(ROW()-1,7)+1
in A2: =IF(MOD(ROW()-1,7)>0,CHAR(CODE(A1)),CHAR(CODE(A1)+1))

Copy both formulae down to suit.

pnuts
  • 58,317
  • 11
  • 87
  • 139