Results 1 to 10 of 10

Thread: Matching number with a given particular text

  1. #1

    Matching number with a given particular text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Is there any function that will help in matching the below conditons

    1) Match Trans Type Transfer In - Inspecie with Transfers In with same amount

    2) Match Trans Type Repurchases with Sell with same amount

    3) Match Trans Type Buy with Sales with same amount
    Attached Files Attached Files

  2. #2
    And what your expected results? Maybe more clear describe it at your file

    Thanks

  3. #3
    Maybe this array​ formula

    =IFERROR(INDEX($D$2:$D$19,MATCH(-C2,IF($E$2:$E$19=INDEX({"Sales","Sell","Transfers In"},MATCH(E2,{"Buy","Repurchases","Transfer In - Inspecie"},0)),$C$2:$C$19),0)),"")
    Last edited by Bob Phillips; 2014-09-17 at 12:11 PM.

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    I'm getting a fail on searching the negative (buy = - sale) plus there is a rounding problem.
    I inserted a D column D2 = =ROUND(C2,2)

    Then the three array formulas (Cntrl Shift Enter)

    H2 = IF($F2="Transfer In - Inspecie",ROW()+MATCH(C2,C3:C100,0),"")
    I2 =IF($F2="Repurchases",MATCH(-D2,IF(F:F="Sell",D : D,0),0),"")
    J2 =IF($F2="Sales",MATCH(-D2,IF(F:F="Buy",D : D,0),0),"")

    Returning the row number, Use index to get whatever you want
    H2 also needs an IFERROR but leaving that to you
    Last edited by WizzardOfOz; 2014-09-17 at 09:31 AM. Reason: Not D:D but D : D

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    [QUOTE=WizzardOfOz;14928]I'm getting a fail on searching the negative (buy = - sale) plus there is a rounding problem.
    I inserted a D column D2 = =ROUND(C2,2) /QUOTE]

    If he had seen the information in other posts on this site about the problem, Bob would have been able to pick up the point about matching + with - values, but this is not mentioned in post #1 here.
    I think that by posting multiple questions, none of which explain the problem fully or accurately a situation similar to cross posting arises.

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by WizzardOfOz View Post
    I'm getting a fail on searching the negative (buy = - sale) plus there is a rounding problem.
    I inserted a D column D2 = =ROUND(C2,2)
    If he had seen the information in other posts on this site about the problem, Bob would have been able to pick up the point about matching + with - values, but this is not mentioned in post #1 here.
    I think that by posting multiple questions, none of which explain the problem fully or accurately a situation similar to cross posting arises.

  7. #7
    Bob Phillips, WizzardOfOz & Hercules1946 I appreciate your efforts, but the thing is not working for me, please check if this can be resolved,

    Thanks in advance

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by dinesh.sarsar View Post
    Bob Phillips, WizzardOfOz & Hercules1946 I appreciate your efforts, but the thing is not working for me, please check if this can be resolved,

    Thanks in advance
    Hello Dinesh
    Perhaps you could start by responding to the points I made in your other post, namely that you don't have enough information to properly automate this process. The correct way to do this is to ensure that matching records carry a common reference,exactly like the match codes that you are allocating manually (1,2,3 Etc). However, this must be done when the records are created, so that you aren't faced with a lot of unmatchable data when the records are eventually brought together.
    To illustrate the matching problem, look at rows 2 and 3 in your workbook "Deals.xls" in Post #1.
    Manually its easy to determine that these rows match with rows 12 and 13, but its impossible to say which is which without more information. You may say that for your purposes that doesn't matter, but others using the information may not agree, and after all there will only be one correct way to match them.
    Despite both sets of records carrying detailed references, there is no commonality in any of them.
    You need to address this issue if you want to automate the process, and resolve the errors that you must be making with your manual matching.
    Last edited by Hercules1946; 2014-09-19 at 06:17 PM.

  9. #9
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    I inserted a round column in D to get rid of the non matching and negative issue. Then I added 3 columns and return the row number for each match. If you add
    index( column to return, my formula) it will return your answer but as Hercules points out I don't know what you want to see, date / ref etc

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by WizzardOfOz View Post
    I inserted a round column in D to get rid of the non matching and negative issue. Then I added 3 columns and return the row number for each match. If you add
    index( column to return, my formula) it will return your answer but as Hercules points out I don't know what you want to see, date / ref etc
    When two matching records are found, the OP needs to add a common numeric value to each, starting with 1, and using the next available number for the next match.

Tags for this Thread

Posting Permissions

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