#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
If someone can help me with the possible macros (either excel or word), that would be much appreciated.
Thank you |
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
If one preference is to be given over other, it would be Excel
|
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
It works like MAGIC ! Thanks a lot
|
|
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 |
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 |
Get multiple cells in separate worksheet | kerkstrt | Excel | 1 | 10-26-2014 11:20 PM |
entering data in separate rows within a cell | drsuis | Excel | 1 | 02-19-2013 04:36 PM |