Hi,

I have a list with titles in column one and properties in column two.

I want to use a function that returns as few properties as possible, but if I filter the original list for all returned properties every title has to be included at least once.

Example (titles are represented by the numbers; properties are represented by the letters):

1 A
1 B
1 C
2 A
2 Z
3 B
3 C
3 K
3 L
4 D
5 E
5 L

Return should be: A;D;L

Can someone help please?

I think more info is needed here...it's not clear what you're trying to do.

Oh ok.
Maybe this helps:

In the example above, one can see that each number is connected to one or multiple letters.

If I apply an excel filter to the list above, I want to tick the boxes for as few as possible letters but still get back all numbers at least once.

So if I apply an excel filter to the list above and only tick A, D, and L, I would get back:

1 A
2 A
3 L
4 D
5 L

In conclusion, all numbers are seen at least once, and I only ticked the boxes for three letters (A, D, and L)

However, the real list is a lot more complex. I need a function that returns a list of letters as small as possible but still fulfills the condition that all numbers are shown at least once when I tick the boxes for each letter returned by the function.

Please let me know whether it is more clear now.

Removed by JT: back to the drawing board

BZDL or ADL or CZDL but I still don't know what you want to achieve
it must be as any automatic process ?

 number Custom 1 A,B,C 2 A,Z 3 B,C,K,L 4 D 5 E,L

Here you go.

Test.xlsx

The return should be as few letters as possible, so in the example case, the only solution would be ADL.
As BZDL and CZDL consist of more letters than ADL.

However, in other data sets, there can be multiple solutions, of course.

Yes, I want an automatic process and do not find the solution myself, as the original data set is much more complex than the provided example.

Can you come up with a suitable excel formula?

for formula you'll need to wait for someone else, I prefer Power Query

btw. your excel file doesn't contain expected result

My excel file does not contain the expected result because I need an excel formula to create the result.

example of expected result manually created, because nobody knows which is A and which is B

maybe something like this as example of the result with distinct all numbers and collapsed subject for each number (similar to post #5)

Not sure what is unclear here.

I just used the letters in the provided example to make it easy.

So they were just used as substitutes for EMPLOYEE selection, JOB resumes, EMPLOYEE retention, etc.

If this is not the answer to your question, please rephrase it.

see post #11

If I see this correctly, you just consolidated every subject of a number in one line.

The answer, however, should look something like this:

EMPLOYEE selection; JOB resumes; EMPLOYEE retention; industry; restaurant

(If this would include all numbers when the respected boxes in the filter are ticked - what is clearly not the case.)

I have no more idea
check this, if this is not what you want we will need to wait for someone else

This is not what I search for either.

Let's wait for someone else, then.

However, thank you for your support and time.
Highly appreciate it.

You are welcome
sorry I didn't help

I would like to help but I too still do not understand what you are after. I know you're explaining it, but it's not making sense to me.

It sounds like you might be wanting every unique number, and then all the unique "letters" that appear for that number, but I don't know if I am following you.

See images
https://i.imgur.com/jX9um4r.png
https://i.imgur.com/8v23bAz.png

these Subjects participate in all Numbers

I am sorry if I have expressed myself in a misleading way.
I think the example I gave + the desired result might be helpful in understanding.

In the example there are five numbers. Each number is assigned one or more letters.
I am looking for an Excel formula in which I specify as input both columns and which gives me as a result a list of letters.

The output list should have the following properties:
1. if an Excel filter is applied to the input list and all letters contained in the output list are displayed, all numbers should be displayed in the input list.

There are several solutions for this property. e.g.:
ABCDEKLZ; ABCDEKL; ABCDEK; ABCDEL; ABCDE; ADL; ... and many more solutions

ABEL, for example, would not be a solution. If you display all items with ABEL using the filter function in Excel, you get the following result:
1 A
1 B
2 A
3 B
3 L
5 E
5 L

This is not a solution that satisfies property 1. since obviously the number 4 is not included.

However, the outputted one must have additionally another property, namely:
2. the output list must be as short as possible.

In the example there is only one solution which fulfills both criteria, namely:

This is the case since all other solutions which fulfill criterion 1. (like e.g. ABCDEKLZ; ABCDEKL; ABCDEK; ABCDEL; ABCDE) are longer than three letters.

I hope it is clearer now.

with the second example
source: there are 378 rows with duplicate numbers
result: there are 257 rows of distinct subjects which include all unique numbers, of course subjects can or rather must be duplicated due to the number of numbers

in short like this
 number subject 1 subject1 1 subject2 2 subject1 3 subject2 3 subject3 4 subject5 5 subject4 5 subject3

with the result
 subject subject1 subject2 subject3 subject5

Of course all subjects of numbers only assigned one subject must be included. However, it's all about how to connect duplicate numbers in the most efficient way.

For example if:

 1 Apple 1 Orange 2 Apple 2 Banana 3 Peach 3 Apple

There would be no need to include Peach, Banana, or Orange, since all numbers would be included when only numbers with the subject Apple are shown.

So the excel formula I search for would return, in this case: Apple

No, result in your case should be

Subject 1
Subject 3
Subject 5

As these subjects would connect all numbers most efficiently.

ops, my mistake sorry

No problem.

But I think it is now clear to you what I am looking for.

for formula you'll need to wait for FormulaMaster

anyway it was an interesting experience
have a nice day

See attached: is this correct?

``Please Login or Register  to view this content.``

That's it.
Great job.

Thank you, sir!

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1