Excel Excel 2007/2010 Duplicates Entries in Four Columns of Data

[Lynda Ritchie]

Hi,

I was hoping some one could help me with a formula I need. I have to find which customers (ID and Value) still owe the same amount in Col D

Column A2 to A1491 & Column B2 to B1491 & Column C C2 to C14091 and Column D D2 to D14091

Column A Column B Column C Column D
Customer ID Value Customer ID Value
1234 100 1231 100
1244 50 1234 100
1245 100 1240 50
1244 25
1245 100
Eg show Customer ID 1234 and 1245 still owe the same amount today.

I can show which client ID's are the same using =countif(a$2:a$1491,c2) unsure how to finish.

Thank you very much

 

[alow]

I just want to clarify before I go off typing a formula. Do you want to match the values in Columns B and D based on the IDs in Columns A and C and show where they are still the same per the ID?

 

[Lynda Ritchie]

Thank you for replying, I really appreciated it.

My formula I currenly have in e2 is

=if(countif(a$2:a$8,c2)*AND(b$2:B$8,d2),"Yes","No") where Client ID in Column A and C 1446 is different it is not working properly. I tried to use match, index. I only want the formula to show Yes for the items marked in red in col C and col D. I would really appreciate some help.

Column A Column B Column C Column D
Client IdAmountClient IdAmount 1234100.001231300.00 1335100.001232300.001446300.001233300.0014457500.001234100.0015558200.001441300.0020235250.001442300.0024807100.001445300.001446250.0014452200.0014453200.0014458200.0015556200.0015557200.0015558200.0020235250.0025800200.0024807100.0024810100.00

 

[alow]

I'm still not quite clear on what it is you want. Do you want a YES if there is a matching pair in columns A&B that matches an ID and Value in C&D? The data you gave didn't come in with a format that I can really see what you're looking for. A little tip that might help is that you're using AND incorrectly. With Excel, AND works like any other formula. You want to do AND(Argument1, Argument2, Argument3, etc). The AND formula will return TRUE if ALL arguments are true and a FALSE if ANY arguments are false. Hope this helps some.

 

[Lynda Ritchie]

Hi Again,

I don't think my example came out as expected, I wanted to see if the same customers still owe the same amount in August 2012 as they did back in May 2012, to show which clients have not made any payments. I did actually forget to email the worksheet home to myself there are 2941 rows of Col a and B and 14091 rows of Col C and D. This is a small example. I want to see the same ones, the red entry only. Customre no 1400 has paid some of their debt that is ok, I only want to show the customers tht have not made any payments.

Thank you so much.

Col A Col B Col C Col D
Oustanding Debts
Aug 2012 May 2012
ID $ ID $
1234 $100 1231 $250
1400 $100 1234 $100
1500 $100 1400 $200

 

[alow]

Give the following a try:

=IF(VLOOKUP(C2,$C$2:$D$14091,2,0)=INDIRECT("R"&MATCH(C2,$A$1:$A$2941,0)&"C2",FALSE),"Yes","No")

Good luck!

 

[Lynda Ritchie]

Thank you I would not have come up with that formula, Vlookup was an option but I did not think it could look up across two columns to find the exact match over another two columns. Thank for for helping me, I think it would of been easier to email you the spreadsheet it did not seem to post as I typed. Thanks Again
Kind Regards
Lynda

 

[alow]

Glad I could help!