Excel, Multiple Values in the Same Cell? Solved

Question

Hello everyone,
I usually come here to help people but today I need your help, my question is as follows:

Is it possible to enter multiple values in the same cell in Excel?
I would like each of these values to be independently recognizable when I apply the filter to the column.

Just to clarify: it is absolutely impossible for me to split each value into separate cells (and yes, I wouldn't be asking if it were possible :s)

There you go, I hope I am fairly clear,

Thank you in advance for your answers

Configuration: Ubuntu 8.10 or Vista, depending on the moment, Firefox 3.5 Mozilla Firefox 3

Raymond PENTIER · Accepted Answer

Ouch! You can't escape from one or two macros... -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

tontong · Answer

Hello,
It is certainly possible to put multiple values in the same cell.
The challenge will be to find a criterion to distinguish them from each other in order to filter.
Indeed, if it is "absolutely impossible for me to separate each value into independent cells", it will be very difficult to implement a customized automatic filter or an advanced filter. Filters also need to distinguish the values.
To move towards a solution, some clarifications on the nature and format of the concatenated data would be needed.

merlone · Answer

Hello Tontong, thank you very much for your response:
It’s a list of names, so character strings, each name separated by a comma, for example:

Valerio Adami, Jean-Michel Alberola, Louis Cane, Jean Degottex, Hélène Delprat, Daniel Dezeuze, Gerardo Dicrola, Jean Dubuffet, Gérard Garouste, Gérard Gasiorowski, Simon Hantaï, Hans Hartung, Jean Hélion, Christian Jaccard, Yves Klein, Pierre Klossowski, François Lamore, Jean Lamore, Robert Malaval, André Masson, Henri Michaux, Pablo Picasso, Patrick Raynaud, Martial Raysse, Georges Rousse

As you surely understood seeing this list, I work in a museum : ) . A cell containing a series of names like this corresponds to a group exhibition, so I would like each artist's name to appear as a separate filter in the filter for this column. Indeed, some artists have participated in several exhibitions over the years and it would be great to isolate all the relevant exhibitions in 2/3 clicks. I want to avoid the text filter because some colleagues work with OpenOffice where it is not available (arrrggggg... open-source is great but not always super complete).

So if you know of any special nomenclature to deconsolidate the character strings.... I would be very, very interested.
Thank you in advance for your time.

merlone · Answer

Yes, indeed, I think it's going to be necessary; I'm going to gradually look into VB this month. I don't know anything about it at all, if you have an idea for a script I'm interested, but I imagine it's long to program this kind of thing.

Have a good evening or a good day!

Raymond PENTIER · Answer

If you've practiced Excel a bit, you should know that a cell contains:
- Fixed text
- Variable value
- A formula (that performs a calculation)
but never one and the other, nor several of each type.
It’s up to you to ensure that your series of data grouped in a single cell, thus unusable, are distributed across cells that contain only one element.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂

eriiic · Answer

Good evening,  What do you mean by text filter?  Because if you set the automatic filter and choose 'custom filter', you can put 'contains'... Of course, it will be limited to 2 names searched, with the advanced filter (data menu) you will have more options. Otherwise, I'm not sure the compatibility with VBA macros is excellent (?)  Eric

merlone · Answer

Good evening to both of you, thank you for your responses

