1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sub column for each category

Discussion in 'Ask an Excel Question' started by Shailender, Jan 10, 2018.

  1. Shailender

    Shailender Member

    Messages:
    110
    Hello All, I Need a small help in the excel sheet on which I'm working on . I have some different categories under column A with different rows in it . I wanted to create a sub column for each category differentiating with a space in it .

    I've attached the excel sheet for the reference.

    Any help would be appreciated.

    Thank you

    Attached Files:

  2. Shailender

    Shailender Member

    Messages:
    110
    Could someone please get back to this query.
  3. S. Das

    S. Das Member

    Messages:
    88
    Do you want something like this

    Attached Files:

    Shailender likes this.
  4. Shailender

    Shailender Member

    Messages:
    110
    Das, thank you for your reply and yes exactly I am looking like this. Just a small change I have attached the excel sheet for your reference and highlighted the text in green color.

    Once again thank you for your help. Awaiting for your reply as soon as possible.

    Attached Files:

  5. S. Das

    S. Das Member

    Messages:
    88
    What to do with that green colored text columns??
    Shailender likes this.
  6. S. Das

    S. Das Member

    Messages:
    88
    See this...

    Attached Files:

    Shailender likes this.
  7. Shailender

    Shailender Member

    Messages:
    110
    Das, I want to separate it in that way. For your reference I've colored it. Thank you.
  8. S. Das

    S. Das Member

    Messages:
    88
    Do you want automatic separation, means you only enter text in column A and it will separate to B and C by itself??
  9. Shailender

    Shailender Member

    Messages:
    110
    Das, yes it should get separated automatically into B and C
  10. Shailender

    Shailender Member

    Messages:
    110
    Yes, exactly in the same way. i want to do it automatically, either through formula or macro.
  11. S. Das

    S. Das Member

    Messages:
    88
    Is it helpful

    Attached Files:

    Shailender likes this.
  12. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Shailender
    You changed case from the original so ..
    Test this ...

    Attached Files:

  13. S. Das

    S. Das Member

    Messages:
    88
    vletm, Can you pls explain how does it work?
    Syndp and Shailender like this.
  14. vletm

    vletm Excel Ninja

    Messages:
    3,669
    S. Das ... as You wrote in #8 :
    Do you want automatic separation,
    means you only enter text in column A and it will separate to B and C by itself??

    Shailender write something as those previous samples in column A - okay?
    After press <Enter> - needed cells values will copy to their column as written in column A without indentlevel - okay?
    Shailender likes this.
  15. S. Das

    S. Das Member

    Messages:
    88
    Yes, that is fine...
    But I want to know without any formula or VBA how it is working and secondly what is the use of .xlsb extension
    Syndp and Shailender like this.
  16. vletm

    vletm Excel Ninja

    Messages:
    3,669
    S. Das
    1) That my sample works with VBA and as I wrote in #14 - - did You read it?
    2) '.xlsb' is one possible extension with VBA.
    3) 'by formula' ... if there would be formulas then ... someone would need to update formulas ... not so automatic or how?
    Syndp and Shailender like this.
  17. Shailender

    Shailender Member

    Messages:
    110
    Vletm, thank you for your response. I have quick question, on what basis, it is getting separated automatically. Could you please let us know how we can do this in xlsx?
  18. Shailender

    Shailender Member

    Messages:
    110
    Das, thank you for the quick response. Could you please explain about those formulas in detail that you have used. It would be helpful for me.
  19. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Shailender
    As written #16 -
    with VBA,
    it's automatic (if You continue using A-column as in Your sample) - test it
    and
    with xlsx ... ooo ... so far not possible! (#10 Reply .. or macro),
    but You don't need to fill Your sheet with formulas!
    Shailender likes this.
  20. S. Das

    S. Das Member

    Messages:
    88
    Shailender, I have changed two things in your workbook for two methods

    1) You have used indent to get the desired space in the starting of each line in column A, but I used <SPACE> for indentation. Press <SPACE> 5 times to send the text in column B and 10 times to send the text in column C.

    2) The Flag Concept: You have to put 1 in column D to send a text to column B and 0 to send text in Column C. After putting 1 or 0 you can hide the column D
    Syndp likes this.
  21. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Shailender
    ... if You need to ReFresh all rows in one time ... activate Cell [A1]

    Attached Files:

    Shailender and Thomas Kuriakose like this.
  22. Shailender

    Shailender Member

    Messages:
    110
    Vletm, thank you for the quick response. It is working good. I have 40 sheets or i can workbooks like this. Every time i can't paste those data into this particular excel sheet. Could you please let me know how did you do this automation process? so that i can apply these in those 40 workbooks. Thank you.
  23. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Do You have
    40 sheets or
    40 workbooks or
    40 workbooks which every has 40 sheets?
    > I can make something that it would be possible to use ...
    Shailender and S. Das like this.
  24. Shailender

    Shailender Member

    Messages:
    110
    Vletm, to make it clear I have 10 workbooks in that I have 10 sheets which has these kind of data in each workbook. Hope this is clear. thank you.
  25. vletm

    vletm Excel Ninja

    Messages:
    3,669
    Shailender - Okay
    Note: this won't do checks, it works with columns A..C! have BackUps!
    Step-by-step instructions:
    1) open the newest version of 'Question.xlsb'
    2) open one of Your '10 workbooks'
    3) From Uppper Menu -> Tools -> Macro -> Macros... ->
    4) Write to Macro name: Question.xlsb!Sheet1.Do_It
    5) After CORRECT above text then there will be [Run]-btn enabled
    6) Press [Run]
    7) Wait until message
    8) Repeat steps from 2 to 7 as many times as needed
    ... those have to do as that order as written above!

    Attached Files:

    Shailender and S. Das like this.

Share This Page