MS Excel combine multiple cells of text into 1 cell

Jon Carlson
Jon Carlson used Ask the Experts™
on
For the attached spreadsheet,  I need to do 2 things to be able to import each row into another spreadsheet

1. On the "articles" tab, for each part number in column A I need all the text in column B to be in 1 box but maintain the rows of text (like in a memo field) it can't all run together it must stay in rows within the cell .  It has to be in one cell for the import.

2. The single cell of text from above needs to be copied into the "allparts" spreadsheet into a new column with the corresponding matching part number.  This could be inserted into a new column C right next to column B "EN"

Thank you
Price-List-Markup-Schedule.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
1. I run a macro to combine the text

Sub test()
    Dim ws As Worksheet
    Set ws = Worksheets("Article")
    
    lastRow = ws.Cells(Worksheets("Article").Rows.Count, "B").End(xlUp).Row
    cpos = 1
    itemID = ""
    tmp = ""
    
    'Application.ScreenUpdating = False
    For i = 3 To lastRow
        'New item
        If ws.Cells(i, 1) = "" And ws.Cells(i, 2) = "" Then
            ws.Cells(cpos, 3).FormulaR1C1 = tmp
        ElseIf ws.Cells(i, 1) <> itemID And ws.Cells(i, 1) <> "" Then
            cpos = i
            itemID = ws.Cells(i, 1)
            tmp = ws.Cells(i, 2)
        Else
            tmp = tmp & Chr(10) & ws.Cells(i, 2)
        End If
        Application.StatusBar = "Running" & String(i Mod 5, ".")
    
        DoEvents
    Next
    'Last item
    ws.Cells(cpos, 3).FormulaR1C1 = tmp
    
    Application.StatusBar = ""
    
    'Application.ScreenUpdating = True
    Columns("C:C").ColumnWidth = 28
    Columns("C:C").EntireRow.AutoFit
End Sub

Open in new window


2. Later, I just do a vlookup to the text that created in part 1, like:

=VLOOKUP(A2,Article!A:C,3,FALSE)

for more info, pls look attached.
Price-List-Markup-Schedule-b.xlsm

Author

Commented:
Thank you Ryan, all seems to be working well except for the VLOOKUP onto the "allparts" sheet puts the text that was combined into the single cell (Column C on Article) into a single line and doesn't keep the line by line formatting. Is there a way to keep this format or re-format once I pull the text into the "allparts" sheet?  Do I need to copy the text from the VLOOKUP somehow into another field, like a past-value type of thing? I've tried a few things and can't figure it out.

See attached file with VLOOKUP on column C on sheet:allparts
Pre-M2M.xlsm

Author

Commented:
quick response,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial