Function

[Jovana]

Hi. I need help with writting a formula. I have a table with 2 or 3 columns. In the first column i have the same number in some cells,but i don't know for sure which cells. In the second and third column i have data that is different in each cell but is connected with the same number from the first column.
What i need is a function or formula that can help me with putting all data from each cell from second and third column, in a row connected with one from the same numbers.

Example:
Colum A B C
1) 1 yellow
2) 4 ....
3) 1 pink
4) 7 .....
5) 9 .....
6) 1 green

Solution that i need:
Colum A B C D
1) 1 yellow pink green

Every help will be appreciated.

 

[Eric W]

Welcome to the MrExcel forum!

If you have Excel 2019 or newer, you can do something like this:

Book1 (version 2).xlsb
	A	B	C	D	E
1	Number	Color		Number	Set
2	1	yellow		1	yellow pink green
3	4	horse
4	1	pink
5	7	pony
6	9	zebra
7	1	green
8
Sheet7

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(" ",1,IF(A2:A7=D2,B2:B7,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Older versions are more problematic. There isn't a function that concatenates variable ranges. You'd have to use a macro. Hope this helps!

 

[Jovana]

Welcome to the MrExcel forum!

If you have Excel 2019 or newer, you can do something like this:

Book1 (version 2).xlsb
	A	B	C	D	E
1	Number	Color		Number	Set
2	1	yellow		1	yellow pink green
3	4	horse
4	1	pink
5	7	pony
6	9	zebra
7	1	green
8
Sheet7

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(" ",1,IF(A2:A7=D2,B2:B7,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Older versions are more problematic. There isn't a function that concatenates variable ranges. You'd have to use a macro. Hope this helps!

It worked. Thank you for your response.