Target prices

[Alexon2008]

Hello!
I have a spreadsheet with flg style:
Column A --- Sales Locations 1,2,3,4,5 etc.
Column B ---- Item number (6 digits)
Column C ---- Prices

I want to macro to find identical item number at different sales locations and then substract prices from our targets prices which are prices at sales location 3 or 5.

As prices for sale locations 3 and 5 are our target prices I want to compare all other sales locations prices to those.

For instance, I would like that macro find item no 450560 at the factory 11 andthen identical item no. at factory 3 or 5 and then substract prices fra sales location 11 from sales location 3 or 5. Prices wil be eather on places 3 or places 5.

[Bob Phillips]

Post a workbook with example data and an example result.

[Alexon2008]

Here is:
Sales location Item no. Price Difference (result) Formula
14 256000 309,84 -95,12 =C4-C2
9 256000 499,05 -284,33 =C4-C3
3 256000 214,72
6 251000 306,95 -31,75 =C8-C5
14 251000 207,38 67,82 =C8-C6
9 251000 188,9 86,3 =C8-C7
5 251000 275,2
9 159999 305,25 17,77 =C11-C9
16 159999 224,55 98,47 =C11-C10
3 159999 323,02

[Bob Phillips]

Add this array formila to D2 and copy down to D10

=IF(COUNTIF($B3:$B$11,$B2),INDEX($C3:$C$11,MAX(IF($B3:$B$11=$B2,ROW($B3:$B$ 11)-ROW($B3)+1)))-$C2,"")

[Alexon2008]

It works only in next to last cell. In last cell return 0 which is not right. And in rest of cells I just got:#value!

[Bob Phillips]

Did you array-enter it?

I did say don't put it in row 11, that will avoid the 0.

[Alexon2008]

It works when entered as array but don't solve my problem. it's not dynamic. My dataset differ from time to how long it is and is not sorted as in eksemple I posted. Can you help me?

[Bob Phillips]

Of course its dynamic, just change the 11 to some point beyond your largest dataset.

What do you mean by not sorted?

[Alexon2008]

When I copy formula after celle D10 it doesn't work:
Sales location vare nr Price Result
14 256000 309,84 -95,12
9 256000 499,05 -284,33
3 256000 214,72
6 251000 306,95 -31,75
14 251000 207,38 67,82
9 251000 188,9 86,3
5 251000 275,2
9 159999 305,25 17,77
16 159999 224,55 98,47
3 159999 323,02 0
15 200000 35 288,02
5 200000 36 287,02
3 355555 40 283,02
15 355555 41 282,02