lookup multiple columns and return values as concatenated

**shaft1** · 12-03-2013, 08:55 PM

Hi

i need to look up two columns (columns G & H) and find matching values in columns C and A respectively. And return multiple results (from column B) concatenated as shown in the attached spreadsheet.

I am most likely to have thousands of rows of these sort of data and have the need to automate as you can understand.

results to be concatenated and separated by an asterik (*) or "alt+enter" built in.

is it possible to generate 3 sheets for correspoding frequency and these sheets to be customised formatted sheets. i.e. i will need to create a customised sheets for each of the frequency and the code will dump the results in the corresponding customised sheets?

Please advise and thanks in advance,
Shaft.

**dwint** · 12-04-2013, 06:34 AM

perhaps its solution is using UDF (User Defined Function) only
no formula in excel which can concatenated whith multiple criterias.

write down this code into module

Please Login or Register  to view this content.

see attached file, and please enable macro when you're using this UDF

**shaft1** · 12-04-2013, 04:21 PM

thanks dwint.
it works like a charm.
is it possible to have a macro built in such that after doing the concatif udf, it also copies the same cell and pastes special values only.
i ask this because i will have about 4 thousand rows of values and dont want live formulas which will just slow down the excel.
thanks.