Hi,
I've got a schedule of orders and subsequent invoices raised and would like to establish which invoices had original related orders.
I've attached an example of what i'm working with and included some additional narrative in comments boxes. I've attempted the first formula myself which does the job but i'm not sure it's the most efficient? i'd be grateful if someone could point out an alternative/better solution?
If you have any queries just give me a shout.
Thanks in advance,
Snook
Last edited by The_Snook; 01-10-2014 at 08:53 AM.
I don't see anything wrong with how you did it. I guess one alternative would be to use an array formula, such as:
=IF(SUM(IF(B3&C3&D3=Orders!B3:B20&Orders!C3:C20&Orders!D3:D20,1,0))>0,"True","False").
I'd guess the quickest calculation wise would probably to have helper columns with the concatenated values of columns B, C and D and use a countif or something with the helper columns.
F3:
=COUNTIFS(Orders!$B$3:$B$20,Invoice!B3,Orders!$C$3:$C$20,Invoice!C3,Orders!$D$3:$D$20,D3)>0
H3
(confirm Contrl+shift+enter
=IFERROR(MATCH(1,(Orders!$B$3:$B$20=Invoice!B3)*(Orders!$C$3:$C$20=Invoice!C3)*(Orders!$D$3:$D$20=D3),0),"")
J3
=COUNTIFS(Orders!$B$3:$B$20,Invoice!B3,Orders!$C$3:$C$20,Invoice!C3,Orders!$D$3:$D$20,D3)
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Hi, try these formulas
Formula:
F3{=IF(ISNA(MATCH(B3&C3&D3,Orders!B$3:B$20&Orders!C$3:C$20&Orders!D$3:D$20,0)),"N","Y")}
Formula:H3{=IFERROR(MATCH(B3&C3&D3,Orders!B$3:B$20&Orders!C$3:C$20&Orders!D$3:D$20,0),0)}
Formula:J3=IF(SUMPRODUCT(--(Orders!B$3:B$20&Orders!C$3:C$20&Orders!D$3:D$20=B3&C3&D3))>0,"Y","N")
and copy the formulas down...
ps: first two of the above are array formulas, i.e. press ctrl+shift+enter instead of just enter
_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved
Alvin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks