+ Reply to Thread
Results 1 to 28 of 28

Convert data into table

  1. #1
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Convert data into table

    The file I will attach have two columns. The first column represents days and the second one has data that I would like to split into some columns to be more readable.
    I have attached the final result
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    Something like
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    Thanks a lot. I got error at this line
    Please Login or Register  to view this content.
    we can't do that to a merged cell
    If possible I wanted the same format (table) as attached

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Convert data into table

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    Thanks a lot Mumps
    That's near to what I wanted. The first row in result ends in 23 and there is still 1049 88329,40
    Another things how to add totals and format the table?

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Convert data into table

    The macro copies the data into the range E4:N34. You have formulas I columns O and P and in row 35 which give you the totals. Do you not want to use the formulas? What do you mean by "format the table"?

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    we can't do that to a merged cell
    Get rid of the merged cells.
    You're sample file didn't have any.

  8. #8
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    @Mumps
    When execute your code I found that The first row in result ends in 23 and there is still 1049 88329,40. Clear the columns from E to S and execute the code
    @Fluff13 the sample file is what I am working on and the error says that there are merged cells although there are not merged cells

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Convert data into table

    Try:
    Please Login or Register  to view this content.
    By the way, you have merged cells in row 2, columns E to P.

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    @Fluff13 the sample file is what I am working on and the error says that there are merged cells although there are not merged cells
    Did you delete your "Result table" first, including the merged cells?

  11. #11
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    Yes it worked now. How can I keep the original data in columns A & B the same untouched and get the results starting in column E for example?

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Convert data into table

    I think that the macro I suggested in Post # 9 does exactly that.
    Last edited by Mumps1; 06-15-2019 at 01:29 PM.

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    This will also do that & does some of the format.
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    This one is wonderful
    Just wanted to add total row at the end of the table (in the example row 35) and as for columns that has the title "B" to be formatted as decimal numbers e.g 23.00
    Thank you for this wonderful code

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    You're welcome & thanks for the feedback

  16. #16
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    Thank you very much

    Just wanted to add total row at the end of the table (in the example row 35) and as for columns that has the title "B" to be formatted as decimal numbers e.g 23.00
    Can you help me with this please?

  17. #17
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    If you record a macro of you changing the Formats & adding the total row, you can put that code at the end of the macro.

  18. #18
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    I am not so expert at that. I tried and tried but no use

  19. #19
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    What code did you get by using the recorder?

  20. #20
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    this what I got
    Please Login or Register  to view this content.
    I think this are too much and at the same time where I am going to put in the code?
    What about formatting columns titled "B" to use separator 23.00

  21. #21
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi, try this !

    Quote Originally Posted by KingTamo View Post
    I have attached the final result
    As a beginner starter according to the initial attachment :

    PHP Code: 
    Sub Demo1b()
        [
    E4:N34].ClearContents
        Range
    ("A4"Cells(Rows.Count2).End(xlUp)).Sort [A4], xlAscendingHeader:=xlNo
        Application
    .DisplayAlerts False
        
    [A4].CurrentRegion.Columns(2).TextToColumns [E4], xlDelimitedConsecutiveDelimiter:=TrueSpace:=True_
                                                    FieldInfo
    :=Array([{1,9}], [{2,9}], [{3,1}], [{4,1}], [{5,1}], _
                                                    
    [{6,1}], [{7,1}], [{8,1}], [{9,1}], [{10,1}], [{11,1}], [{12,1}], _
                                                    
    [{13,9}], [{14,9}]), DecimalSeparator:=","
        
    Application.DisplayAlerts True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 06-16-2019 at 12:32 PM. Reason: optimization …

  22. #22
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Hi, try this !

    I've slimmed the recorded code down & got this
    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    Thanks a lot for all of you
    I have used the last one of Fluff13. It is very wonderful

  24. #24
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    Glad to help & thanks for the feedback

  25. #25
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    I have this line to format the columns titled "B"
    Please Login or Register  to view this content.
    This works partially.. The zeros still the same 0 and I wanted the 0 to be 0.00

  26. #26
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    Just format the column to show 2 decimals.

  27. #27
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Convert data into table

    Thanks a lot. That works fine.

  28. #28
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Convert data into table

    You're welcome

+ 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. Replies: 4
    Last Post: 11-29-2015, 07:20 AM
  2. auto convert data from dynamic table to static data
    By wijnand in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-23-2015, 10:11 AM
  3. Convert table data to another sheet
    By cemregunay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2014, 03:33 PM
  4. How do I convert my data into a table like this?
    By funkylaundry in forum Excel General
    Replies: 2
    Last Post: 03-04-2013, 10:55 AM
  5. Convert pdf data to table
    By plumfin in forum Excel General
    Replies: 10
    Last Post: 01-19-2011, 02:37 PM
  6. Convert Pivot Table to Normal Data table
    By ashish128 in forum Excel General
    Replies: 2
    Last Post: 05-02-2006, 04:40 AM
  7. [SOLVED] Pivot Table - How Do I Convert Data?
    By Nick Krill in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2006, 03:00 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