Need help with 'Concatinate' function

[jvcalandra]

I have a spreadsheet I use to track orders. Column P in my sheet uses a "vlookup" formula to show data in cells ranging from P40:P73. I want to reference all of the data in that range into another cell (call it cell A1) preferably with spacing or commas to separate the row data. I came close using a combo of "transpose" and "concatenate", but some of the cells in column P have no data to pull in from the 'vlookup' formula, so those cells return a 'N/A' result. If any cell in range P40:P73 returns an 'N/A' result, then the 'concatenate' function won't work and I get an 'N/A' result in cell A1. Is there a way to concatenate and skip or ignore 'N/A' result data?

[saravnepali]

can you have a workbook to show what you want ?

Show the data you have in hand and May be mock up the desired result

[FlameRetired]

Hello jvcalandra. Welcome to the forum.

Please upload a small Excel workbook that illustrates what you are working with and what you hope to accomplish. The instructions for doing so are in the 'gold' banner at the top of this page.

[jvcalandra]

I've summarized some general data into a sample sheet and attached it here. Thank you for helping.

[AliGW]

Explain the desired result - where is it coming from, where should it go and why?

[Glenn Kennedy]

A bit confused. If you select 1 2 and 4 in column L, you want the 3 maatching values t appear concatenated in one cell? Correct?

If not, please exlain more slowly!!

Option 1: helper column & ordinary formula.

Option 2 a UDF (needs an .xlsm marcro enabled file.

You choose...

[Desotronics]

Wrap your Vlookup with an if.error

So =iferror(vlookup(a1;B1:C10;2;false)"")

If not found "" nothing will be shown.
Instead of nothing you could also say "Not found"

and you can also wrap your concatenate function with an if.error

good day sir !

[AliGW]

I think you've misunderstood the query. This is about concatenating results, not replacing errors.

[Glenn Kennedy]

Here's the vba option...

Happy to explain it.

[Pete_UK]

As you have posted in the Formulas sub-forum, I supposed that you wanted a formula-based solution. Use column G as a helper column, with this formula in G2:

=IF(AND(ISNUMBER(MATCH(A2,$L$3:$L$6,0)),COUNTIF(A$2:A2,A2)=1),MAX(G$1:G1)+1,"-")

Copy this down to the bottom of your data (or beyond, if you expect to add more data). Then you can use this formula in the cell where you want to see the result:

=SUBSTITUTE(IFERROR(PROPER(INDEX(B:B,MATCH(1,G:G,0)))&", ","") & IFERROR(PROPER(INDEX(B:B,MATCH(2,G:G,0)))&", ","") & IFERROR(PROPER(INDEX(B:B,MATCH(3,G:G,0)))&", ","") & IFERROR(PROPER(INDEX(B:B,MATCH(4,G:G,0)))&", ","") &"z",", z","")

This will give you up to 4 destinations, but it is easy to expand if you need more - notice that the MATCH terms have the numbers 1, 2, 3 and 4 (in red), so you can just keep adding more of the IFERROR terms and change that number. You will also need to change the range $L$3:$L$6 in the first formula to suit.

Hope this helps.

Pete

[jvcalandra]

Hi Glenn, you are correct.

[Glenn Kennedy]

So... Are you OK with either of the solutions offered?