vlookup with multiple matches displayed in one cell

[jaapaap]

Dear,

I have data set like this

1 pear
3 apple
3 whatever
3 chicken
6 mc
8 fruit
8 meat

outcome Vlookup on 3 should be in one cell: (apple, whatever, chicken)

what kind of formula do I need to use?

[shukla.ankur281190]

Row\Col	A	B
1	Data	outCome
2	pear
3	apple	apple
4	whatever	whatever
5	chicken	chicken
6	mc
7	fruit
8	meat

B2

Please Login or Register  to view this content.

[jaapaap]

Thank you,

I have added a document to explain. I would like to have in cel B16 not only "Chicken" but "kip" and "," and "haan" as well.

regards

[shukla.ankur281190]

No excel sample file is attached.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[chief_abound]

https://www.extendoffice.com/documen...-criteria.html

[shukla.ankur281190]

Try

B16

Please Login or Register  to view this content.

Drag down!

[shukla.ankur281190]

ok if you want to separated by comma then

C2

Please Login or Register  to view this content.

Copy down

B16

Please Login or Register  to view this content.

[jaapaap]

sorry
did not saw you last comment

[jaapaap]

wow this is almost perfect.

If I change in A10 6 into 4, the value in B16 is only "fruit"

can you help me with that as well?

[shukla.ankur281190]

Then try

C2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

Copy across

B16

Please Login or Register  to view this content.

[jaapaap]

1 apple
1 pear
2 whatever
4 chicken
3 kip
4 haan
3 hello
2 bey
4 fruit
3 meat
1 big
1 cat


4 fruit; fruit


this works better but not yet good. I guess the order is important. Can you exclude that?

[shukla.ankur281190]

1 apple
1 pear
2 whatever
4 chicken
3 kip
4 haan
3 hello
2 bey
4 fruit
3 meat
1 big
1 cat


4 fruit; fruit


this works better but not yet good. I guess the order is important. Can you exclude that?

ahahah lolzz yeah shorting must be done before do so

[amar502322]

make a macro under which( using advance filter option with "criteria and list" then copy filtered items and then paste it with transpose in any location then use concatenate formula to combined transposed cell.

[jaapaap]

I prefer to solve it with a formula. I do not know much about marcro's.

[Glenn Kennedy]

You will need a UDF for this... But don't panic about macros, it's run just like a formula.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]


Then use this array formula:

=ConcatAll(IF(A2:$A$13=A16,$B$2:$B$13,""),", ")


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[jaapaap]

it works for me, thanks a lot

[Glenn Kennedy]

You're welcome... Thanks for the rep.