Intercompanies inventory control table

[Uolsiec]

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

[:) Sixthsense :)]

Please make your query more descriptive with a sample file

[Glenn Kennedy]

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

[Uolsiec]

Thanks for your opinion.
Sample file is added.
The question is amended.

[mngrshoaib]

Dear,
Please respond.....

[Uolsiec]

Done. Sample added.

Please make your query more descriptive with a sample file

[Uolsiec]

Done. Example added.

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

[Uolsiec]

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.

Dear,
Please respond.....

[bebo021999]

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.

[Uolsiec]

This is my raw data. It is inputted by various departments base on their information and I have to confirm their input is correct.
So it's no other methods to tackle this problem?

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.

[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.

[Uolsiec]

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.

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.

[Uolsiec]

Any body can help?

[Uolsiec]

May I just generate 1 time VBA to investigate all the items which are not match? And then, past all the item on the new worksheet. Is it better than just use a formula to run it because my pc have to handle over 50,000 formula as once? Please help.

[ExcelBin]

I have been playing with Excel tables and pivot tables and your thread was a good chance to practice. I thought I would pass it along. If you have questions, please let me know. Thank you!

[Uolsiec]

I am not sure I understand you mentioned in the Pivot.
May I know how to transfer the form from my sample to your pivot?
Another issue is I can't imagine how you label the Transfer# because if I can mark the Transfer# for each item. I think the problem can be solved.
Have a nice day.

I have been playing with Excel tables and pivot tables and your thread was a good chance to practice. I thought I would pass it along. If you have questions, please let me know. Thank you!

[ExcelBin]

You need to open the file in Excel 2013. Your sample data has been entered and shows the errors in the data such as the 3 and 1 being positive rather than negative for the transfer out. The Transfer# column is optional but is helpful.

[Uolsiec]

Hi All,

The following would be the real case I am handling.
Please kindly suggest how to handle by Pivot.

Thanks.

Sample.xlsx

I have been playing with Excel tables and pivot tables and your thread was a good chance to practice. I thought I would pass it along. If you have questions, please let me know. Thank you!