Finding the matchs in columns A,B,C and gives output in D & E

[shekar goud]

Hi Team,

Could someone please help as I need to get the totals and whether its negative or positive balance.

To be more specific in a workbook in sheet1 I have Client data columns A,B & C in A,B,& C should be match with the companys data in the next sheet.

If this both sheets sheet1 & sheet2 and columns A,B,& C is matched then in column D in both the sheets should be Matched and Column E should be 0 in both the sheets.

And,The problem starts when both the sheets are not matched if the Column A,B,C is not matched in Column D it should show as "NOT Matched " and difference in Column E in both the sheets.It may be positive figure in one sheet and Negative figure in another sheet.

Please advise If any Forumulae which can help me to sort this out or should I need to go for an Macro.

Please find an sample sheet is attahced.

And finally how could I find the person who has opened the macro in a worksheet.

Thanks for all your help.

Thanks & Regards,
Shekar Goud.

[DonkeyOte]

Which version of XL are you using ?
(It would be helpful if you were to specify this in your profile as approaches can vary)

EDIT: Also worth adding

Will the combinations always exist on both sheets ?
If so, will the combinations always appear in the same order on both sheets ?
Will the combinations only ever appear once on any given sheet ?

[shekar goud]

Hi DonkeyOte,

Iam using 2003 version excel.

Yes, The data will be same in all the times.
Yes,combination Will be in same order only.
No,i did'nt understand the last line but it will never reflect any where.


Regards,
Shekar.

[DonkeyOte]

So if the sheets are identical in terms of layout and content, ie Client Data A2:B2 = Company Data A2:B2 etc then why not a simple subtraction ?

Client Data!E2: =C2-'Company Data'!C2
Client Data!D2: =IF(E2,"Not Matched","Matched")
copied down

For the other sheet you can simply invert the E2 value

Company Data!E2: =-'Client Data'!E2
Company Data!D2: ='Client Data'!D2

[shekar goud]

Hi DonkeyOte,

No....This will not work as the Columns A,B,& C would be same but the sequence will change in the rows like sometimes Row1 data in Sheet1 may exist in Row 10 of Sheet2.

We can't simply fix cell refernce to the cell of next sheet.I Meant A1 of Sheet1 to A1 of Sheet2

Hope you are getting my point.


Regards,
Shekar.

[DonkeyOte]

OK. That was what I meant by:

...will the combinations always appear in the same order on both sheets ?

to which you responded

Yes,combination Will be in same order only.

so I'm guessing this was lost in translation and you were referring to A & B whereas I was referring to order of rows.

As is you have a few choices one of which would be:

Please Login or Register  to view this content.

The formulae in D remain as before, the accompanying formulae for Company sheet are as above with sheet references adjusted.

Again this is still based on the assumption that the combination appears once on each sheet (ie always appears on both sheets but never more than once on either...)

[shekar goud]

Hi DonkeyOte,

Iam Sorry I was on leave for couple of days because of that I did'nt responded.You are almost at the point of the solution.

The fact is I need the total of same From and To if it is more than one time for example

France to Dubai is more than twice and any difference in from client sheet to company sheet should be reflected in company sheet column D.

I belive one Index formulae would do the magic.

I Appericate you and thanks for your help.

Regards,
Shekar Goud.

[DonkeyOte]

In truth I can't really understand your last post - it would be a lot easier if you were to post an example that actually highlighted all of your requirements.

[shekar goud]

Hi DonkeyOte,

I just attached an sample sheet now.

In simple words I need the difference of Client sheet Column C to Company data sheet Column C in Company data sheet in column E if col A,B are matched.

I Think you are still in dilemma so please look at the sample sheet.


Regards,
Shekar Goud.

[DonkeyOte]

please be clear... are you saying you want 0 for duplicates or not ? (eg E8 / E9)

EDIT: hang one - so you're saying in fact (I think) you want to check for A & B and C ... isn't this just a repetition of an earlier thread ?

EDIT: EDIT: In fact I'd go so far as to say I have no idea what you want to do here... and no idea how you intend to differentiate between sheets given duplicity of values etc... I think I'm bowing out on this one I'm afraid.
Perhaps someone else will be able to make sense of it coming at if from anew.

[DonkeyOte]

Last ditch effort to try and actually understand the requirements...

Based on your file are you saying:

First instance of France/Dubai on Client should be validated against first instance of France/Dubai on Company? Second instance of France/Dubai on client should be compared to 2nd instance of France / Dubai on Company etc... eg in case of France & Dubai:

Client!D2 vs Company!D2
Client!D8 vs Company!D7
Client!D9 vs Company!D8

??

[shekar goud]

yes exactly.....

[DonkeyOte]

I would suggest doing the following... first add some "keys" to reduce complexity, eg:

Client Data
F2: =A2&":"&B2&":"&1+COUNTIF($F$1:$F1,A2&":"&B2&":*")
copied down

Above repeated for Company Data sheet

Once the above is in place you can use a standard SUMIF approach in E

Client Data
E2: =$C2-SUMIF('Company Data'!$F:$F,$F2,'Company Data'!$C:$C)
copied down

Above repeated for Company Data sheet - revising sheet refs. accordingly

[shekar goud]

Hi DonkeyOte,

Thanks for all your help.

Regards,
Shekar.