Multiple lookup and finding unmatched data in each case

[paradise2sr]

Hi,

I have a problem with that of finding difference in each part of data.First Data consist from Column C:D and 2nd Data consists from F:G.Since this is a sample data only,I want a formula that quickly find differences .Let me summarise,

Column B numerial part must match with Column F numerical parts, and now if these two column matches then automatically matches Column C and Column D their value.
Now if first data does not matches / does not found in 2nd data and vice versa then,in that case there must mentioned as "difference" or "Not found" in either column D and Column H.

Hope I could be able to make a sense.

With Best Rgds,
Suresh

[boopathiraja]

hi, Refer the attachment,

[misrasomendra]

Hi,

See the file.

[paradise2sr]

Thanks for the reply.I have seen.You have done working in 3 columns.Will you be able to merge three column workings into a single column for each part.Since practically I have to face a lot of data for which I have only a nick of time.If you can help me in doing in a single column for each part tracing the result of only 'False' part,I will be very much and much more thankful in this regard.

[paradise2sr]

@misrasomendra

there is no file in your attachment

[misrasomendra]

Hi,

Which solution are you ref. to as in my file, it is single column result only.Yellow cells.

[boopathiraja]

HI, Ref the file, True means match and false means No Match

[paradise2sr]

HI, Ref the file, True means match and false means No Match

Thanx dude,you did really a wonderful job in saving my a lot of times.At last what I want to see is the "FALSE" one in the column D & H .Just little bit revised is required that if the result is "FALSE" then it must show in column D & H as "FALSE" and if it is "TRUE" then it show only blank cells in column D & H.

I think you can do it.Hoping for the best

Thanx again boopathiraja.

With Best Rgds,

[paradise2sr]

Hi,

Which solution are you ref. to as in my file, it is single column result only.Yellow cells.

Thanx for your effort.Refer Bhoopatiraj last post & my post I want that sort of result but to display only FALSE in each case & TRUE must be blank.

[misrasomendra]

Well I gave solution as per your first post in which you did not mentioned anything about TRUE or FALSE. My solution was looking for Invoice number in Column H and returning a good if invoice number is there and values are same else if invoice number is same but values are different or not found of invoice number is not there.

This is a single cell formula in D3 or J3 which will return TRUE or FALSE.

=IF(SUMPRODUCT(--ISNUMBER(FIND(MID(F3,FIND("/",F3)+1,255),$B$3:$B$10))),IF(INDEX($C$3:$C$10,MATCH(TRUE,ISNUMBER(FIND(MID(F3,FIND("/",F3)+1,255),$B$3:$B$10)),0))=G3,TRUE,FALSE),FALSE)

[boopathiraja]

Hi, Ref the attachment

[Punnam]

hi,

Check this
Small Modification delete the data in Column & copy paste the formula in column I & drag it down

Punnam

[samba_ravi]

SEE THE ATTACHED FILE
=IFERROR(IF(MATCH(RIGHT($F3,LEN($F3)-FIND("/",$F3)),RIGHT($B$3:$B$10,LEN($B$3:$B$10)-3),0),"Good"),"Not Found")
TRY THIS ARRAY FORMULA IN H3 and copy towards down (Shift + Ctrl + Enter)

[paradise2sr]

Well I gave solution as per your first post in which you did not mentioned anything about TRUE or FALSE. My solution was looking for Invoice number in Column H and returning a good if invoice number is there and values are same else if invoice number is same but values are different or not found of invoice number is not there.

This is a single cell formula in D3 or J3 which will return TRUE or FALSE.

=IF(SUMPRODUCT(--ISNUMBER(FIND(MID(F3,FIND("/",F3)+1,255),$B$3:$B$10))),IF(INDEX($C$3:$C$10,MATCH(TRUE,ISNUMBER(FIND(MID(F3,FIND("/",F3)+1,255),$B$3:$B$10)),0))=G3,TRUE,FALSE),FALSE)

No that does not matter TRUE means Match/Good/found,FALSE means Unmatch or difference.It is self understood.And hence no explanation is required in this case.With Rgds to difference extraction I was refering difference i.e FALSE/difference/Not found ,etc whatever name you may call both I require in column D & H .

