Excel Alt+Enter automatically with pasted data 4

[ITALIAORIANA]

Hello,

Is there a way to paste information from multiple cells into one cell having it mimic Alt+Enter?

Example: Report shows:

Column A Column B Column C
Customer Name Address Service
John Doe 123 Main Cable
John Doe 123 Main Phone
John Doe 123 Main Internet

Jane Smith 456 First Phone
Jane Smith 456 First Internet

I need to copy portions of the report but what I want is to have information in columns A and B displayed once which is an easy copy. But column c I want to be in one cell. I don't want to have to copy and paste each service individually using Alt+Enter. Is there a way to copy this from multiple cells and paste into once cell?

Thanks
Deana

 

[SkipVought]

hi,

Please explain exactly WHAT it is that you want the result to be, rather than HOW you think it ought to be accomplished.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ITALIAORIANA]

Hi Skip,

I would like the services cable phone and internet to be in once cell instead of 3 separate cells. I kept saying Alt+Enter because that's what I wanted it to look like and I was using that as a reference. I am not sure if it's even possible.

Thanks
Deana

 

[SkipVought]

Format column D Alignment as Wrap Text.

Formula in column D
[tt]
D2: [highlight]=A2&char(10)&B2&char(10)&C2
[/tt]
Copy D2 down thru all rows of data.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ITALIAORIANA]

Thanks skip, this works great by making it look like what I needed but I need to be able to copy from one spreadsheet and paste into another.

Also the amount of services may vary it could be 1 or 30 for example, so I would have to modify the formula for each member.

I really don't think I am being clear in my request and I apologize for that. I'm not exactly sure how to ask for what I need.

What I am instructed to do. Is take a report and only copy portions of it and paste it to a client spreadsheet for their review. They want everything combined. So if the member has 200 services with us, they are on 200 rows on our sheet. The client wants to see one row, with all the 200 services in one cell.

So I was looking to be able to copy the information in the column cells and paste it into one cell in the format they want to see.

 

[SkipVought]

Please be clear, concise and complete with your request and instructions.

Please do not try to explain HOW to do it, like COPY and PASTE. Just explain WHAT it is that you need to accomplish.

Here's a macro that does what you need.

1. Use NAMED RANGES, based on the headings in your table.

2. Insert a sheet and name it [highlight]OUT[/highlight]

This macro puts the data in sheet OUT

Sub GenerateOutput()
    Dim r As Range, sPrev As String, lRow As Long, sOUT As String, iCol As Integer
    
    For Each r In [Customer_Name]
        
        If sPrev <> r.Value Then
            If lRow > 0 Then
                Sheets("OUT").Cells(lRow, 1).Value = sOUT
            
            End If
            lRow = lRow + 1
            sOUT = ""
        End If
        
        For iCol = 0 To 2
            sOUT = sOUT & r.Offset(0, iCol).Value & vbLf
        Next
        
        sPrev = r.Value
    Next
    
    Sheets("IN").Cells(lRow, 1).Value = sOUT
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BobJacksonNCI]

Sounds like a candidate for a pivot table.

Using your sample data, a basic one returns:


Count of NAME SERVICE
NAME Cable Internet Phone blank) Grand Total
Jane Smith 1 1 2
John Doe 1 1 1 3
(blank)
Grand Total 1 2 2 5

Poster's Note: This is my first pivot table. Learned about them in a class, but first time I've created one.

HTH,
Bob

 

[SkipVought]

@Bob,

Glad you're getting acquainted with PTs. Its a great tool!

However, it appears that the use is not looking for aggragations. Rather they want concatenated data in ONE CELL per Customer.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You can:
1. copy range,
2. open new excel instance (start new excel application),
3. in the new instance: enter the formula bar and paste, adjust column's width and height,
4. check if it is really what you planned to get.


combo

 

[lionelhill]

Italoriana, reading what you are actually doing, I think you are creating individual spreadsheets for individual clients, each containing only those rows that apply to that particular client? You are doing this by copying and pasting a section of a master-worksheet, which therefore has to be formatted the way you originally requested.

If this is the case, Pivot tables may well be, in fact, a better option. A pivot table will give you a master table with one row per client. If a particular client asks to see their services, double-clicking on their row will automatically create a new worksheet containing every row of original data that relates to them.