+ Reply to Thread
Results 1 to 10 of 10

Format data and split unique criteria to new file

  1. 07-02-2018, 11:21 AM #1
    pauldaddyadams
    pauldaddyadams is offline
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Format data and split unique criteria to new file

    Hi,

    I was previously helped out with a code here which would split the data into new files based on a unique PC number:
    HTML Code: 
    But frustratingly the original requirements changed and I am hoping someone can help amend the existing code provided to achieve what I now need.

    Requirements

    On the attached I have three tabs.
    1. This is the raw Data
    2. This is the layout requirement for the raw data (only it would be split by PC number)
    3. This is the old format of the data for reference only

    The following is what I need:
    1. I no longer have header rows 1:5 so this requirement has now gone. (Only the first row needs to be retained)
    2. Column B is now the column to be split (to create each new file)
    3. Columns A:E need to be deleted (so I am left with just three columns as shown on the tab TB load Format)

    Can anyone assist please?

    I have uploaded an example with the original macro included.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-02-2018, 11:59 AM #2
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this ‼


    According to your attachment as a beginner starter :

    PHP Code: 
    Sub Demo1()
            Sheet2.UsedRange.Offset(1).Clear
        Sheet1    .UsedRange.Offset(1).Columns("E:G").Copy Sheet2.[A2]
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
    Last edited by Marc L; 07-02-2018 at 12:13 PM. Reason: typo …
    Reply With QuoteRegister To Reply
  3. 07-02-2018, 12:13 PM #3
    pauldaddyadams
    pauldaddyadams is offline
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Format data and split unique criteria to new file

    Hi,

    I should have stated the existing code:
    Please Login or Register  to view this content.
    Does anyone know if I change:
    Please Login or Register  to view this content.
    to 1 (as i have only one header row)

    And change:
    Please Login or Register  to view this content.
    To B (as my unique references are in column B)

    If that would work or do I need to change anything else?

    Also I would like to auto delete columns A:D if possible?
    Reply With QuoteRegister To Reply
  4. 07-02-2018, 12:19 PM #4
    pauldaddyadams
    pauldaddyadams is offline
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Format data and split unique criteria to new file

    Hi,

    Answering my own post - it did work!! I had to change the sheet to "Data" but it worked.

    Can anyone help me in deleting the columns A:D please?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-02-2018, 12:22 PM #5
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Format data and split unique criteria to new file


    From your attachment, no need to delete anything, just to copy only the desired !

    As the better initial explanation & attachment, the better & quicker solution …
    Reply With QuoteRegister To Reply
  6. 07-02-2018, 12:45 PM #6
    pauldaddyadams
    pauldaddyadams is offline
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Format data and split unique criteria to new file

    I agree that it can be copied but the requirements are to be able to split into multiple files also
    Reply With QuoteRegister To Reply
  7. 07-02-2018, 12:52 PM #7
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Format data and split unique criteria to new file


    Which does not reflect your attachment as the code is at the attachment level : a bad attachment, a bad code !
    Accuracy is needed since the initial post as we do not have to guess anything.
    Beginner way to extract under condition : an easy advanced filter …
    Reply With QuoteRegister To Reply
  8. 07-02-2018, 12:55 PM #8
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Format data and split unique criteria to new file

    Try next Code
    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  9. 07-02-2018, 01:07 PM #9
    pauldaddyadams
    pauldaddyadams is offline
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Format data and split unique criteria to new file

    Hi PCI

    I believe that has done the trick!!!! thank you so much!! I am going to test fully tomorrow but I believe you have achieved what I needed

    Regards

    Paul
    Reply With QuoteRegister To Reply
  10. 07-02-2018, 01:41 PM #10
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Format data and split unique criteria to new file

    Please do tests and give your feedback, I have the feeling it could take time and some improvement could be welcome ...!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] vba split data in respective columns based on unique list sheet
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-29-2017, 02:40 PM
  2. Split a File to Multiple files with Selected columns into CSV format
    By suchetherrah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2017, 11:46 AM
  3. Macro to split one file into many based on criteria !!!
    By gamefreak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2015, 05:15 PM
  4. Split cell data into unique rows
    By mattywoo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-26-2014, 09:31 PM
  5. [SOLVED] VBA Split Data and Move to targeted named file from master file
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2013, 01:06 PM
  6. [SOLVED] Split and send data per unique value in a column
    By Tino XXL in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2013, 04:32 AM
  7. Split String into many columns according to Format criteria
    By wali in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-23-2010, 01:50 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1