you did in only one column but I was prefering in both column which Bhoopatiraj did. Anyhow thanx for your help.

[paradise2sr]

Hi, Ref the attachment

Thanx dude, you did what I required and expected.My problem is now solved.

[Punnam]

paradise2sr

So what solution provided by me & Nfsales Post 12 & 13 .

Punnam

[paradise2sr]

post # 11 result.

[Punnam]

paradise2sr

Post 12 is not giving you desire result ?

Punnam

[paradise2sr]

Thanx to all forum contributors who has directly/indirectly helped me.Now I have also the same case but in slightly different manned with minimal change in the existing data.

Now I have added data in column A and slight change in column B,C,F in the existing data.Now First data should match with the column now of A+B (Numerical part only)+C with that of second data of column F (take now A/0480,L/0502,...) +G data.And finding same as that was done in Post # 11.The same calculation of difference in column D (Reco with 2nd) and column H (Reco with 1st)

Pls find the revised attached data with addition and alteration.

With Best Rgds

[Punnam]

either mark the thread as unsolved or start a new thread

Punnam

[paradise2sr]

Bhupatiraj was much more effective.I have again posted a new post posted in post #19 same case with minimal change.Kindly I require all of forum contributor to help me in this final case which I have posted in Post #19.

Hope for the best.

[samba_ravi]

See the attached file

[paradise2sr]

I have made this thread for a moment again as unsolved.A little bit more case is related with case which I have mentioned in post #19.Kindly refer the post.

[paradise2sr]

See the attached file

I have seen your file.As this is a new case,here you have to match A column also which you have not taken in your formula.Let me explain again for this case.
A3+B3+C3 = F5+G5
see how,
A3=L (new case) i.e A3+B3 matches with F5 = L/1112 (should now be taken,this is a new case)
B3=1112 (should be taken same as old case)
C3=10144 i.e C3 matches with G5 same as old case

so now,
A3,A4,A5,....... has different kinds of data i.e L,M,A to match with F column L/.......,M/....... & A/......

Hope now I am now able to make understand.

With Best Rgds

[samba_ravi]

D3=IFERROR(IF(VLOOKUP("Invoice #: "&A3&"/"&RIGHT(B3,LEN(B3)-3),F$3:G$11,2,0)=C3,"","False"),"False")
H3=IF(SUM(INDEX(("Invoice #: "&$A$3:$A$11&"/"&RIGHT($B$3:$B$11,LEN($B$3:$B$11)-3)=F3)*($C$3:$C$11),0))=G3,"","False")

[paradise2sr]

D3=IFERROR(IF(VLOOKUP("Invoice #: "&A3&"/"&RIGHT(B3,LEN(B3)-3),F$3:G$11,2,0)=C3,"","False"),"False")
H3=IF(SUM(INDEX(("Invoice #: "&$A$3:$A$11&"/"&RIGHT($B$3:$B$11,LEN($B$3:$B$11)-3)=F3)*($C$3:$C$11),0))=G3,"","False")

Can you reference in "Invoice #: " in formula instead of manually mentioning in formula.Also the formula mentioned in H3,I have little problem in understanding,can you use either vlookup or some other formula as you did in D3 which was short,sweet and easily undertandable.

Hope you will be able to do it.

With Best Rgds,

[samba_ravi]

D3=IF(SUMPRODUCT(($A3&"/"&RIGHT($B3,LEN($B$3)-3)=RIGHT($F$3:$F$11,LEN($F$3:$F$11)-FIND(" #: ",$F$3:$F$11)-3))*($G$3:$G$11))=C3,"","False")
H3=IF(SUMPRODUCT(($A$3:$A$11&"/"&RIGHT($B$3:$B$11,LEN($B$3:$B$11)-3)=RIGHT($F3,LEN($F3)-FIND(" #: ",$F3)-3))*($C$3:$C$11))=G3,"","False")

[paradise2sr]

It seems that my problem is now solved.Thanx a lot.

[samba_ravi]

thanks for your feedback