excel for each ..next

  • Thread starter Thread starter Gabriel
  • Start date Start date
G

Gabriel

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,

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
 
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
 
Back
Top