Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: VBA Account Hierarchy- Text to Multiple Colum

  1. #1
    Join Date
    8th April 2005
    Posts
    39

    VBA Account Hierarchy- Text to Multiple Colum

    Hi

    I would like to move text in to multiple columns based on parent and child relationship, into a visual hierarchy showing the relationship between Parent and Child Account, similar to an organisation structure.

    Please see the attached xls as this shows what i am trying to do.
    Sheet Name "Example" is sample date.
    I want output which is mentioned in sheet "Result"

    I have more than 5000 lines.

    Please could someone give me some ideas about how to proceed.

    Udhay
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: VBA Account Hierarchy- Text to Multiple Colum

    Hi Udhay

    Is the "Example" worksheet how your raw data is set-up? I cannot see any real visual difference between "Example" data and "Result" data.

  3. #3
    Join Date
    8th April 2005
    Posts
    39

    Re: VBA Account Hierarchy- Text to Multiple Colum

    Hi,

    In Example Sheet, everything in Colum A
    In Result Sheet, Account code status is in Column A, Great Parent in Colum B, Parent in Colum C, Child in Colum D....

    Visualy both are same, but I want Parent account code in one colum, child values in another colum in the same visual format.

    Thanks
    Udhay

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: VBA Account Hierarchy- Text to Multiple Colum

    Have you tried Data>Text to columns..? How does one determine what is parent, child etc?

  5. #5
    Join Date
    8th April 2005
    Posts
    39

    Re: VBA Account Hierarchy- Text to Multiple Colum

    Hi,

    Yes, I tried Data>Text to columns.
    When I use Tex to colum Parent Account code description is not appeared properly, spliting into differnt column.

    Please see the attached file (sheet "Text to Colum") highlighted rows.

    Udhay
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: VBA Account Hierarchy- Text to Multiple Colum

    How does one determine what is parent, child etc?
    .....

  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,383

    Re: VBA Account Hierarchy- Text to Multiple Colum

    Hi Udhay,

    See the attachment. I put some formulas for each column. Copy down the formulas and after completing the task you can pastespecial the formulas into values.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  8. #8
    Join Date
    8th April 2005
    Posts
    39

    Re: VBA Account Hierarchy- Text to Multiple Colum

    Hi Krishnakumar,

    Thanks.. this is I want.
    I need one more things to do.
    In the first line account code status is "Active"
    If the account code status is "Inactive" or "Not Postable" then the lenth will remain change.

    Rgds,
    Udhaya Shankar

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,383

    Re: VBA Account Hierarchy- Text to Multiple Colum

    Hi Udhay,

    In C1,

    =IF(OR(B1={26,28,24}),TRIM(MID(A1,FIND(CHAR(32),A1)-1+B1,255)),"")

    change the numbers between {..} .

    HTH

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Create Parent Child Relationship From Hierarchy
    By gtg688h in forum EXCEL HELP
    Replies: 6
    Last Post: February 7th, 2008, 22:44
  2. Map Account Numbers From Text File
    By mattenborough in forum EXCEL HELP
    Replies: 3
    Last Post: September 24th, 2007, 22:42
  3. Change Vertical Rolling Hierarchy To Horizontal
    By eoaevan in forum EXCEL HELP
    Replies: 2
    Last Post: September 19th, 2007, 22:46
  4. Rearrange data In Hierarchy To Table`
    By Belinda Li in forum EXCEL HELP
    Replies: 14
    Last Post: June 19th, 2007, 06:09
  5. Replies: 7
    Last Post: January 18th, 2006, 08:35

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno