Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-24-2018, 10:09 AM
vjvj123 vjvj123 is offline Organise cells with line break into separate rows Windows 10 Organise cells with line break into separate rows Office 2016
Novice
Organise cells with line break into separate rows
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

Hi all,



I need help with a typical issue concerning tables. I have a table with (say) 10 columns (C1-C10) and 15 Rows (R1-R15). Most rows have one entry in each cell across the 10 columns. However, in C7 and C8, in some of the rows (say R5, R9 etc) the cells have multiple entries separated by a line break (I can find the line break by using ctrl+J in the find dialog box).
As an example the cell R5C7 contains 3 entries separated by line breaks, and the corresponding adjacent cell R5C8 also contains same number of entries (i.e. 3) separated by line breaks. I attach a sample file as an example, where the text under consideration is marked red for easy identification for this forum. Similarly, R9C7 and R9C8 contain multiple (two in this case) entries each. Multiple entries may be two, three, four or more in other C7 and C8 cells (although entries in a particular C7 cell=entries in the corresponding(adjacent) C8 cell).

Now, I wish to organise the data so that these multiple entries become separate rows of the table. The other remaining cells data (i.e. cells except C7 and C8) of these rows will be replicated. As shown in the attached sample file, below the original data, there is "Desired Data", the format in which I need the data.

Any help would be much appreciated, thanks for reading through the post.

Sorry for posting Excel file here, but since I can copy the table from excel to word and vice versa, any solution to this problem w.r.t MS Word will be appreciated as well.
Attached Files
File Type: xlsx SampleData.xlsx (11.5 KB, 10 views)
Reply With Quote
  #2  
Old 09-24-2018, 05:17 PM
macropod's Avatar
macropod macropod is offline Organise cells with line break into separate rows Windows 7 64bit Organise cells with line break into separate rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

There is no paste option for doing what you want; not only do you want each of the cells with line-breaks to become separate rows, but you also seen to want the data from other columns on the same row replicated in those new rows.

A macro could be used to undertake the task but quite different macros would be required for Word and Excel.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-25-2018, 08:00 PM
vjvj123 vjvj123 is offline Organise cells with line break into separate rows Windows 10 Organise cells with line break into separate rows Office 2016
Novice
Organise cells with line break into separate rows
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

If someone can help me with the possible macros (either excel or word), that would be much appreciated.

Thank you
Reply With Quote
  #4  
Old 09-25-2018, 08:27 PM
macropod's Avatar
macropod macropod is offline Organise cells with line break into separate rows Windows 7 64bit Organise cells with line break into separate rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You should decide which application you want this for - no-one wants to find they've written a macro for the application that's less suited to the one you need the output in.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 09-25-2018, 08:53 PM
vjvj123 vjvj123 is offline Organise cells with line break into separate rows Windows 10 Organise cells with line break into separate rows Office 2016
Novice
Organise cells with line break into separate rows
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

If one preference is to be given over other, it would be Excel
Reply With Quote
  #6  
Old 09-25-2018, 10:30 PM
macropod's Avatar
macropod macropod is offline Organise cells with line break into separate rows Windows 7 64bit Organise cells with line break into separate rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

For Excel, try:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim lRow As Long, r As Long, lCol As Long, c As Long
Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long
With ThisWorkbook.Worksheets("Sheet1")
  With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
    lRow = .Row
    lCol = .Column
  End With
  For r = lRow To 1 Step -1
    For c = 1 To lCol
      j = UBound(Split(.Cells(r, c).Value, Chr(10)))
      For i = 1 To j
        With .Rows(r).EntireRow
          .Copy
          .Insert Shift:=xlDown
        End With
      Next
      If j > 0 Then Exit For
    Next
    If j > 0 Then
      For k = c To lCol
        l = UBound(Split(.Cells(r, k).Value, Chr(10)))
        If l > 0 Then
          n = j
          For m = r + j To r Step -1
            .Cells(m, k).Value = Split(.Cells(r, k).Value, Chr(10))(n)
            n = n - 1
          Next
        End If
      Next
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 09-26-2018, 09:19 AM
vjvj123 vjvj123 is offline Organise cells with line break into separate rows Windows 10 Organise cells with line break into separate rows Office 2016
Novice
Organise cells with line break into separate rows
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

It works like MAGIC ! Thanks a lot
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Interchange line break and paragraph break shortcut keys skan Word 2 01-20-2017 03:50 AM
Organise cells with line break into separate rows Converting rows to separate colums MNMark Excel 1 01-06-2017 11:41 AM
How do I separate an address into separate cells Austcart Excel 2 01-18-2015 03:48 PM
Organise cells with line break into separate rows Get multiple cells in separate worksheet kerkstrt Excel 1 10-26-2014 11:20 PM
Organise cells with line break into separate rows entering data in separate rows within a cell drsuis Excel 1 02-19-2013 04:36 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft