+ Reply to Thread
Results 1 to 15 of 15

Get column value based on cell valued with duplicates

  1. #1
    Registered User
    Join Date
    03-20-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    7

    Question Get column value based on cell valued with duplicates

    Hello everyone!

    I have this table:

    Attachment 636234

    **********321------206------207------210------213------214------215------329------216------222***
    321---AA---311---------------------------------------------------1--------------------1
    206---BB--------------345-----------------------------2-----------------------------------------5
    207---CC--------------------------188----------------11-------------------------------2
    210---DD---1----------2-------------------397------------------------------1------------------10
    213---EE----------------------------------------------157---------------------------------------------------3
    214---FF--10----------------------------------------------------227-------------------2--------------------4
    215---GG---------------1-------------------7----------1-------------------256--------2--------17---------3
    329---HH------------------------------------------------------------------------------254
    219---II-----------------3------------------3------------------------------1--------------------193----
    222---JJ------------------------------------------------------------3------------------------------1--------236

    I am trying to get the row and column values based on a given name from column 1 and 2, like this:
    Name: BB (finds row values)
    Loc: 206 (finds column values)

    Expected Result:
    **206------213------2016**
    345------2 ------ 5
    2
    1
    3

    So far I got this formula: INDEX($D$14:$M$14,,MATCH(D23,INDEX($D$15:$M$21,MATCH($C$23,$B$15:$B$21,0),),0))
    but it does not work when same numbers are in the row.

    Please I need your help, your knowledge.

    Thanks
    Attached Files Attached Files
    Last edited by dna7gran; 08-12-2019 at 11:46 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Get column value based on cell valued with duplicates

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-20-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    7

    Re: Get column value based on cell valued with duplicates

    Thanks.
    I just updated the post and attach a mock table.
    Now, I am crossing fingers.
    Have a nice day

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Get column value based on cell valued with duplicates

    You should be able to put this formula in E17 and then copy over and down:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Get column value based on cell valued with duplicates

    I forgot to mention that the numbers you have in the top row are text, but the numbers in the bottom grid are numbers. You have to make them the same (either make them all text or all numbers) or it won't work.

  6. #6
    Registered User
    Join Date
    03-20-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    7

    Re: Get column value based on cell valued with duplicates

    Thanks for your time.
    I guess I did not express myself correctly.
    The solution you provide works if I know the row and column headers.
    But I want to extract them from the table, providing only Column Header and name.
    I want the whole row and whole column without blank spaces from the table

    Like this:

    345-2-5
    2
    1
    3

    Sorry for the misunderstanding.

    Thanks again for your time and support

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Get column value based on cell valued with duplicates

    I think I know what you mean. With an input of a "Loc" and a "Name", you would like the whole "mini-table" to be generated starting at G16 - you want the headers across the top filled in and also the labels down the left hand side automatically filled in and then numbers inside? Are you looking do this without VBA/macros?

  8. #8
    Registered User
    Join Date
    03-20-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    7

    Re: Get column value based on cell valued with duplicates

    I don't mind working with VBA.
    Yes, I want a "mini-table" to be generated.
    Yes, you read my mind, I want the headers across the top filled in and also the labels down the left hand side automatically filled in and then the numbers inside on the "mini-table"

    Am I asking a lot?

    Thanks

  9. #9
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Get column value based on cell valued with duplicates

    I don't know how that can be done without using VBA (at least not elegantly).
    I'm not an expert in VBA, but I'm sure it would be doable - just a lot of work for me - sorry. Hopefully someone else will read this and have a much better answer!

  10. #10
    Registered User
    Join Date
    03-20-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    7

    Re: Get column value based on cell valued with duplicates

    What if I have the values inside the mini-table?
    I will need only the headers from the column and labels from the rows.

  11. #11
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Get column value based on cell valued with duplicates

    I think I may have something, but I have a question:
    Can you upload a file which shows the result grid as it should be if you had the number 7 in Cell D3 and the number 8 in Cell e2?

    Or, instead of showing it for BB and 206, show results for DD and 210.

  12. #12
    Registered User
    Join Date
    03-20-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    7

    Re: Get column value based on cell valued with duplicates

    Here is the file with the new result given DD and 210:
    321 206 210 215 216
    DD 1 2 397 1 10
    GG 7
    II 3
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Get column value based on cell valued with duplicates

    OK, so I didn't think it could be done without VBA, but I was able to make it work with just formulas. Like I said, it's not elegant, but it does work.

    If you open the attachment and enter any Name and Loc, it seems to build the table as expected. I used some helper rows and columns.
    I made the font white for those cells so it looks cleaner, but you can see them in columns starting at N2 and O2, and then in rows starting at D13 and D14.

    While the formulas work by just taking the first ones in each row/column and dragging them down or across (including the formulas in the result grid), you'll
    have to do some editing if your top table has more rows and columns.

    I didn't take anymore time to try and build the formulas so they could be used when the table is bigger, but if this works for you,
    you can probably get the idea and expand the formulas to work as needed.

    There may be a much simpler solution - maybe using the Aggregate formula, but I don't know how to use that formula yet.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-20-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    7

    Re: Get column value based on cell valued with duplicates

    Thank you so much!
    I just tried it and it works perfectly!!

    Thanks for your time and patience.

    Have a nice weekend.

  15. #15
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Get column value based on cell valued with duplicates

    Happy to help and glad that it works for you.

+ 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. Replies: 2
    Last Post: 02-26-2018, 12:31 PM
  2. [SOLVED] Add up quantitites in Column E based on duplicates in Column A, then delete duplicates
    By eli-vergara in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2016, 05:44 PM
  3. Return Number of Unique Valued Based On Specific Text
    By ljochmann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2015, 02:27 PM
  4. Format the lowest valued cell without effecting duplicates
    By gomezth3killa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2015, 03:14 AM
  5. Replies: 5
    Last Post: 05-08-2014, 07:24 AM
  6. Merge values from column in one cell, based on duplicates in another column
    By Rawland_Hustle in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-24-2014, 11:04 AM
  7. Require a cell to be valued based on another cell
    By psmith4497 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2005, 01:14 AM

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