+ Reply to Thread
Results 1 to 27 of 27

split data from sheet into multiple sheets based on first two letters

  1. #1
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    split data from sheet into multiple sheets based on first two letters

    hi
    I would split data from sheet TIRES into multiple sheets based on first for two letters (FS,BS.....) into column B and when split data should arrange data the numbers from small to big.
    so as you see in sheet BS when arrange. it should search the less number after R and arrange to big and should search the less number in the beginning item before letter R and arrange from small to big,also should replace data in divided sheet when change or add new data in sheet TIRES.
    I put simple the result what I want after split in sheets BS,FS. but my true data in sheet TIRES are about 1000 rows and will increase
    thanks
    Attached Files Attached Files
    Last edited by abdo meghari; 02-08-2022 at 11:30 AM.

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,262

    Re: split data from sheet into multiple sheets based on first two letters

    This appears to be the extension of the thread below. You want separate macro for this or the macro given in the referred thread can be modified . so that directly you get
    BS and Rs sheets without Tires sheet.
    copy items into column across files and copy to bottom with ignore duplicate itemshttps://www.excelforum.com/excel-pro...ate-items.html
    Last edited by kvsrinivasamurthy; 02-08-2022 at 05:04 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    so that directly you get
    BS and Rs sheets without Tires sheet.
    no the sheet TIRES is important .
    I don't see similar like my question . in my case should be split based on two first letters not only FS,BS there are manys DT, FD .... then should be multiple divided sheets and when arrange data should be the last number because it's tires desco size and see the first item should be arrange as I put it . I no know if this way logical about arrange or not . I can't modified the code as you suggest and I don't see close as in what I ask for it .

  4. #4
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: split data from sheet into multiple sheets based on first two letters

    Try this code. It assumes the 2-ltr word sheet does not already exist.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: split data from sheet into multiple sheets based on first two letters

    This update will work even if the sheets already exists.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    @maniacb thanks for your code but I have two things .
    first of all I would when run ,macro continuously just update the data when the sheets have already existed without shows error as occures based on your code because I add new data or change in sheet TIRES , then should add the data automatically to sheets are divided.
    second about arranging if you compare the arranging of data based on OP with your code is difference.
    I no know if my arranging of data are possible or not . just I would when arrange the data should search the less number after letter R and search the less numbers before letter R. should arrange from small to big . I no know this arranging way can achieve by Excel or not .

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,262

    Re: split data from sheet into multiple sheets based on first two letters

    In Both FS and BS sheets you are having data starting with BS only. What about FS.

  8. #8
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    OMG !
    my apologies I attached wrong file .
    @kvsrinivasamurthy thanks for inform me
    my apologies guys
    I edited file .

  9. #9
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    @maniacb thanks again . can you see the arranging ,please?

  10. #10
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: split data from sheet into multiple sheets based on first two letters

    See post #5 for your first question. Working on the sorting.

  11. #11
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    See post #5 for your first question. Working on the sorting.
    I appreciated.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,049

    Re: split data from sheet into multiple sheets based on first two letters

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  13. #13
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters


  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,049

    Re: split data from sheet into multiple sheets based on first two letters

    Thank you.

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: split data from sheet into multiple sheets based on first two letters

    Please Login or Register  to view this content.
    Last edited by sintek; 02-08-2022 at 12:01 PM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  16. #16
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    @sintek thanks for your code , but the data are not arranging as I put it .
    may you see some detailes in post#6 as I said to maniacb ,please? if this is not clear just tell me what you want to know .

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: split data from sheet into multiple sheets based on first two letters

    Explain arranging in more detail...By referencing a few items...

  18. #18
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    ITEM BRAND QTY
    1 FS 195/65R15 EP150 THI 27410
    2 FS 195/65R15 EP150 JAP 28445
    3 FS 195/65R15 T001 JAP 29480
    4 FS 205/65R15 EP150 INDO 25340
    5 FS 205/65R15 MY02 THI 26375
    6 FS 205/65R15 TZ700 JAP 31550
    7 FS 195/55R16 EP300 THI 30515
    8 FS 205/55R16 RE003 THI 24305


    the most important the sorting should see to after letter R should sort from small to big as you see this alway contains numbers after letter R after the number there is space and some items about sorting should be normal . so in second part after two letters FS should sort based on it
    this item 195/65R15 when sort it should sort 195/65 from small to big and also sort R15 from small to big . it's difficult to explain this complicated case .I thought the result where I put it may be clear
    Last edited by abdo meghari; 02-08-2022 at 12:41 PM.

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: split data from sheet into multiple sheets based on first two letters

    This solves...
    Please Login or Register  to view this content.
    Untitled.png

  20. #20
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    @sintek I no know how thank you . in the beginning I tought this is not possible to sort for this way , but it seems my explenation is not clear.
    honestly I had a problem with how to explain this method despite of I issue some results , but it doesn't seem enough .
    many thanks for this great help

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: split data from sheet into multiple sheets based on first two letters

    .............................................
    Thanks.gif

  22. #22
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: split data from sheet into multiple sheets based on first two letters

    Here is another solution for you

    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    @maniacb great code !
    million thanks

  24. #24
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,045

    Re: split data from sheet into multiple sheets based on first two letters

    Please try

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

  25. #25
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    @Bo_Ry thanks for your trying , but it doesn't arrange correctly .
    see this picture based on your code and compare with right arranging as in post#19
    Attached Images Attached Images

  26. #26
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,045

    Re: split data from sheet into multiple sheets based on first two letters

    Try

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

  27. #27
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    449

    Re: split data from sheet into multiple sheets based on first two letters

    @Bo_Ry thanks
    did you see the result when attach your file ?
    the same thing as in post#24 . it doesn't arrange correctly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] split data for multiple digits & letters , symbols into sheet based on another
    By Alaa-A in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 01-03-2022, 05:18 AM
  2. [SOLVED] split data based on three columns to multiple sheets and rename sheets based on headers
    By KalilMe in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2021, 08:35 AM
  3. [SOLVED] split data across multiple sheets based on month
    By Alaa-A in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-10-2021, 06:54 AM
  4. [SOLVED] split data from sheet to three sheets for many names based on two columns
    By leap out in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-14-2021, 10:02 AM
  5. [SOLVED] Split one excel sheet into multiple sheets based on column value but only selected columns
    By nr6281 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-01-2019, 05:25 AM
  6. [SOLVED] Split excel data into multiple sheets based on Employee ID
    By Preeti1309 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-21-2014, 02:33 AM
  7. [SOLVED] Split Data from one sheet into multiple sheets
    By jstewart22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-11-2013, 06:54 PM

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