Grouping certain value by colomn

manggo

New Member
Joined
Jul 29, 2017
Messages
18
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,777
Office Version
365
Platform
Windows
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
ABCDEFGHIJKLM
2TRANSFAUNAFLORAGROUPITEM
3Entry No 1CarDuckCabbageEntry No 1
Car
DuckCabbageTRANSCar
4Entry No 2BirdBananaEntry No 2NoneBirdBananaTRANSPlane
5Entry No 3PlaneBirdEntry No 3PlaneBirdNoneTRANSBike
6Entry No 4BikePearEntry No 4BikeNonePearFAUNABird
7Entry No 5PearEntry No 5NoneNonePearFAUNADuck
8Entry No 6CarDuckBananaEntry No 6CarDuckBananaFAUNADog
9Entry No 7DuckBikeEntry No 7BikeDuckNoneFLORACabbage
10FLORABanana
11FLORAPotato
12FLORAPear
Sheet2
 

manggo

New Member
Joined
Jul 29, 2017
Messages
18
Dear Peter

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

very appreciate.

Manggo
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,777
Office Version
365
Platform
Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,777
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 29, 2017
Messages
18
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,777
Office Version
365
Platform
Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,100,138
Messages
5,472,742
Members
406,834
Latest member
RahafKh

This Week's Hot Topics

Top