Need help in solving concatenating text

[Excel Advanced Learner]

For example, in workbook one, I have a column with values displaying in cells such as "22, 33, 44, etc..." I have the second column in the same workbook displaying values "Apples, Oranges, Lemons, etc.."

My goal is to only display the values only if the search in the second column is "Apples", and then take those results of multiple "Apples" matches of Column 1 values and display them in the second workbook i.e. "22 and 44" in one cell.

How do I do that?

Example Workbook 1:

Column 1 COlumn 2
---------- ----------
22 Apples
33 Oranges
44 Apples
55 Lemons

Second Workbook output in one cell for "Apple" matches:

Apple matches are: 22 and 44

How do I do that? Thanks!!

 

[cyrilbrd]

Hi, not sure if this is what you need:
Given the following

Excel 2010
	A	B
1	Val	Name
2	22	Apples
3	33	Oranges
4	44	Apples
5	55	Lemons
6	66	Oranges
7	77	Lemons
8	88	Apples
Excel Advanced Learner_1

Excel 2010
	A	B
1	Name	Apples
2	Vals	22
3		44
4		88
Excel Advanced Learner_2

Formula in B2 is =IFERROR(INDEX('Excel Advanced Learner_1'!A$2:A$15,SMALL(IF('Excel Advanced Learner_1'!B$2:B$15=$B$1,ROW('Excel Advanced Learner_1'!B$2:B$15)-ROW('Excel Advanced Learner_1'!B$2)+1),ROWS($B$2:B2))),"") Ctrl + Shift + Enter not just Enter on a PC or Command + Return on a MAC.


Would that be what you wanted?

 

[Excel Advanced Learner]

Hi,

Yes, right direction! But I need the values displayed in just 1 cell in second workbook:

E.g. Apples values are 22, 44, and 66.