Compare two sets of data and display results

BrutalDawg

New Member
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?

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

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.

http://www.filedropper.com/comparetwosetsofdataexample

Thanks for support again!

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.

http://www.filedropper.com/comparetwosetsofdataexample

Thanks for support again!

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

Last edited:
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 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
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)
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

Replies
12
Views
2K
Replies
9
Views
2K
Replies
6
Views
487
Replies
2
Views
355
Replies
2
Views
505

1,207,089
Messages
6,076,518
Members
446,211
Latest member
b306750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back