@Raymond: yes, unfortunately I see what you're talking about, but I thought it would be possible in one way or another to indicate to Excel that the cell contained multiple values... I had thought of your solution, but that would force me to create duplicates (or more) of rows or columns:
Basically, for an exhibition line x, I would put all the artists who participated in the same cell,
this would require me to create as many lines for exhibition x as there are artists who participated....
You'll agree with me that this is not a very clean solution.
After, if I split all the names into different cells (let's say B1 to B11), can we indicate to Excel that they are all attached to the same row, row 1 for example, so that there are no duplicates when I apply a filter?
I know it sounds absurd but in practice it would be very useful.

@Eric: Yes, I believe we are talking about the same thing, I am using Excel 2007, so maybe the automatic filter menu is not exactly the same, I don’t know, in any case I can easily use Contains... but my problem is that this Contains does not exist in Openoffice.org that some colleagues use, well let me know if I’m wrong.

gbinforme · Answer

Hello  You will agree with me that this is not a very clean solution.  Yet, it is the very principle of relational databases used by almost all database software: you have an exhibition, and there can be from 1 to n exhibitors.  Can we indicate to Excel that they are all linked to the same row,  You have a column with the exhibition code or name, and all the exhibitors who have that name or code are part of the exhibition. If you take Access, which is more of a database manager, you will even have to create as many rows as there are exhibitors because otherwise you won't be able to find the exhibitions an grouped exhibitor participated in, like you want to do.  If you look more closely at your problem, you will even realize that creating as many rows as there are exhibitors greatly simplifies the way to retrieve your information: - by selecting an exhibition, you have the list of all the exhibitors nicely "clean" since they are individualized. - by selecting an exhibitor, you can find all their exhibitions. This way, you can make all the desired searches without having to create complicated formulas or macros.  If databases work this way, it's because there are indeed some advantages, and before the arrival of this type of organization, it wasn't as simple. --  Always zen.

Raymond PENTIER · Answer

Hello. First, thank you to gbinforme for this fundamental theoretical reminder. Then, merlone, when you write "that way there are no duplicates when I apply a filter" I remain skeptical, since the role of the filter is precisely to select all records that have the same field value! Moreover, your table seems extraordinarily simple to me: Column A for the exhibition name, Column B for the artist's name, Column C for additional information. Filling in columns A and B is child’s play, thanks to the auto-complete feature. You can even have fun using cell drop-down lists! To exploit this database, you play with filters and sorting, and possibly even with pivot tables... What more could one ask for?  I will try to create a sample file for you. To avoid writing nonsense, give me a few exhibition names, or even a start of your file. -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

lermite222 · Answer

Hello everyone,
An example? ... (if I understood correctly)
Workbook attached
See you soon
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-) (Confucius)

merlone · Answer

Hello everyone and thank you for the time you are spending on my question.

Well gbinforme, thank you for the reminder, yes indeed this solution is effective when researching by artist. When searching by exhibition, you get as many lines as there are artists, but as you said, it must be inevitable.

That is to say filter by exhibition column > select an expo > 20 artists have exhibited so 20 lines: only the artist column changes from one line to another, which is normal, so it’s not entirely a duplicate because I was talking about repetitions of cells belonging to a column on which the current filter does not apply). But it’s true Raymond, it’s indeed the role of a filter to isolate all records having the same value, but we agree, when they belong to the same field.
; ).

In any case, a HUGE THANK YOU TO YOU LERMITE222, your macros have SOLVED my problem, I will now adapt all of this, you must have spent a hell of a lot of time, I am super impressed by your abilities...so again, THANK YOU

The solution was indeed to create large, powerful macros ; )

Good evening everyone, I’m marking this as resolved!

EDIT: yes I just started adding my values to your file (well, your mini program, let's say), it’s awesome!!

kouak · Answer

Hello everyone,

Sorry to bring up this topic that dates back a little over 3 years, but after searching in vain, this discussion focuses the most on what I want to do.

I am in the same situation as Merlone, that is to say, assigning multiple values to an Excel cell on which individual sorting can be performed.
The script developed by LERMITE222 seems promising but unfortunately has been erased from CiJoint.

Do you think it would be possible to put it back online please?

Thank you in advance for your help,
Best regards,
Aurélien

Mfp · Answer

Hello Merlone and Kouak
I have the same need. A list of people assigned to a list of tasks they can perform
Ex: Line 2: Paul is associated with "wash" and "kitchen"
Line 3: Simon is assigned to "wash", "kitchen", "supervision", "mechanics", etc.

I would like to find a way to enter all the tasks in the same cell in column B. And to be able to filter and sort them later to help me when I'm looking for someone who can do, for example, "Washing" and "mechanics"

I tried a drop-down list, but it doesn't allow me to associate multiple tasks for one person. And I then find myself having to duplicate all of Simon's data in order to enter different tasks on each line

This makes for a database that is way too long and not very easy to update

Can someone help me please

Marc

Excel, Multiple Values in the Same Cell?

13 réponses

Saracroche: the effective french app against phone spam

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress

Inuyasha in french