Grouping certain value by colomn

[manggo]

Dear Forum

Need help please.

I have entries raw data that not in order like this.
Entry No 1, Car, Duck, Cabbage
Entry No 2, Bird, Banana
Entry No 3, Plane, Bird
Entry No 4, Bike, Pea
Entry No 5, Pear
Entry No 6, Car, Duck, Banana
Entry No 7, Duck, Bike

I want all entries will grouping according to categories (by colomn) TRANS, FAUNA, FLORA as like this :

NUMBER TRAN FAUNA FLORA
================================
Entry No 1 Car Duck Cabbage
Entry No 2 None Bird Banana
Entry No 3 Plane Bird None
Entry No 4 Bike Pear None
Entry No 5 None None Pear
Entry No 6 Car Duck Banana
Entry No 7 Car Bike None

How I put the formula in excel please ?

Any help would very appreciate ..

The excel I put here :
https://drive.google.com/open?id=1rQ5lHNx-FYfdKCbulo2frzY7g5-8mAA2

Regards,
Manggo Manggo

 

[Peter_SSs]

Is this what you mean?
It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
2								TRANS	FAUNA	FLORA		GROUP	ITEM
3	Entry No 1	Car	Duck	Cabbage			Entry No 1	Car	Duck	Cabbage		TRANS	Car
4	Entry No 2	Bird	Banana				Entry No 2	None	Bird	Banana		TRANS	Plane
5	Entry No 3	Plane	Bird				Entry No 3	Plane	Bird	None		TRANS	Bike
6	Entry No 4	Bike	Pear				Entry No 4	Bike	None	Pear		FAUNA	Bird
7	Entry No 5	Pear					Entry No 5	None	None	Pear		FAUNA	Duck
8	Entry No 6	Car	Duck	Banana			Entry No 6	Car	Duck	Banana		FAUNA	Dog
9	Entry No 7	Duck	Bike				Entry No 7	Bike	Duck	None		FLORA	Cabbage
10												FLORA	Banana
11												FLORA	Potato
12												FLORA	Pear
Sheet2

 

[manggo]

Dear Peter

Thank you very much.
I will try your formula and let you know ..

very appreciate.

Manggo

 

[manggo]

Hi Peter

I just copied your formula to H3. but its None on H3. Its supposed to "Car".
I copied accross and down: still None

Please advice. Perhaps some error on my paste, its the link:
https://drive.google.com/open?id=1A3-UH3rfy3tmcD_4xZBzubuCCDel2ozM

Thanks Peter

Manggo

 

[Peter_SSs]

Check that the values in B3:D9 and in L3:M12 and in H2:J2 do not have any leading or trailing spaces.
If you still cannot resolve, can you provide the actual file like you did in post 1 instead of just an image? Make sure the formula that you added is in the file.

 

[manggo]

Dear Peter

Its the file i tried to : https://drive.google.com/open?id=12LnzSDDRmOf2QNe4ZvBgTPcadfXgR9QM
I have been paste your formula

Many thanks Peter.

Manggo

 

[Peter_SSs]

I have been paste your formula

Looks like you forgot about this part

It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

 

[manggo]

Dear Peter

I do sorry for my ignorant, brothern

I tried following step by step as your guide: all formula within cell copied across and down. Confirmed with {) all in its cell.
But still only "none" value appears. Would you please check this file that has been copied with your formula.

Here the file : https://drive.google.com/open?id=1aZiTPPPa838oKu8qHrY6tm4sQsSV4Hq1

Many thanks Peter.

 

[manggo]

Aha .. Peter ..

Dont know

With this file with your formula If I edit with online GoogleDoc, its worked well ..
but seems your formula has converted by googledoc ..

see screenshot https://drive.google.com/open?id=1j0BJ3lisl9AN3Df9BsNGI15If3-JaDe3

I will try with different excel version then, as now I am using 2010 Profesional Plus...
I will let you know, brother

many thanks

manggo

 

[Peter_SSs]

1. What version of excel are you using?

2. What operating system are you using - Windows or Mac?


Edit: Looks like you have just answered Q1. TEXTJOIN does not work in Excel 2010. I will consider options.