Compare data from two sheets and export to sheet 3

BrutalDawg

New Member
Joined
Jun 10, 2015
Messages
41
Hello,

Here is what I would like to do. I have Sheet1 with a list of item numbers in column A and quantity in column B. On sheet2 the list will have Destination(A) Item Number(B) Due Date (c) and quantity (D). Sheet 1 is our quantity we have, while sheet2 is the demand schedule. I need a macro, that will compare item number and quantity from sheet 1 -> sheet 2 -> then display results on sheet3. My end goal is to be able to show current inventory compared to the two or 3 week demand from sheet2 for each item number. Any help would be awesome!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
The narrative of he post is somewhat vague with regard to the expected results, but you can try this.
Code:
Sub comparencopy()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Set sh3 = Sheets(3) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    With sh3
        sh1.Range("A1:B1").Copy sh3.Range("A1")
        .Range("C1") = sh2.Range("C1").Value
    End With
    For Each c In sh1.Range("A2:A" & lr)
        Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                With sh3
                    .Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
                    .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = c.Offset(0, 1).Value
                    .Cells(Rows.Count, 1).End(xlUp).Offset(0, 2) = fn.Offset(0, 1).Value
                End With
            End If
    Next
End Sub
 
Upvote 0
Whiz,

This is a very nice start. I am sorry I was vague in my expected results. What this macro is doing, is search the items from sheet2 and sending results to sheet 3. What I need is for the macro to look for the items from sheet2, in sheet1, then subtract the quantity from sheet2 from the quantity in sheet1. This will show us a shortage or not. Also, it would be ideal for any (-) numbers (meaning shortage) to be in red, and its also important that if an item in sheet2 is not found in sheet1 it show on sheet3 as the negative. I know this is confusing so below is a little example

Item NumberOur QuantityItem NumberRequested Quantity
Item1100Item150
Item2200Item250
Item3150Item3200
Item4300Item4150
Item5400Item5500
Item 650



<tbody>
</tbody>
What I would want sheet 3 to say would be

Item1 50
Item2 150
Item3 -50
Item4 150
Item5 -100
Item6 -50


Does this help clarify what I would like the results to look like?

Thanks for any and all help!
 
Upvote 0
So long as there are no duplicate entries in either sheet, this will work fine. If there are duplicates then your results will be erroneous.
Code:
Sub comparencopy()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, c As Range, fn As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Set sh3 = Sheets(3) 'Edit sheet name
lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row
    With sh3
        sh1.Range("A1:B1").Copy sh3.Range("A1")
    End With
    For Each c In sh2.Range("B2:B" & lr)
        Set fn = sh1.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                With sh3
                    .Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
                    .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = fn.Offset(0, 1).Value - c.Offset(0, 1).Value
                    If fn.Offset(0, 1).Value - c.Offset(0, 1).Value > 0 Then
                        .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Font.ColorIndex = 10
                    Else
                        .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Font.ColorIndex = 3
                    End If
                End With
            End If
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top