finding/and transferring data..stuck on last part

[demodren]

Hi all, thanks everyone for their help.. I really appreciate it! I am working to finish my project and currently stuck on the last part. I am looking for some advice how to collect and pull the data..Any advice or feedback would be very appreciated.. I wish there was a way to upload a workbook would be little easier to explain.. but please let me know if I am unclear on below..thanks so much.. a

-Dan

Here is a summary, I have 3 tabs:
1) Summary Information (this is main tab where I need to summarize my data)
2) Current Month Sales(Pull data daily..data for current month only Sales and on how many units are left)
3) Previous Months Sales (This gets updated at end of month..contains up to current month data all the sales in past 2 years)

**Both Current Month Sales and Previous Month Sales are identified by 2 identifiers: Account Number(in total 5) and unique ID(I can have 5 same IDs across 5 Account Numbers though)

In Summary Information tab I need to perform 2 steps:

Step 1 (I was able to put it together) - copy/paste 4 columns:
(A) account number
(B) ID
(C) Units
(D) Current Month Sales

then I do lookup function in my "Previous Month Sales" by account number and ID to pull 5th column:
(E) Previous Months Sales

Dim LastRow As Long

LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Windows("Sales_Information.xls").Activate
    Sheets("Summary").Select
    Range("F3").Select
    Range("F3:F" & LastRow).Formula = "=LOOKUP(9^99,IF({1,0},0,LOOKUP(2,1/(A3=prior_months_sales!A$3:A$100)/(C3=prior_months_sales!B$3:B$100),prior_months_sales!E$3:E$100)))"
    Range("F3").Select
    
    Range("F3:F" & LastRow).Select
    Selection.Copy
    Range("F3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("F3").Select

[/CODE]

Finally I, TOTAL all the sales in the 6th column:
(F) TOTAL (here I just sum up Current month Sales with Previous Months Sales)

Step 2 (stuck on this part) - find any remaining data in Previous Month Sales and copy all the data to the last row on Summary Information tab(last row will always change as it depends how many items there are in Current Month Sales) Basically this will give me full scale picture of any units that are remaining to be sold with all the previous sales.. I am stuck on this, because I was thinking of doing a reverse LOOKUP and finding any #N/As then doing a filter to select all #N/As and then paste those columns in the Summary tab.. seems little too long and choppy.. I just thought there might be an easier way



My columns in Summary Information tab.

Account Number | ID | Units | Current Month Sales | Prior Month Sales | TOTAL