+ Reply to Thread
Results 1 to 14 of 14

Convert a multilayered staggered data to flat file.

  1. #1
    Registered User
    Join Date
    12-03-2008
    Location
    USA
    Posts
    12

    Convert a multilayered staggered data to flat file.

    Attachment 49027I have an excel data dump of the organizational hierarchy out of ERP system that I need to convert to a flat file pivot table ready format. Could you guys help me with the vba code to accomplish this. The catch is that my lowest level always has to be a department meaning that a dept should be a last column however in the ERP not all the departments are mapped to the lowest level layer i.e. I might have Layer 1, Layer 2 dept or Layer 1 Layer 2 Layer 3 and then dept mapped to 3. Attached is the sample of the file.

    Your help is much appreciated.
    Attached Files Attached Files
    Last edited by matrosov; 07-22-2009 at 04:47 PM.

  2. #2
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Convert a multilayered staggered data to flat file.

    That file is very confusing, but maybe something like this:

    Please Login or Register  to view this content.
    The idea being that each variable will only change when a new value for that column is found, and you write all relevant variables to new sheet depending on which column contains the value.
    Last edited by jrussell; 07-23-2009 at 11:34 AM. Reason: spelling

  3. #3
    Registered User
    Join Date
    12-03-2008
    Location
    USA
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    There is a wrinkle to it though. Basically I have multi level layers/nodes and the lowest layer would be my departments. So to tie it back to the file I have a layer 02Layer1 under that layer I have departments mapped to that top layer directly but I also departmental groupings 02A 02B 02C 02D etc mapped to that layer as well. so I'd have 02Layer1 some departments and also 02A mapped to 02Layer1 02B will be under 02A 02C will be under 02B etc etc etc. Each of those may or may not have departments mapped to it. In other words departments are not mapped to the lowest level in hiearchy. But on the flat file they need to appear in the same column with blank rows in between as necessary. So on my file as needed type you will see that for departments that are mapped directly to 02Layer1 all the other layers descriptions are blank. Further problem that on export file the text description of one layer would be in the same column as an alpha numeric designatior of a lower layer.

    Thanks.

  4. #4
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Convert a multilayered staggered data to flat file.

    Quote Originally Posted by matrosov View Post
    There is a wrinkle to it though. Basically I have multi level layers/nodes and the lowest layer would be my departments. So to tie it back to the file I have a layer 02Layer1 under that layer I have departments mapped to that top layer directly but I also departmental groupings 02A 02B 02C 02D etc mapped to that layer as well. so I'd have 02Layer1 some departments and also 02A mapped to 02Layer1 02B will be under 02A 02C will be under 02B etc etc etc. Each of those may or may not have departments mapped to it. In other words departments are not mapped to the lowest level in hiearchy. But on the flat file they need to appear in the same column with blank rows in between as necessary. So on my file as needed type you will see that for departments that are mapped directly to 02Layer1 all the other layers descriptions are blank.
    This is accounted for in my prevoius post. Looking at the first sheet of your file, as each line is read, depending on which column contains text, it is saved as a different variable. So in cell B3 it reads 02Layer1 and saves it as textB. Then in rows 5-23 it reads the depts and descriptions in cols C and H, saves them as textC and textH and writes them to the new sheet along with 02Layer1. Then in cell D25 it reads 02A0336 and saves it as textD. Since this is in col D it knows this is a subgroup of the last value in col B, 02Layer1. Then in cell F27 it reads 02B0780 and saves it as textF. Since this is in col F, it knows this is a subgroup of the last value in col D. Then in rows 29-38 it reads the depts and descriptions in cols G and L, saves them as textG and textL and writes them to the new sheet along with the last values of cols F, D and B (textB, textD, textF)

    Quote Originally Posted by matrosov View Post
    Further problem that on export file the text description of one layer would be in the same column as an alpha numeric designatior of a lower layer.
    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    You just have to describe the pattern step by step in a way the computer can understand.

    Edit: Wow, not sure if the text sizes in this post look all screwy to anyone else, but they sure do to me.

  5. #5
    Registered User
    Join Date
    12-03-2008
    Location
    USA
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    jr. Could you give me a one more nudge in a form of a real code that relates to this spreadsheet . I'm extremely rusty on declaring variables and on the loops. Thanks a lot.

  6. #6
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Convert a multilayered staggered data to flat file.

    Take a look at the attached file. You would start on the exported data from erp sheet and run the macro. There must not already be a sheet named Results when you run the macro.

    Please Login or Register  to view this content.
    My results are more internally consistent - your results printed some lines where there was no ending department, but not others. This code prints all of them. I think it makes it easier to read this way.

    If you don't want to print lines where there is no department, just a layer, you would comment out the relevant lines in the code by putting an apostrophe in front of them like so:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-03-2008
    Location
    USA
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    jrussell Thanks a lot for your help. One more and hopefully last question. I'm trying to bring this data to be completely inline. Meaning that if there is a department and a layer bring the department on the same row as the first occurence of that layer and don't print layer blank blank blank and then a department. Works the same for layer to layer assignment. I've attached the example file
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Convert a multilayered staggered data to flat file.

    If I understand what you're after, you would look for the comments I put in the macro that say "write 1st layer", "write 2nd layer", "write 3rd layer", and "write 4th layer" and comment out the lines that write the data to the next row of the Results sheet and increment NextRowResult by 1. I have done this in the attached file.

    Please Login or Register  to view this content.
    I can't really spend a lot of time on this as I am at work.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-03-2008
    Location
    USA
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    Jrussell this is absolutely no rush. Your help is really appreciated. When You comment out those certain lines of code it brings inline the results that have end level departments but it totally ignores empty nodes. What I am after is also display those empty child layers in line so for example I might have a situation where I have in the original file

    Layer one offset one row down layer two no other layers no departments I want to display layer one and layer two on the same row and move on to the next one. Right now if I run the original macro it will print layer1 and all the blanks then second layer would be layer1 and layer 2 so what I don't want is that first layer 1 blank blank blank blank.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Convert a multilayered staggered data to flat file.


  11. #11
    Registered User
    Join Date
    08-23-2007
    Location
    Nanaimo, BC, Canada
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    Thank you StephenR,

    This is not a question it's a project.

    I was looking at this and came up with a few ideas how to do it (after way too much time figuring out the <lack> of logic in the file).

    Even posted a couple of responses...

    However, although I would have done the code on spec (if not for free) I don't think I'll consider that now.
    http://www.excelvba.joellerabu.com

  12. #12
    Registered User
    Join Date
    12-03-2008
    Location
    USA
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    Guys I absolutely appologize for this. In my rush to get started. I totally did not read the rules and caused an unnecessary churn. No excuse for this except to say that I've learned my lesson.

  13. #13
    Registered User
    Join Date
    08-23-2007
    Location
    Nanaimo, BC, Canada
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    Hi,

    Thanks!

    Have you noted this at OZ as well?

    Did you get a solution?

  14. #14
    Registered User
    Join Date
    12-03-2008
    Location
    USA
    Posts
    12

    Re: Convert a multilayered staggered data to flat file.

    rbrhodes yes I have. Thank you very much. I'm looking at it now trying to see what it does. The real file is 8 layers deep and 4500 rows so the vba errors out at certain point so I'm trying to figure out what is causing it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1