# hopefully simple problem

##### New Member
sounds simple, but I have not been able to write a formula (for problem below) which can be extended down through a long column by using EDIT-FILL DOWN.

The problem is to take an existing long column of different numbers (COL A), and create a second column (COL B) with each number from COL A repeated once. e.g. 1,1 2,2 3,3 4,4 etc.

thanks for any help in creating formula to accomplish this

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### erik.van.geit

##### MrExcel MVP
Welcome to the Board !!!!!

this is not really clear to me

what would be the results in B1 ... B5 (?) when column A has
A1: 1
A2: 5
A3: 5
A4: 1
A5: 2

kind regards,
Erik

##### New Member
here is what I expect col A and col B (after applying formula) to show:

col A col B
1 1
2 1
3 2
4 2
5 3
3
4
4
5
5

#### erik.van.geit

##### MrExcel MVP
here is what I expect col A and col B (after applying formula) to show:

col A col B
1 1
2 1
3 2
4 2
5 3
3
4
4
5
5
is this a response to my question or a new example ?
can you explain why col B stops at 3 ?

#### erik.van.geit

##### MrExcel MVP

Code:
``````col A  col B
1       1
2       1
3       2
4       2
5       3
3
4
4
5
5``````

now I see
your example is not displayed as intended !
TIP2: for such little samples you can use the codebutton (above your messagebodywindow when writing a post)
else check the HTMLmaker link at the bottom of the page

you would need this formula
=INDIRECT("A" & TRUNC((ROW()+1)/2))

kind regards,
Erik

##### New Member
Erik,

That's a beautiful display of your knowledge of EXCEL. Way over my capabilities, and I do very much appreciate your time and effort.

Great solution,
Alan

#### erik.van.geit

##### MrExcel MVP
Way over my capabilities
take a look at INDIRECT in the helpfiles
take a look at ROW
the other stuff is just mathematics or "trial and error"
then once it will be way under your capabilities

Replies
2
Views
86
Replies
1
Views
91
Replies
1
Views
174
Replies
0
Views
33
Replies
0
Views
158