+ Reply to Thread
Results 1 to 28 of 28

Please Help! - Excel database work

  1. #1
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Please Help! - Excel database work

    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?
    Last edited by TNTN; 03-16-2023 at 04:47 PM.

  2. #2
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Please Help! - Excel database work

    I think more info is needed here...it's not clear what you're trying to do.
    Last edited by NewYears1978; 03-18-2023 at 11:17 PM.

  3. #3
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    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.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Please Help! - Excel database work

    Removed by JT: back to the drawing board
    Last edited by JohnTopley; 03-18-2023 at 10:21 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Please Help! - Excel database work

    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

  6. #6
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    Here you go.

    Test.xlsx

  7. #7
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    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?

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Please Help! - Excel database work

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

    btw. your excel file doesn't contain expected result

  9. #9
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

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

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Please Help! - Excel database work

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

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Please Help! - Excel database work

    maybe something like this as example of the result with distinct all numbers and collapsed subject for each number (similar to post #5)
    Attached Files Attached Files
    Last edited by sandy666; 03-18-2023 at 11:38 AM.

  12. #12
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    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.

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Please Help! - Excel database work

    see post #11

  14. #14
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    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.)
    Last edited by TNTN; 03-18-2023 at 11:47 AM.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Please Help! - Excel database work

    I have no more idea
    check this, if this is not what you want we will need to wait for someone else
    Attached Files Attached Files
    Last edited by sandy666; 03-18-2023 at 12:20 PM.

  16. #16
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    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.

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Please Help! - Excel database work

    You are welcome
    sorry I didn't help

  18. #18
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Please Help! - Excel database work

    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

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Please Help! - Excel database work

    these Subjects participate in all Numbers
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    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:
    ADL

    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.

  21. #21
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Please Help! - Excel database work

    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
    Last edited by sandy666; 03-19-2023 at 08:46 AM.

  22. #22
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    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
    Last edited by TNTN; 03-19-2023 at 08:51 AM.

  23. #23
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    No, result in your case should be

    Subject 1
    Subject 3
    Subject 5


    As these subjects would connect all numbers most efficiently.
    Last edited by TNTN; 03-19-2023 at 08:53 AM.

  24. #24
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Please Help! - Excel database work

    ops, my mistake sorry

  25. #25
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    No problem.

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

  26. #26
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Please Help! - Excel database work

    for formula you'll need to wait for FormulaMaster

    anyway it was an interesting experience
    have a nice day

  27. #27
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Please Help! - Excel database work

    See attached: is this correct?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 03-20-2023 at 04:05 PM.

  28. #28
    Registered User
    Join Date
    03-16-2023
    Location
    Germany
    MS-Off Ver
    2302 (Build 16130.20306 Click-to-Run))
    Posts
    13

    Re: Please Help! - Excel database work

    That's it.
    Great job.

    Thank you, sir!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SOLVER problem with a lot of restrictions (BEST POSSIBLES VALUES)
    By dontafy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-01-2019, 03:30 PM
  2. [SOLVED] Extract unique limited, number of values based on criteria in ascending order
    By Villalobos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2017, 04:56 AM
  3. [SOLVED] Restrictions in Text boxes values
    By Mangai Venkata in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-05-2016, 05:47 AM
  4. [SOLVED] Setting Restrictions On Cell Values
    By HurstUOB in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-07-2015, 01:27 PM
  5. Limited number of matrix operations?
    By ldj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-01-2010, 10:16 AM
  6. Grouping values by restrictions
    By patriko123 in forum Excel General
    Replies: 2
    Last Post: 06-04-2008, 02:09 AM
  7. [SOLVED] limited number of columns
    By Katja in forum Excel General
    Replies: 2
    Last Post: 02-05-2005, 12:06 PM

Tags for this Thread

Bookmarks

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