# Compare: not so easy as it seems

#### Pitbull_Raven

##### New Member
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 -->

Last edited:

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### njimack

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

#### Pitbull_Raven

##### New Member
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

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

#### Pitbull_Raven

##### New Member
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

##### Well-known Member
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

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

#### Pitbull_Raven

##### New Member
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.

Replies
0
Views
118
Replies
10
Views
402
Replies
15
Views
858
Replies
21
Views
387
Replies
7
Views
416

1,190,894
Messages
5,983,430
Members
439,843
Latest member
PlanetFitness

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back