x
Solved

# Draw several entries from another worksheet and enter them in one cell separated by manual line breaks

Posted on 2016-10-07
Medium Priority
62 Views
Dear Experts:

I wonder whether the following is feasible with a formula:

I would like to draw the WashingCode Descriptions for different items and enter them in the Column that has got the Description_Formula Header.

The code descriptions are to be separated by a manual line break.

Is this feasible with a formula or do I need VBA programming?

I have attached a similar sample table for your convenience.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

ItemCode     WashingCode_1    WashingCode_2      WashingCode_3          Description_Formula
Z1                         101                             102                        103                                 Sterile
Manual Washing
No Rubbing

Z2                         102                             104                       105                                   Manual Washing
Dry Cleaning
Mild Detergents

On the Second Worksheet (named WashingCodes) the WashingCodes are listed in a table along with their description

WashingCodes                   Description
101                                           Sterile
102                                           Manual Washing
103                                           No Rubbing
104                                           Dry Cleaning
105                                           Mild Detergents

Draw_mulitple_entries_merge_in_one_.xlsx
0
Question by:Andreas Hermle
• 4
• 2
• 2

LVL 35

Expert Comment

ID: 41833206
In F use the following formula:

=VLOOKUP(B2,Tabelle1[#All],2,FALSE)&CHAR(10)&VLOOKUP(C2,Tabelle1[#All],2,FALSE)&CHAR(10)&VLOOKUP(D2,Tabelle1[#All],2,FALSE)&CHAR(10)&VLOOKUP(E2,Tabelle1[#All],2,FALSE)

Format the cell with Wrap Text enabled.

CHAR(10) is the Ascii code for a Carriage Return.
0

LVL 9

Assisted Solution

Koen earned 1000 total points
ID: 41833207
0

LVL 35

Accepted Solution

Rob Henson earned 1000 total points
ID: 41833210
Amended to allow for missing or erroneous codes:

=IFERROR(VLOOKUP(B2,Tabelle1[#All],2,FALSE)&CHAR(10),"")&IFERROR(VLOOKUP(C2,Tabelle1[#All],2,FALSE)&CHAR(10),"")&IFERROR(VLOOKUP(D2,Tabelle1[#All],2,FALSE)&CHAR(10),"")&IFERROR(VLOOKUP(E2,Tabelle1[#All],2,FALSE),"")
0

Author Comment

ID: 41833349
Hi Koen,

wow this is awesome, exactly as I wanted it

Hi Rob,
will test it this afternoon and then let you know.

Thank you to both of you ...
0

LVL 35

Expert Comment

ID: 41833351
Koen's file has the exact same formula as I posted in my first comment.  The second version just allows for error trapping.
0

Author Closing Comment

ID: 41833527
I suggest splitting the points. Koen, thank you very much for already entering the formula in my sample file, very convenient.

I realy appreciate your great job, Koen and Rob

Have a nice weekend, Andreas
0

LVL 9

Expert Comment

ID: 41833548
Yeah Rob was first but i was making the excel..comments are only minutes apart...
0

LVL 35

Expert Comment

ID: 41833552
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Though there are a few manual ways to import PST files to Office 365 , third-party PST to Office 365 import tools are preferred over them due to various reasons.  Consequently, many tools or services are available for the same. Here, we pick the to…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month4 days, 5 hours left to enroll