hopefully simple problem

asker2

New Member
Joined
Dec 18, 2005
Messages
5
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
 

Some videos you may like

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
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Dec 18, 2005
Messages
5
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
Joined
Feb 1, 2003
Messages
17,832
asker2 said:
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
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

New Member
Joined
Dec 18, 2005
Messages
5
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, :biggrin:
Alan
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,503
Members
412,671
Latest member
xcihan
Top