Outlining based on level numbers in most left column

Hi,

I have a table, which is downloaded from SAP and has no formulas.
The identifier of the rows is a project item number like "PRD.
00133.07-03-04" and
I count the number of "." and "-" with a formula to get the level of
the project item.
My goal is now to make an outline by vba using the level numbers, e.g.
4 in the case
described above.
The first row of the table is poulate with these level numbers like
1
2
2
3
3
2
1
2 and so on.

Does anyone have a vba procedure to solve this problem.
Thanks.

Excel XP SP3
Windows XP SP3

Best Regards
Werner
0
Werner
3/31/2010 5:03:25 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1156 Views

Similar Articles

[PageSpeed] 3

First, this needs xl2k or higher to run.  It uses Replace that was added in
xl2k.

Second, I'm not sure how your data is laid out, but I created a test worksheet
with the data to be examined in column B.  And I plopped the numbers into column
A.  So don't use this without modifying the code or your data if it doesn't
match!

Option Explicit
Sub testme02()
    Dim myCell As Range
    Dim myRng As Range
    Dim wks As Worksheet
    Dim HowManyChars As Long
    Dim WhatChars As Variant
    Dim cCtr As Long
    
    WhatChars = Array(".", "-")
    
    Set wks = ActiveSheet
    
    With wks
        Set myRng = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))
        
        For Each myCell In myRng.Cells
            HowManyChars = 0
            For cCtr = LBound(WhatChars) To UBound(WhatChars)
                HowManyChars = HowManyChars _
                                + (Len(myCell.Value) _
                                 - Len(Replace(expression:=myCell.Value, _
                                           Find:=WhatChars(cCtr), _
                                           Replace:="", _
                                           compare:=vbTextCompare))) _
                                    / Len(WhatChars(cCtr))
            Next cCtr
            myCell.Offset(0, -1).Value = HowManyChars
        Next myCell
    End With
    
End Sub



Werner Rohrmoser wrote:
> 
> Hi,
> 
> I have a table, which is downloaded from SAP and has no formulas.
> The identifier of the rows is a project item number like "PRD.
> 00133.07-03-04" and
> I count the number of "." and "-" with a formula to get the level of
> the project item.
> My goal is now to make an outline by vba using the level numbers, e.g.
> 4 in the case
> described above.
> The first row of the table is poulate with these level numbers like
> 1
> 2
> 2
> 3
> 3
> 2
> 1
> 2 and so on.
> 
> Does anyone have a vba procedure to solve this problem.
> Thanks.
> 
> Excel XP SP3
> Windows XP SP3
> 
> Best Regards
> Werner

-- 

Dave Peterson
0
Dave
3/31/2010 5:41:31 PM
Assuming I understand your counting method (more examples would have been 
helpful), I believe this macro will do what you want...

Sub LevelNumbers()
  Dim Cell As Range
  For Each Cell In Range("B2", Cells(Rows.Count, "B").End(xlUp))
    Cell.Offset(0, -1).Value = UBound(Split(Replace(Cell.Value, _
                               ".", "-"), "-")) - (Cell.Value = "")
  Next
End Sub

