excel 2003 - convert indent to index

[firegambler]

Hi guys,

I wonder if you could please help me with this problem.

I have an excel file with about 800 lines in a column that
have a hirarchy shown as indent of the content of a cell.
like

item 1
item 2
item 3
item 4
item 5
item 6
item 7
item 8
item 9


The problem I have is that all items are in the same column but i would need them in one column per hirarchy level.
I.e. Items 1,7 in column A,
items 3,8,9 in column B,
items 4,5 in column C.

The hirarchy is currently shown via the indent of the cell content.

Is there a way to distribute the content of the column to various columns according to the indent or at least have a function that returns the indent of a cell's content?

Thanks a lot in advance!

 

[SkipVought]

Hi,

What is the reason for putting the items in defferent columns? It's not a particularly good idea in general. It might be better to store the indent value instead.

At any rate, I can't see any wayt of doing this using native Excel functionality. Rather, it will take VBA (macro) code. VBA questions are addressed in Forum707.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[firegambler]

Never mind,

got it solved by a colleague:

Sub Macro2()
Dim row As Integer

row = 1

Do While row < 750
Cells(row, 3).Value = Cells(row, 1).IndentLevel
row = row + 1
Loop

End Sub

 

[firegambler]

Hi Skip,

thanks for your reply,

i need this for a profit and loss statement,
and the distribution of the items is used for the functionality of subsumming the line items.

thanks for the comment on vba

cheers

 

[SkipVought]

That is the way to go; recording the indent level. Then you can use the indent level to categorize your aggregations in a pivot table quite easily.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Salut39]

My first reaction was to use =LEN() to group them and then combine it with IF function to place in the right columns. That's without VBA.

Yuri

 

[AnotherHiggins]

Maybe I'm missing something, but it seems to me that this could easily be accomplished using Data > Text to Columns.

Choose Delimited, select Space as the delimiter and make sure that Treat consecutive delimiters as one is [!]un[/!]checked.

The only problem you'll run into is if you acually have spaces in your headers (like "item[red]_[/red]1").


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.