Compare: not so easy as it seems

[Pitbull_Raven]

Hi. My first post had a title that didn't quite do it justice and probably some ppl dismissed it as easy because of that. So I started this thread instead. As stated in previous thread, already tried some other suggests but they didn't work out. Any help would be appreciated and I can provide sample sheets if needed.

I need help with an issue that I have to deal with daily and that is a huge pain. I work with SAP and I can convert the output to tables that I can use in Excel. Daily, I have to export 2 tables. The first shows me all client orders and respective positions that have to be shipped that day besides other info that doesn't matter. Example as follows:
100001 10 other data other data
100002 10 other data other data
100002 20 other data other data

The second shows me which orders and positions have already been released for production as follows:
100001 10
100001 20
100002 10
100003 20

What I need is to compare the data in sheet 2 to the data in sheet 1 and highlight the orders in sheet 1, that already have a released production order in sheet 2, by putting a 1 in the last column. Example:

100001 10 other data other data 1
100002 10 other data other data 1
100002 20 other data other data

Is this possible?
<!-- / message -->

 

[njimack]

You mean like this?
=SUMPRODUCT(--(Sheet2!$A$2:$A$5=A2),--(Sheet2!$B$2:$B$5=B2))

 

[Pitbull_Raven]

Unfortunatly that didn't work. If i try to copy that formula it shows up as text. Seems to be some error in the formula.

 

[njimack]

What do you mean it shows up as text? Make sure the cell is formatted as General, not Text.

 

[Pitbull_Raven]

What do you mean it shows up as text? Make sure the cell is formatted as General, not Text.

It is. I can type in other formulas and they work, but if I copy / past or type in the formula you provided, nothing happens after I hit enter at the end. It just displays the formula. in the cell. As if it's not a valid or well formed formula.

 

[njimack]

If the formula was invalid, you would receive an error message. Try using the formula wizard to enter the formula I provided.

It is. I can type in other formulas and they work, but if I copy / past or type in the formula you provided, nothing happens after I hit enter at the end. It just displays the formula. in the cell. As if it's not a valid or well formed formula.

 

[wraith972]

It sounds like you may have copied it and pasted it in the cell. Try pasting it in the formula bar.

 

[Pitbull_Raven]

If the formula was invalid, you would receive an error message. Try using the formula wizard to enter the formula I provided.

It sounds like you may have copied it and pasted it in the cell. Try pasting it in the formula bar.

I have tried both suggestions and for some reason it still won't work. I took extra care to not paste it this time and rather copied it and double-checked to make sure I copied it correctly. I even checked if I was missing a space or something. Tried in different cells too. But the wierd thing is that usually when you type in a formula the syntax is displayed while you're typing. For this formula, nothing is showing up.