Hi,

I have the following table (in reality it is quite large) in an excel
wksheet:

1 1 1
2
2 1
2
2 1
2 1
2
3

I want to write a procedure such as I could obtain in another wksheet
the following result:

1_1_1
1_1_2
1_2_1
1_2_2 and so forth. There is a pattern here. Is there is a way to set
up a procedure to take advantage of this pattern? Does anyone has a
hint?

Thank you in advance
Gabriel

Copy your worksheet to its new home.

Then select your range (A1:C999???).
then Edit|goto
click special
select Blanks

hit the = (equal sign)
hit the up arrow
hit ctrl-enter

Select your range and copy|paste special values if you want.

Debra Dalgleish has nice pictures at:
http://www.contextures.com/xlDataEntry02.html

And I'm not quite sure what happens in that 3rd column. You may not want to
include that in your selection (if you want to keep the blanks there).



Gabriel wrote:

Hi,

I have the following table (in reality it is quite large) in an excel
wksheet:

1 1 1
2
2 1
2
2 1
2 1
2
3

I want to write a procedure such as I could obtain in another wksheet
the following result:

1_1_1
1_1_2
1_2_1
1_2_2 and so forth. There is a pattern here. Is there is a way to set
up a procedure to take advantage of this pattern? Does anyone has a
hint?

Thank you in advance
Gabriel

--

Dave Peterson

Gabriel,

Formula wise (assuming that your values are in A, B & C).
In D1, enter the following:
=IF(A1=0,1,A1)&"_"&IF(B1=0,1,B1)&"_"&IF(C1=0,1,C1)
and copy down

John

"Gabriel" wrote in message
om...
Hi,

I have the following table (in reality it is quite large) in an excel
wksheet:

1 1 1
2
2 1
2
2 1
2 1
2
3

I want to write a procedure such as I could obtain in another wksheet
the following result:

1_1_1
1_1_2
1_2_1
1_2_2 and so forth. There is a pattern here. Is there is a way to set
up a procedure to take advantage of this pattern? Does anyone has a
hint?

Thank you in advance
Gabriel

Hi,

First of all, thank you all for the hints. I think I oversimplified my
problem when I asked for help.

In fact, I have a hierarchy in excel that looks like this
A B
C D
E
F
G H I

I have a hard time creating a sub that recognizes the relative position
of each member(child) in the structure. Ideally, each element should
state the descendency( in lack of a better word)in the tree. For
instance, each cell in my range (D for instance) should recognize that
it's 1_2_1 and H that it's 2_1. It's really like a tree..

One more precision, the elements are not related in any way other that
their relative position in the hierarchy.

Someone care to help
Gabriel

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!