Compiling Values

[zoya25]

Hello,

So I am trying to track the status of multiple work products on a day to day basis using Excel Macros. I am trying to run a loop checking each row in sheet 1 against sheet 2. And if the value doesn't exist in sheet 2 then inputting it in sheet 2 towards the end of the records without erasing any existing records. Essentially compiling the values. I want to do this only for 5 columns. Would really appreciate your help.

 

[Joe4]

Welcome to the Board!

When you are relating two different tables of data like this, what you are really describing is a relational database.
As such, it is usually much easier to use a relational database program, like Microsoft Access, instead of Excel (which is NOT a relational database program).
Do you have Microsoft Access?

Basically, what you would be doing is an Unmatched Query from one table to the other, when joining them on all 5 fields.

 

[zoya25]

I was actually able to do this in Macros using a Loop with if statement embedded in it

Sub process_data()
Set sh = Sheet1
Set res = Sheet14
On Error Resume Next


For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
isMatch = ""
isMatch = WorksheetFunction.Match(sh.Range("A" & i), res.Range("B:B"), 0)
If Len(isMatch) > 0 Then
res.Range("D" & isMatch) = sh.Range("F" & i)
Else
nRow = res.Range("A" & Rows.Count).End(xlUp).Row + 1
res.Range("B" & nRow) = sh.Range("A" & i)
res.Range("A" & nRow) = sh.Range("I" & i)
res.Range("C" & nRow) = sh.Range("B" & i)
res.Range("D" & nRow) = sh.Range("F" & i)
End If
nxti:
Next i


res.Activate


End Sub

 

[Joe4]

Glad you figured something out.

I never said you couldn't use Excel to do it, I was just asking if you had Access at your disposal. If so, then there is no sense in recreating the wheel, when you can use a tool that was designed for precisely that purpose.