Creating an Excel Table of Components By Product From a List Using the TEXTJOIN Function By David Hager

The listing of recipes for process blending is difficult, since each recipe can contain a different number of components. Therefore, the usual listing of this recipe information is as shown in columns A and B. From the sample table, defined names are given where arange is the list of blended compounds and brange is the list of components. In this case:

arange=Sheet1!$A$2:$A$25

brange=Sheet1!$A$2:$A$25

tj_pic1

The following formula was used to make the unique list of compounds in column E.

=INDEX(arange,MATCH(0,INDEX(COUNTIF($E$2:E2,arange),0,0),0)) in E3.

Then the formula is copied down until #N/A appears in a cell. Cells containing #N/A are then deleted.

I found this formula at Oscar’s great Excel site. See:

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

Now that the unique list is in place, a comma delimited list of components can be created in column F.

Place the following formula in cell F3 and fill down.

=TEXTJOIN(“,”,,IF(arange=E3,brange,””))

This array formula compares the value in E3 to each value in arrange and if there is a match, the corresponding value in brange is returned to the array. The TEXTJOIN function converts the array to a string using comma as the delimiter.

You can download the file here.

 textjoin1

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

Advertisements

3 thoughts on “Creating an Excel Table of Components By Product From a List Using the TEXTJOIN Function By David Hager

  1. Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: # Excel Super Links #65 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s