Compare two sets of data and display results

[BrutalDawg]

Here is what I need to do. I will have two sets of data like below.

Item Number	Our Quantity		Item Number	Requested Quantity
Item1	100		Item1	50
Item2	200		Item2	50
Item3	150		Item3	200
Item4	300		Item4	150
Item5	400		Item5	500
			Item 6	50

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

What I am trying to develop is a shortage report. So I would need excel to determine the value on hand vs requested quantity and display what I will be short on in the coming weeks. Also, there are times when they will request product we have 0 quantity on hand so it will not populate in our inventory list. I would like it to point that out too. In theory what I need it to do, is take the quantity of the specific item we have, subtract the quantity requested and show the difference. We have thousands of unique items on hand, so doing this manually takes several hours. Does anyone know a way to compare the data like this?

 

[Tubal]

Hello BD,

Thanks for a good description of your problem!

Put your Item Number and Our Quantity in Columns A and B.
Skip column C
Put your requested items and quantity in column D and E
Select your data (not the header in row 1) in Columns A and B and give it a Named Range of nr_OnHand
In column F, starting in row 2 enter

=IFERROR(VLOOKUP(D2,nr_OnHand,2,FALSE),0)-E2

Copy the equation down to the last item in column E

If you don't now about named ranges, then replace nr_OnHand with $A$2:$B$6

If you really want to make it shine, search on "dynamic named range"

Tubal

 

[BrutalDawg]

Thank you for the support Tubal,

I do not think it is acting like I need it to act. See below a little piece of what happen.

Item Number	Quantity		Item number	Quantity
64775	645		64775	45	-45
138380	165600		100812	100	-100
149036	14		138380	170,350	-170350
209957	50		149036	14	-14
209961	50		209957	50	-50
209962	50		209961	150	-150
209963	100		209962	100	-100
1935510	1		209963	50	-50

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

As you see on the left is our quantity on hand, on the right is the demand. Far right is "F"

So for example first Item we have 645, they request 45 it should show 600. Did i do something wrong? Ideally i need it to search each item and its Quantity on hand, find the same item number and requested quantity and subtract. It would be very ideal if it could highlight all negatives (shortages) in red.

Can you try and help me solve this issue? I may have entered something incorrectly, I am not sure.

Below is link to download the whole excel file if that would help more.

http://www.filedropper.com/comparetwosetsofdataexample

Thanks for support again!

 

[Simon4s]

Thank you for the support Tubal,

I do not think it is acting like I need it to act. See below a little piece of what happen.

Item Number	Quantity		Item number	Quantity
64775	645		64775	45	-45
138380	165600		100812	100	-100
149036	14		138380	170,350	-170350
209957	50		149036	14	-14
209961	50		209957	50	-50
209962	50		209961	150	-150
209963	100		209962	100	-100
1935510	1		209963	50	-50

<tbody>
</tbody>

As you see on the left is our quantity on hand, on the right is the demand. Far right is "F"

So for example first Item we have 645, they request 45 it should show 600. Did i do something wrong? Ideally i need it to search each item and its Quantity on hand, find the same item number and requested quantity and subtract. It would be very ideal if it could highlight all negatives (shortages) in red.

Can you try and help me solve this issue? I may have entered something incorrectly, I am not sure.

Below is link to download the whole excel file if that would help more.

http://www.filedropper.com/comparetwosetsofdataexample

Thanks for support again!

Try this:
=VLOOKUP(D2,$A$2:$E$1069,2,FALSE),0)-e2

 

[BrutalDawg]

This keeps giving me a formula error. Keep in mind, that generally we will have around 1000 item numbers, but the demand is only for around 100-150 at a time. The data I am using as an example for demand right now is an old stock report.

Perhaps we can go about it a different way, if community is willing to help as I am a bit of a noob at formulas. I think a script could be ran if I keep the content in different sheets. Sheet 1 could be the item and quantity sheet (A&B) ; sheet 2 be the same for demand, item and demand (A&B). Run a multiple finder that finds items from listed in sheet 2, and subtracts sheet 1 B from sheet 2 B and display results in sheet 3.

I have a script similar, but it is just a simple list of parts in sheet 2, that searches sheet one for same item number and transfers all the information to sheet three.

Option Explicit
Sub MultipleFinder()
Dim srchLen, myString, nxtRw As Integer
Dim firstAddress As String
Dim c As Range
'Clear Sheet 3 and Copt Column Headings from Sheet 1
Sheets(3).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(3).Rows(1)
'Determine length of Search Criteria Column from Sheet2
srchLen = Sheets (2).Range("A" & Rows.Count).EndxlUp).Row
'Loop through list in Sheet2, Column A. As each value is
'found in Sheet1, Column A, copy it to the next row in Sheet3
With Sheets(1).Columns(A")
For myString = 2 To srchLen
Set c = .Find(Sheets(2).Range("A" & myString), lookat=xlWhole)
If Not c Is Nothing Then
firstAdress = c.Address
Do
nxtRw = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row + 1
c.EntireRow.Copy Destination :=Sheets(3).Range("A' & nxtRw)
Set c = .FindNext(c)
Loop While Not C Is Nothing And c.Address <> firstAddress
End If
Next
End With
End Sub


Not sure if that would help anyone. I would also need to know if an item listed on sheet two is not on sheet one if that is possible. Any help would be greatly appreciated, I need this desperately