Intercompanies inventory control table

Table.PNG

Hi all,
i am preparing a intercompanies inventory transfer table.
Is there anyone can help to teach me how to verify the figure in this table?

Thanks!!!
p.s. of course, my table have much more items than the sample.

For example,
Factory1 gives 2 apples to Factory2.
I deducted 2 in the Factory1, C5.
And then, I added 2 apples to Factory2, D2.
The question is how I can ensure that I entered -2 in C5 and 2 in D2 by 1 simple formula or function in excel?


**********Sample File********
Sample.xlsx

Please make your query more descriptive with a sample file

A screenshot is useless. please attach a sample worksheet, explaining clearly what you mean by "verify the figures in the table"

Done. Sample added.

Originally Posted by :) Sixthsense :)

Please make your query more descriptive with a sample file

Done. Example added.

Originally Posted by Glenn Kennedy

A screenshot is useless. please attach a sample worksheet, explaining clearly what you mean by "verify the figures in the table"

I checked the file you attached. The idea is good but in your table I can't control which party transfer out the apple and which party transfer in the apple.
I have to control the apple is correctly recorded in both parties.

Originally Posted by mngrshoaib

Dear,
Please respond.....

Which is your raw data?

It should be raw table like this:

Deliver ----- Receiver ----- Description ----- Qty
Fact 1---------Fact 2 --------Apple------------2
Fact 1---------Fact 2 --------Orange------------3
Fact 1---------Fact 2 -------Pineapple------------1
Fact 2---------Fact 4 --------Orange------------6
....

Only with that table, it is able to create a matrix.

I apply Conditional formatting to highlight pair of cells those are not equal.

Stay in C5, apply this formula in Conditional formatting box:

=SUMPRODUCT(($A$5:$A$19=$A5)*($B$5:$B$19=C$4)*($C$4:$G$4=$B5)*IFERROR($C$5:$G$19+0,0))<>-C5

Apply to the whole range.

Hi bebo,
Thank you so much! Your method work well!!!
But unfortunately, my pc was malfunction after I had applied the method to my data file. Old PC seek help...

It has more than 50,000 cells have to be process.

Originally Posted by bebo021999

I apply Conditional formatting to highlight pair of cells those are not equal.

Stay in C5, apply this formula in Conditional formatting box:

=SUMPRODUCT(($A$5:$A$19=$A5)*($B$5:$B$19=C$4)*($C$4:$G$4=$B5)*IFERROR($C$5:$G$19+0,0))<>-C5

Apply to the whole range.