+ Reply to Thread
Results 1 to 21 of 21

Match 3 set of columns and return value from another column in case of a unique match.

  1. #1
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Question Match 3 set of columns and return value from another column in case of a unique match.

    1) I need to match column B, C & D from 'Product Test Offshore' sheet with
    columns C, D & E resp from 'DATA' sheet and in case of a match on all 3,
    copy corresponding value from column G from 'Data' sheet into
    corresponding column F of 'Product Test Offshore' sheet.

    All columns may involve duplicates & unsorted data and column D & E of 'DATA' sheet may involve blank cells.

    I tried Index, Match combination for above but not working for me.

    Note : Only excel 2003 workable solution needed.


    2) In Status summary sheet, I need to pull the count of different
    status of differnt business process from Product Test Offshore. Column
    A has business process & G has status.
    I tried sumproduct, vlookup, count etc but not working.

    Note : Only excel 2003 workable solution needed.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-06-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    1) Put the formula below in cell F1 of "Product Test Off Shore" and drag it down.


    =IF(AND(B2=Data!C2,C2=Data!D2,D2=Data!E2),Data!G2,"~")


    2) I don't understand this one. Column A has "Team" and nothing says "business process". Column B says "Process ID". I'm okay with column G "Status"

    Explain better please.
    Last edited by Frasterist; 04-14-2013 at 02:57 PM.

  3. #3
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    Quote Originally Posted by Frasterist View Post
    Put the formula below in cell F1 of "Product Test Off Shore" and drag it down.


    =IF(AND(B2=Data!C2,C2=Data!D2,D2=Data!E2),Data!G2,"~")
    Actually the above formula will not work. My requirement is bit complex.

    I have just craeted some sample data to explain my problem.
    Here column 1 to match with 4, if match found then match corresponding values(same row) in column 2 & 5, if match found then match corresponding values in column 3 & 6 and if match found then copy the corresponding value from column 7 in the formula cell.
    All above should happen considering duplicates, unsorted data & excel 2003.

    a 3 10 c 1 10 A
    a 1 20 c 2 20 B
    a 2 30 c 2 30 C
    c 2 20 c 2 40 D
    c 3 40 c 3 40 E
    c 5 50 a 1 20 F
    b 2 10 a 2 10 G
    b 1 30 a 2 20 H
    b 3 50 a 2 30 I

  4. #4
    Registered User
    Join Date
    04-06-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    I thought I understood your first question "1)". Did you insert the formula in F1 and try it?

    I'll attach your file with the formula already inserted.

    It was your second question that I couldn't understand, the question preceded by "2)"
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    Quote Originally Posted by Frasterist View Post
    I thought I understood your first question "1)". Did you insert the formula in F1 and try it?

    I'll attach your file with the formula already inserted.

    It was your second question that I couldn't understand, the question preceded by "2)"
    You would notice that only first 5 cells are getting value and rest none till the end(you can try by dragging till end). Even the next 3 after the 5th cell are also not getting the value whereas we can clearly see the matching for those. Infact all the cells should fetch some value as the first set of 3 columns are subset of next set of 3 columns from DATA sheet.

    So still my first question seems unresolved !!

  6. #6
    Registered User
    Join Date
    04-06-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    Okay, I'll bite. Tell me which row after 6 is a match.

    I attached the file again (#4) with color added to make it easier to see the comparisons. Columns that are the same color in each sheet are the ones being compared for a match. I dropped down to rows 7, 8, and 37 and added some color there too for clarity. Sorry, they don’t match:



    row 7 Product 100 1 3
    row 7 Data 100 1 60


    row 8 Product 100 1 47
    row 8 Data 100 1 61


    row 37 Product 102 2 110
    row 37 Data 102 2 130
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    I don't want to limit the match to same row number. Its more of a lookup where we need to first find the value from first column in corresponding column of DATA sheet(not necessarily same row number, infact it will not be in almost all cases), if value found then try to find same row second column value from product sheet in DATA but for the same value of first column. If second match happen try to match third column, if second match does not happen try to move to move to next row. So basically keep on moving in DATA sheet till exact combination of 3 cells from "Product" sheet found in DATA sheet.

    For easier understanding of data again pasting some sampe data. Consider First three from Product sheet & next 4 from Data. Value return would from 7th column in case first 3 combination match with next 3:

    a 3 10 c 1 10 A
    a 1 20 c 2 20 B
    a 2 30 c 2 30 C
    c 2 20 c 2 40 D
    c 3 40 c 3 40 E
    c 5 50 a 1 20 F
    b 2 10 a 2 10 G
    b 1 30 a 2 20 H
    b 3 50 a 2 30 I

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    For 1) try this...

    for a regular formula, on sheet1, cell H2, copied down, use this...
    =B2&C2&D2
    then on sheet2, cell H2, copied down, use this...
    =C2&D2&E2
    Then in F2, copied down, use this...
    =INDEX(Data!G:G,MATCH('Product Test Off Shore'!H2,Data!H:H,0),1)

    OR use an array formula, in F1, copied down, use this...

    =INDEX(Data!G:G,MATCH('Product Test Off Shore'!B2&'Product Test Off Shore'!C2&'Product Test Off Shore'!D2,Process_ID&Procedure_ID&Condition_Num,0),1)
    entered using CTRL SHIFT enter, not just enter
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    1) Thanks a ton for this help !! The trick of combining cells worked, infact we can directly combine these cell in index formula for atleast first sheet rather than creating separate column. Can we do this for DATA sheet as well for whole column range ?

    2) Also putting my second question again. In Status summary sheet(same excel sheet), I need to pull the count of combination of 'status' & 'business process' from Product Test Offshore sheet(same excel sheet). Column A has business process & G has status in Product Test Offshore sheet.
    Once I have the count I can create other stats from it.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    Yes, sorry, wasnt thinking - dint need the helper on sheet1...
    =INDEX(Data!G:G,MATCH(B2&C2&D2,Data!H:H,0),1)

    I am llooking into your 2nd question now

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    on status sheet, B2, copied down and across...

    =SUMPRODUCT(('Product Test Off Shore'!$A:$A='Status Summary'!$A2)*('Product Test Off Shore'!$G:$G='Status Summary'!B$1))

  12. #12
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    This formula is giving #NUM! error for all combinations :-(

    Status/ Business-process In Progress Tested Successfully Test Condition Failed - TPR raised Technical Issue Query Sent to onshore Target Count till today
    Tariff Modeling #NUM! #NUM! #NUM! #NUM! #NUM! #NUM!
    Billing #NUM! #NUM! #NUM! #NUM! #NUM! #NUM!
    Credit Collection #NUM! #NUM! #NUM! #NUM! #NUM! #NUM!
    Finance #NUM! #NUM! #NUM! #NUM! #NUM! #NUM!
    Orders #NUM! #NUM! #NUM! #NUM! #NUM! #NUM!

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    see the attached...
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    If you want code as an option, there is a button on the first sheet
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    I was using the same suggested formula but not working.

    When I opened your sheet and try to edit the formula by simply removing the bracket & adding back, it started giving me same #NUM! error, where earlier in your original sheet there was number reflecting !!

    Is there any difference in version ?? I have to survive with excel 2003 only !!

  16. #16
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    How can I see the code behind the button ? And how to add that button ?

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    Quote Originally Posted by hisaurabhmact View Post
    I was using the same suggested formula but not working.

    When I opened your sheet and try to edit the formula by simply removing the bracket & adding back, it started giving me same #NUM! error, where earlier in your original sheet there was number reflecting !!

    Is there any difference in version ?? I have to survive with excel 2003 only !!
    OK which formula/question are we talking about here?

    If you are talking about question 1, in teh file I uploaded, I still have the array formula included, that MUST be entered with CTRL SHIFT enter, else use the formula from post #10

    If you are talking about question 2 (post 11), please explain what went wrong?

  18. #18
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    I have already added a button on first sheet with pink colour, Column K(Press me)

  19. #19
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    Quote Originally Posted by FDibbins View Post
    OK which formula/question are we talking about here?

    If you are talking about question 1, in teh file I uploaded, I still have the array formula included, that MUST be entered with CTRL SHIFT enter, else use the formula from post #10

    If you are talking about question 2 (post 11), please explain what went wrong?
    Talking about question 2

    Unable to attach the sheet last attached by you. But let me try to explain what I did. Here I just deleted and added back the last bracket close(basically to let that cell take the effect of version I am using) of cell B2 & B3 in Status summary sheet and it started giving me #NUM! error. I didn't touch any other cell on this sheet and so they are looking fine.


    #NUM! 3 0 0 0 0
    #NUM! 43 0 2 0 0
    0 0 0 0 0 0
    0 0 0 0 0 0

    So same formula if giving correct value to you but on my system giving this error !!


    Adding some more analysis:

    The problem seems to be with 'Product Test Off Shore'!$A:$A or 'Product Test Off Shore'!$G:$G
    of the formula as while doing error evaluation, this one first getting #NUM!
    Last edited by hisaurabhmact; 04-14-2013 at 06:42 PM. Reason: clarification

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    the formula I used will work just fine in 2003. Are the columns that you are referencing still the exact same 1's as per your sample workbook?

    the formula...
    =SUMPRODUCT(('Product Test Off Shore'!$A:$A=$A2)*('Product Test Off Shore'!$G:$G=B$1))
    uses the data in sheet3 column A "Tarrif Modeling" (A2), "Billing" (A3) etc and the status in sheet3 row 1 "In Progress" B1, "Tested" C1 etc to count the entries on sheet1 column A and column G that match.

    So if your column/cell refs dont match in your "real" data, then you will need to adjust the references as needed

    It would be easier to trouble-shoot if I could see what you have done

  21. #21
    Registered User
    Join Date
    04-14-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Match 3 set of columns and return value from another column in case of a unique match.

    Attaching the same sheet which you attached few posts back with the formula applied on Status summary sheet.

    Notice that B2 & B3 are showing #NUM!, because I just tried to edit it(only tried didn't edited anything).

    These cells are still having same formula which you inserted but on my system, may be becasue of version issue, not doing the intended function.
    I didn't touched the other cell as they will also turn #NUM! as shown earlier.

    Mine is excel 2003. Do you see any issue with that ??
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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