Note that I assume this code is being run on the ActiveSheet and that your 
ID numbers are in Column B starting in Row 2 (adjust the B2 references in 
the Range call and the "B" column reference in the Cells call, both in the 
For..Each statement as needed) and the level numbers are being placed in the 
preceding column (that's what the -1 is doing in the Offset call).

-- 
Rick (MVP - Excel)



"Werner Rohrmoser" <werner-rohrmoser@hotmail.de> wrote in message 
news:3e94a5d5-4574-448a-b3dc-8a29b9816f26@z4g2000yqa.googlegroups.com...
> Hi,
>
> I have a table, which is downloaded from SAP and has no formulas.
> The identifier of the rows is a project item number like "PRD.
> 00133.07-03-04" and
> I count the number of "." and "-" with a formula to get the level of
> the project item.
> My goal is now to make an outline by vba using the level numbers, e.g.
> 4 in the case
> described above.
> The first row of the table is poulate with these level numbers like
> 1
> 2
> 2
> 3
> 3
> 2
> 1
> 2 and so on.
>
> Does anyone have a vba procedure to solve this problem.
> Thanks.
>
> Excel XP SP3
> Windows XP SP3
>
> Best Regards
> Werner 

0
Rick
3/31/2010 6:21:44 PM
Oh, I'm sorry, I've described my problem insufficient.
My problem is not to count the numbers of "-" and ".", because this
ismade by a formula
which goes like this: {=SUM((MID(SUBSTITUTE(B6,"-","."),COLUMN(1:1),
1)=".")*1)}.

My table is organised by column this way:
Level  Criteria_A  Criteria_B  ........   Criteria_N.

So, based on the level (1, 2, 3,..., N) I'd like to make the same
which I have to do now
manually with the command "Data" => "Group and Outline" => "Group".


Example for the table:

Level	Crit A	                Crit B

1	PRD.00133	Crit 1
2	PRD.00133.01	Crit 2
2	PRD.00133.04	Crit 3
2	PRD.00133.05	Crit 4
2	PRD.00133.06	Crit 5
2	PRD.00133.07	Crit 6
3	PRD.00133.07-02	Crit 7
3	PRD.00133.07-03	Crit 8
4	PRD.00133.07-03-01	Crit 9
4	PRD.00133.07-03-02	Crit 10
4	PRD.00133.07-03-03	Crit 11
2	PRD.00133.08	Crit 12
2	PRD.00133.10	Crit 13
2	PRD.00133.11	Crit 14

I hope my definition of the problem is now better, there are always
problems
as a non native speaker, sorry once more.

Best Regards
Werner
0
Werner
4/1/2010 12:00:14 PM
Reply:

Similar Artilces:

columns show as numbers
I just noticed that all of my columns now show up as numbers instead of letters, which makes it difficult for entering formulas - how do I get the columns back to letters Tools, Options, General, (uncheck) R1C1 reference style --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "jbv" <anonymous@discussions.microsoft.com> wrote in message news:B43F64D5-7296-43AB-A137-98F623BC5236@microsoft.com... > I just noticed tha...

Title Column
Hi all. simple question.... how would i go about making column A title column so when i move along, say to column z i can still see th titles in column A. :confused -- jhome ----------------------------------------------------------------------- jhomer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2397 View this thread: http://www.excelforum.com/showthread.php?threadid=37596 Select column B Window>Freeze panes Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: htt...

Dependent Formatting (Text or Numbers
Hello All, I am attempting create formatting for an object based upon the value displayed. If the value is six numbers entered without spaces (Ex. 123456), then the formatting should be "##-##-##" (Ex. 12-34-56). Otherwise, the object must allow regular text. I have tried several different methods but with no luck. Below is my most recent attempt. I tried inserting it into the "lost focus", "on change", and "after update actions". Many thanks in advance for helping clear up a headache. If Me.AccountCode1 Like "######" Then Me.AccountCode...

Microsoft Knowledge Base Article
I experience this error "A document with the name '<file name.xls>' is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that's currently open, or rename one of the documents". The MS Knowledge Base does explain solution. Not even one condition apply to the cause stated in the article. OS windows 2000 Pro, Excel 97 SP2. Any idea what's wrong and how to resolved it. Maybe this'll help (From Debra Dalgleish's site): http://www.con...

Calculations on a parent form based on info in Child
Bear with me, I am very new to access... I have a contributor table and a donations table. I also have a form for each. On the contributors form I have displayed a child form generated from the donation table which shows the donations that contributor has made. I would like to have a few fields on the parent which totals info listed in the child. For example: Count of donations made, Total value of those donations, and Based on the amount the individual has donated I would like Access to assign a category. Friend, Supporter, patron... I was told to use the following formulas Count of ...

Outlining
We are trying to import an outline, usually two levels, with an occasional third. The original is either WordPerfect X3 or MSWord 2003. These two program produce outlines differently. WPX3 is superior by the way. But I digress. We want to bring that outline into Publisher 2007. We get the text but no numbers. We used Publisher 2000 and got the numbers until we updated to WPX3 and Word 2003. Once we made those changes no numbers. I have tried to import text. I have tried copy text. And I have tried creating an outline from scratch and cannot get two level outlines. Is such a...

Application base address question
Hi Experts: I'm looking at a list of running processes on Windows XP and noticed that multiple applications have the same base addresses. Does this make sense? How could multiple applications be loaded at the same base address. Thanks in advance! Polaris On Tue, 23 Jan 2007 21:10:20 -0800, "Polaris" <etpolaris@hotmail.com> wrote: >Hi Experts: > >I'm looking at a list of running processes on Windows XP and noticed that >multiple applications have the same base addresses. Does this make sense? >How could multiple applications be loaded at the sam...

Cell Value Updated based cell input
I'm trying to update a cell "A1" based on a cell value in "B1": If cell value in B1 < 5, A1 should be 100 if cell value in B1 is between 5-10, A1 should be 200 if cell value in B1 is between 10-20, A1 should be 300 and so on. How do I write a formula in A1 to make this calculation? If this is just a three condition item, I would use an IF statment =IF(condition,true,false) You can nest them up to 7 deep I think. =IF(b1 < 5,100,if(and(b1>=5,b1<=10),200,if(and(b1>10,b1<20),300,"Not In Range")) If you want a matrix, then I think a mat...

Trouble formatting page numbers
I have a document that uses a 2 page master, both pages have a page number centered in the footer. When I switch from the Master to the publication pages, the page number moves over to the outer margin of the page...I hae tried deleting the footer and re-insterting, copying from a file that the footer is centered correctly, etc... nothing seems to work for long. Any ideas? Thank you! Tanya You can move the page number text box anywhere on the Master Page, format it right, left or center. It behaves the same as any text box. -- Mary Sauer http://msauer.mvps.org/ "Tanya" ...

Outline
I have a sheet with NO calculations. I would like to manually outline (I am new at this feature) so repetitive data collapses. Let's say my data looks like this: Acme Co. North Acme Co. North Acme Co. South Badge Corp. Badge Corp. Chicago Loop Chicago Loop I want it to collapse to show only unique names, like below. +Acme Co. North Acme Co. South +Badge Corp. +Chicago Loop When I manually outline each group, they all combine to one big group. Any hints? How about using the subtotal feature from the data menu? Just select "count" of "co. name" or whatever. >...

Formatting cells for phone numbers
Hey, im looking to format a number of cells into a phone number format I have a rather large excel spreadsheet of around 900 phone numbers they are currently in different formats such as: 0870 8888888 +44 0800 7777777 +800 (900) 666666 0980 888888 These are obviously just example numbers and not real ones, as you ca see they are all over the place and in the wrong format that i nee them in. Changing them all manually would take me forever so ive trie to create a custom formatting rule to have them represented like this: +44 (800) 888888 So basically they must all begin with +44 then the n...

Can I sort text data by reference to text data in another column?
I have imported text data (e-mail addresses) into a worksheet that also has a column of text data (also e-mail addresses), many of which, but not all, are the same as the imported text data. I wish to sort the imported text data into the rows of the matching original text data. How do I do that? The basic process would be to get all email addresses into a single column. Then select ALL columns to be sorted and use Data --> Sort and choose the column with the email addresses as the column to base the sort on. Duplicate email addresses will end up on adjacent rows. &q...

Saving custom number format
1) I am trying to save a custom number format for e.g. ###,###,000.000 But when I open a new file this format is not available. Why doesn't it gets saved? (excel 2002) 2) My home computer has recently been upgraded to the latest version of excel. But I used to hit Alt + F and then C to close a file in earlier versions. This no longer works! I have to hit Enter key after pressing the "C". Is it a bug? or am I missing something? Shantanu Oak Custom formats are saved with the workbook. To have these available for all new workbooks, save a blank workbook with the custom formatti...

Checking cell for Number
I have about 200 numbers in column A. I would like to enter any number in Column B. In column C I would like, it to show if the number is used or free. can this be done with a if statement? Thanks Try... =IF(COUNTIF($A$1:$A$200,B1),"Used","Free") If you have a list of numbers in Column B that you'd like to check, enter the above formula in C1, and copy and paste the formula to your other cells in Column C. Hope this helps! In article <E5CA9196-6FCF-492C-B488-1B7E431720CF@microsoft.com>, Lee <Lee@discussions.microsoft.com> wrote: > I have abou...

outlook not responding after being left open for a little while
outlook will not respond after being left open for a little while, I have to shut down and restart to get e-mail messages. What have you tried? What version of Outlook? What version of Windows? -- Susan Ramlet -- please reply to the newsgroup so all may benefit. "tamsb1963" <tamsb1963@discussions.microsoft.com> wrote in message news:2BC2B81B-57C4-40DB-BE0B-FDD50988BB62@microsoft.com... > outlook will not respond after being left open for a little while, I have > to > shut down and restart to get e-mail messages. ...

Change positive number to negative number
Hi, I've searched this board and have tried a few things, no luck yet. I have about 100 rows, in columns A and B there is descriptive text. In column C it is either blank or it contains the text "CR". In column D there is a value (number with 2 decimal places). Column E is blank and available to use for the formula. If there is "CR" in column C, then the value in Column D must be displayed as a negative number. If "CR" doesn't exist then the number should be left positive. How can I do this? I've been working with the ABS and I...

Outlines of Pie Charts
I am working with pie charts in Excel and cannot seem to get the line/edge of the pie smooth. The line looks wavy - is there any way that I can get this to look smooth? ...

Recommendations on number of items in PST and OST
I need to know whether if there is a recommendation on number of ITEMS in a PST and OST? I am currently running MS Outlook 2003. Thanks I know that there is a limit to the number of items in a single folder in a pst file...it was around 16,000...actually had a user run into it once, and that folder was no longer accessible... -- Susan Conkey [MVP] "Hasher" <Hasher@discussions.microsoft.com> wrote in message news:2864AF80-FD7A-4578-8A80-AB2A32E75993@microsoft.com... > I need to know whether if there is a recommendation on number of ITEMS in a > PST and OST? > &g...

sorting numbers #5
Hi were have problems in sorting out our inventory item numbers. We have over 15,000 item numbers that can't be changed or zeros deleted. The problem is when there are zeros in the item number. Excell sort CA0002 CA00020 CA0021 CA02 CA021 CA02C Needs to be CA0002 CA02 CA02C CA00020 CA0021 CA021 Thanks Darkjedi -- darkjedi ------------------------------------------------------------------------ darkjedi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29153 View this thread: http://www.excelforum.com/showthread.php?threadid=488748 Since I didn't re...

Append mail names based on whether 1 or two recipients and if marr
I have a table that can have two names on the various fields. What I want to achieve is like this: R Upson R & A Upson R Upson & A Other I have set up the first part but didnt know where to go on the 2nd part: Shortname: IIf([No_Of_Acct_Holders]=1,IIf([Customer1_FirstName]="",[Customer1_Surname],Left([Customer1_FirstName],1) & " " & [Customer1_Surname]),"XYZ") The table source is from a table so I could create a field that runs a macro to paste the data into the field if this is easier? There's not a simple, practical sol...

MFC Appwizard not allowing CHtmlView base class
I have Visual C++ 6.0 with Service Pack 5 installed. I have been trying to create a browser-like application as described in the MSDN Library by using the MFC Appwizard (exe version). The instructions indicate that in the last step of the Appwizard, select CHtmlView as the View base class from the dropdown list, but when I use reach this point, CHtmlView is never in the list of available base classes, no matter what settings I try. I am creating a SDI with Document/View Architecture support, as per the instructions in the MSDN Library. I have also performed a full reinstall of Visual C++an...

General numbers lookup from FY-PP-WK-D resulting in MM-DD-YY
I have 8 numbers in general format in the J column of the main! sheet: 20100416 20100527 20100427 20100513 20100414 These numbers represent dates as follows: YYYY ( Fiscal year)-Pay Period ( 01 thru 26)-(week of pay period, either 1 or 2), and day of week; 1=sat, 2=sun,3=Mon,4=Tue,5=Wed,6=Thu & 7 = Fri. The first number 2010 04 1 6 would indicate Fiscal year 2010, PP 04, week 1, day 6. I also have another sheet (table!) with first 7 of the 8 numbers that indicates the first day of the week based on the 042, 051, 052, etc, such a...

Email messages and pics are outlined and in box form
How do I change my Email Messages that I receive that appear outlined in box form? They are inside a box and it is a mess. Thanks debull@gmail.com You haven't provided any details. Any answers you get at this point will simply be guesses. Please advise the following: Version of Outlook Type of mail account Default format O/S Any error messages? What do they say? Has it always been like this, or is this new? Does this behaviour continue if you open Outlook in safe mode? If you use Vista or Windows 7 follow this instruction: Start > Start Search > outlook.exe /safe...

Outline format disappeared
I am no longer getting the hierarchical tree structure of messages on this forum. They are all listed one after another in date order. Can anyone suggest how to get it back? "Nicholas Geti" <ngeti@optonline.net> wrote in message news:4ba0e9dc$0$22518$607ed4bc@cv.net... >I am no longer getting the hierarchical tree structure of messages on this >forum. They are all listed one after another in date order. Can anyone >suggest how to get it back? > It would have made things a lot easier if you had told us how you're accessing this newsgroup...

Disabling Top Level Pub folder creation
Hi, I am trying to restrict Top level Public folder creation to administator. I click on the check box to deny this to the "Everyone" group, but it also stops "Domain Admins" from creating top level folders. What am I doing wrong? Thanks, Tom ...