# Concatenate by condition

#### VN

##### Board Regular
How can I build the description as below sample ?

I would like to make a description by using concatenate cell by cell as sample. Condition of Concatenation -> New Description have to refer the numbers that is store at the top in each column.

I use this formula now :-

Code:
`` =CONCATENATE(INDEX(\$D5:\$N5,1,MATCH(1,\$D\$2:\$N\$2,0))," ; ",INDEX(\$D5:\$N5,1,MATCH(2,\$D\$2:\$N\$2,0))," ; ",INDEX(\$D5:\$N5,1,MATCH(3,\$D\$2:\$N\$2,0))," ; ",INDEX(\$D5:\$N5,1,MATCH(4,\$D\$2:\$N\$2,0)))``

How can I build the description in column B as below sample ?

Thank you.
0Test.xls
BCDEFGHIJKLMN
1
2Selectorderby==>1243
3
5A;W;W;HAIWFHWQDQXV
6HVTZ]^[[TRO
7ACZIL^CUQDN
8HVOOBRVEJWK
9U^KH^]RMDEE
Sheet2

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Andrew Fergus

##### MrExcel MVP
Hi

I'm not sure I follow 100%. If you copy the formula from cell B5 to cell B6, you will get the result :

H ; T ; ^ ; ]

Is that what you want?

Andrew

#### VN

##### Board Regular
Thanks Andrew93

But I alway change the number at top so I need to use the automatic formula to get the description of all.

I have table of data like this about 300 tables. Each table 's not same the number at top.

Example.

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=15><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - 0Test (version 1).xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=15><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=15><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION value=N selected>A2</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%">

#### Andrew Fergus

##### MrExcel MVP
Hi

What you are trying to do is return a number of matches where you have a varying number of items to match. The only way I can get this to work with a single formula in a single cell is using a pre-determined number of concatenated HLookup statements (instead of the index and match) and testing for non-matches with the isna function. It's not pretty but my formula works like this:

Code:
``=HLOOKUP(1,D2:N4,3,FALSE)& IF(ISNA(HLOOKUP(2,D2:N4,3,FALSE)),"",";"&HLOOKUP(2,D2:N4,3,FALSE))& IF(ISNA(HLOOKUP(3,D2:N4,3,FALSE)),"",";"&HLOOKUP(3,D2:N4,3,FALSE))& IF(ISNA(HLOOKUP(4,D2:N4,3,FALSE)),"",";"&HLOOKUP(4,D2:N4,3,FALSE))& IF(ISNA(HLOOKUP(5,D2:N4,3,FALSE)),"",";"&HLOOKUP(5,D2:N4,3,FALSE))& IF(ISNA(HLOOKUP(6,D2:N4,3,FALSE)),"",";"&HLOOKUP(6,D2:N4,3,FALSE))& IF(ISNA(HLOOKUP(7,D2:N4,3,FALSE)),"",";"&HLOOKUP(7,D2:N4,3,FALSE))& IF(ISNA(HLOOKUP(8,D2:N4,3,FALSE)),"",";"&HLOOKUP(8,D2:N4,3,FALSE))& IF(ISNA(HLOOKUP(9,D2:N4,3,FALSE)),"",";"&HLOOKUP(9,D2:N4,3,FALSE))``

I can't post an html image because the ampersand (&) symbols don't work in the html layout and the html viewer ruins the formula. This worked for me using your latest layout.

There may be a much more efficient way of doing this with VBA.

Andrew

#### VN

##### Board Regular
I will try to do as your advice.

Thank you so much Andrew93

Replies
3
Views
196
Replies
6
Views
223
Replies
4
Views
649
Replies
0
Views
114
Replies
0
Views
198

1,196,008
Messages
6,012,836
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back