hopefully simple problem

[asker2]

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

 

[erik.van.geit]

Hello, asker2
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

 

[asker2]

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]

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]

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 !
TIP1: always check your posts
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

 

[asker2]

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]

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