Comparison of two Columns

[Moazam Ali]

Hi Everyone,

I am making a database for inventory management. Everyday a certain quantity of material is assigned to different machines and record is kept for each machine. At the end of the day some of the unused material is being reversed to the warehouse.
I am having difficulty in figuring out to determine the exact material which is used for a certain machine.
Can you guys tell me which function to be used and how ?

[alansidman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Moazam Ali]

Here you go Alan.

[JeteMc]

There are several discrepancies with the 'after' sheet that could be cleared up so that help can be offered.
1) There are 10 bar codes under Materials issued but only 8 combined under materials used and materials reversed although the equation on the sheet reads "Material Used = Material Issued - Material Reversed"
2) A bar code that are listed once in materials issued and shows up under materials reversed and materials used (I32F4QW000450087641703038).
3) Material bar code Q00009600635067501 is issued to machines 1,2 and 4 but listed as used by machine 5.
4) Material bar code Q00009600635067501 is issued three times and reversed once. How is it determined the machine from which the material was reversed?
5) The material bar codes listed as issued to machine 5 are neither listed as reversed nor used.

[Moazam Ali]

Dear Jete,
This was a sample file just to give you guys an idea what I am trying to do.
However Debugged file is attached below.

[JeteMc]

The following formula will give the values for column D:

=IFERROR(INDEX(A$4:A$14,AGGREGATE(15,6,(ROW($A$1:$A$10))/ISERROR(MATCH(A$4:A$14,C$4:C$14,0)),ROW(A1))),"")

The following formula will give the values for column E:

=IFERROR(INDEX(B$4:B$14,MATCH(D4,A$4:A$14,0)),"")

Let me know if you have any questions.

[Moazam Ali]

Thanks a lot Jete
But I need a generic formula for any length of column because the barcodes will be adding day by day and column length will increase accordingly.
I hope you can help me.

[JeteMc]

Simplest way that I can think of would be to increase the second row number reference to be that of the last possible row, which for version 2010 is 1048576.
The formula for column D would then read:

=IFERROR(INDEX(A$4:A$1048576,AGGREGATE(15,6,(ROW($A:$A))/ISERROR(MATCH(A$4:A$1048576,C$4:C$1048576,0)),ROW(A1))),"")

The formula for column E would then read:

=IFERROR(INDEX(B$4:B$1048576,MATCH(D4,A$4:A$1048576,0)),"")

In the attached file I added conditional formatting to column D so that any zeros would have a fount color of white and not be visible.
Let me know if you have any questions.

[Moazam Ali]

Thanks a lot Jete.
Just one more thing, Can you please explain me both f these codes for my better understanding and future use ?

[JeteMc]

Looking at the formula that populates column D from the inside and working out, it uses instances where there is no match between the values in column C and the values in column A to create a true/false array. This array is then compared to the row numbers in the sheet, generated by ROW($A:$A), which creates an array of numbers and #DIV0 errors. The second argument of AGGREGATE, 6, ignores the errors and the first argument, 15, finds the nth smallest, as determined by the second ROW function, number in the array. That number is passed to the index which will find the bar code in that row of column A.
The formula that populates column E is just an index match where the match finds the bar code and returns the row number where it is found and the index finds the associated machine# in column B.
Looking at the file attached to post #6, which has fewer rows to work with, select D5 and run the Evaluate Formula on the Formula tab to see how this all works.
As to the solution itself, Your Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[Moazam Ali]

One Last thing Jete.
A lot of material from same batch has the same barcode number. This means that same barcode is issued to two different machines.
So If column D has the same barcode twice , Column E only returns the same machine number against the both barcodes.
I have attached the file for better understanding.

[JeteMc]

We can clear up the numbering issue by doing the following.
1) Modify the match argument cell references for the column D formula to absolute as in:

=IFERROR(INDEX(A$4:A$1048576,AGGREGATE(15,6,(ROW($A:$A))/ISERROR(MATCH($A$4:$A$1048576,$C$4:$C$1048576,0)),ROW(A1))),"")

2) Copy the formula over to column E and, with both D4 and E4 selected, double click to copy down.
This does re-raise question #4 from post #4, what if a lot of batch H0003819717101400002363 was reversed, how could it be determined from which machine it came?
Let me know if you have any questions.

[Moazam Ali]

Yes this do re raise that question - "what if a lot of batch with same barcode was reversed, how could it be determined from which machine it came."
What if we introduce another column against * Material reversed" which contain the machine number from which material is reversed.
Can we eliminate that problem ?

I have attached the file to get the better understanding.

[JeteMc]

Try pasting the following formula in E4 then copy down and across:

=IFERROR(INDEX(A$4:A$1048576,AGGREGATE(15,6,(ROW($A:$A))/ISERROR(MATCH($A$4:$A$1048576&$B$4:$B$1048576,$C$4:$C$1048576&$D$4:$D$1048576,0)),ROW(A1))),"")

Note: the processor in my laptop ran for a while before the correct values were displayed in columns E and F.
Let me know if you have any questions.

[Moazam Ali]

Thanks a lot Jete for contributing this much to my work. Your efforts are highly appreciated and apologies for not replying earlier.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.