+ Reply to Thread
Results 1 to 18 of 18

Compare list with another column, take rows where true, match to another column value

  1. #1
    Registered User
    Join Date
    01-22-2019
    Location
    Australia
    MS-Off Ver
    Office360
    Posts
    10

    Compare list with another column, take rows where true, match to another column value

    *see attached spreadsheet for data

    Trying to create in "Summary" sheet a list of all information[numbers] when information[comparision of cells] matches one of the "results meaning" sheet lists (either light or heavy results) no duplicates required.

    I know I need an array, tried using INDEX and MATCH functions in an array with ROW but I don't understand it enough to use it properly and it produced different values. to what I needed.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Compare list with another column, take rows where true, match to another column value

    Nope. I haven't a clue what you want. Please add manually calculated results to your sheet and repost it.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Compare list with another column, take rows where true, match to another column value

    I'll guess.

    Array enter this in B6 of 'Summary' and fill down until you get blanks.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then array enter this in B18 and fill down as before.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Those formulas will return the "light and heavy" numbers.

    In C6 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In C18 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Those last two return 'Status'.
    Dave

  4. #4
    Registered User
    Join Date
    01-22-2019
    Location
    Australia
    MS-Off Ver
    Office360
    Posts
    10

    Re: Compare list with another column, take rows where true, match to another column value

    Thank-you for taking a stab - this is very close to what I want, but instead of looking for table2[results 1]="(blank)" and table 2[results 1]="light" what I need it to do is reference the two lists I have in results meaning sheet and if any of those items in that list come up then I want to reference the table2[number] as you've done.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Compare list with another column, take rows where true, match to another column value

    Can we see a before/after sample, as requested (Post 2)

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Compare list with another column, take rows where true, match to another column value

    What Glenn says.

    Will take one more guess though. Array enter this in B6 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Compare list with another column, take rows where true, match to another column value

    My guess is

    Remove the bracket from (blank) from "Information"

    Then Summary
    B6 press Ctrl+Shift+Enter and drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    B18 press Ctrl+Shift+Enter and drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    No idea for Vlookup, they have multiple columns and value.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-22-2019
    Location
    Australia
    MS-Off Ver
    Office360
    Posts
    10

    Re: Compare list with another column, take rows where true, match to another column value

    Sorry see attached example spreadsheet with pasted values. Bo_Ry you seem to be on the right track as you're referencing the results lists, but I haven't checked yet.
    Attached Files Attached Files

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Compare list with another column, take rows where true, match to another column value

    Then normal Vlookup at C6 would do

    =IFERROR(VLOOKUP(B6,Table2,6,),"")

  10. #10
    Registered User
    Join Date
    01-22-2019
    Location
    Australia
    MS-Off Ver
    Office360
    Posts
    10

    Re: Compare list with another column, take rows where true, match to another column value

    So Bo_Ry, you're referencing the "result 1", "result 2" etc columns which isn't necessary as the list should match the "comparison of cells" column, also those brackets for blankblankblank are auto put into that cell so the brackets will remain. I should put brackets in the list in results meaning though so they are the same. I've used the normal Vlookup in column C its not really the issue here.

    breaking it down into smaller sections I need an equation to:

    1. get all the row numbers in Table2 where [comparison of cells] responses matches one of the lists in results meaning sheet ( I can duplicate the formula for the second list)

    2. using those row numbers produce the result in the number column, no repeat required

    i.e. the light list matches "lightlightlight" which would result in rows 11,12,15,16,25 and 26 being put into an array, however when looking for the [number] result, I don't want two P067 to show up when the equation is dragged down if possible.

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Compare list with another column, take rows where true, match to another column value

    After you add brackets to Results meaning sheet

    Try at B6 With CSE, match Results meaning with Comparison.
    Row number in blue


    =IFERROR(INDEX(Information!B:B,SMALL(IF(FREQUENCY(IF(--ISNUMBER(MATCH(Table2[Comparison of cells],'Results meaning'!$H$9:$H$23,)),MATCH(Table2[Number],Table2[Number],)),ROW(INDIRECT("1:"&ROWS(Table2)-1)))=COUNTIF(Table2[Number],Table2[Number]),ROW(Table2)),ROWS(B$6:B6))),"")

  12. #12
    Registered User
    Join Date
    01-22-2019
    Location
    Australia
    MS-Off Ver
    Office360
    Posts
    10

    Re: Compare list with another column, take rows where true, match to another column value

    Sorry Bo_Ry, it still didn't work - none of the results came up.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Compare list with another column, take rows where true, match to another column value

    Is this what you meant? I put the results in column A for comparison:

    =IFERROR(INDEX(Information!B:B,AGGREGATE(15,6,ROW(Table2[Number])/((ISNUMBER(MATCH(Table2[Comparison of cells],'Results meaning'!$F$9:$F$23,0)))*(COUNTIF(A$5:A5, Table2[Number])=0)),1)),"")

    However, you do need to ensure that

    (blank)(blank)(blank) on "information" matches blankbankblank on results meaning....I changed the latter to (blank)(blank)(blank)
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-22-2019
    Location
    Australia
    MS-Off Ver
    Office360
    Posts
    10

    Re: Compare list with another column, take rows where true, match to another column value

    thanks Glenn, yes it is what i meant! Thank-you. I'll change that in my actual file.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Compare list with another column, take rows where true, match to another column value

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  16. #16
    Registered User
    Join Date
    11-14-2016
    Location
    usa,CA
    MS-Off Ver
    2010
    Posts
    20

    Re: Compare list with another column, take rows where true, match to another column value

    How do you add the file? I dont see any attachement options.

  17. #17
    Registered User
    Join Date
    11-14-2016
    Location
    usa,CA
    MS-Off Ver
    2010
    Posts
    20

    Re: Compare list with another column, take rows where true, match to another column value

    Hi Glen,

    Could you please help me figure out what to do with this excel spreadsheet?
    I have a row with columns that I want to put in formula.

    See attached file.

    Thanks,
    Attached Files Attached Files

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Compare list with another column, take rows where true, match to another column value

    Please take a moment to re-read rule 4. Do not hijack threads. Start your own. Also, please amend your sheet to show your desired results.

+ 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] Formula to compare column 1 to column 2 and list items not in column 2.
    By mahalek1976 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2016, 12:55 PM
  2. [SOLVED] VBA code to compare rows based on 4 column values and highlight is no match
    By Sirilias in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-16-2015, 02:22 PM
  3. [SOLVED] Compare value on a range if match list linked column
    By ricklou in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-28-2015, 01:54 PM
  4. [SOLVED] Compare 1 column with another column and match data in next column
    By screambeam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2015, 02:49 AM
  5. Replies: 6
    Last Post: 05-07-2014, 12:13 AM
  6. [SOLVED] Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not matched
    By Synchronicity in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-11-2013, 01:21 PM
  7. Compare values in column a to column b, and put it in column c if they match
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2013, 07:52 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