Matching number with a given particular text

dinesh.sarsar

New member
Joined
Sep 8, 2014
Messages
21
Reaction score
0
Points
0
Location
India, Maharashtra Pune
Excel Version(s)
MS OFFICE Windows 10
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
 

Attachments

  • Deals.xls
    17 KB · Views: 28
And what your expected results? Maybe more clear describe it at your file

Thanks
 
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:
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:
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.
 
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.
 
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
 
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:
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
 
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.
 
Back
Top