Solved: Conditional Output Where Names are the Same

[Sisu]

Hello again,

I am having difficulty with this challenging excel problem and thought perhaps someone may have a better view this solution. Some time ago I received help from here for the topic "conditional output to a formatted spreadsheet". I cannot thank the forum enough for its help on this but thought some may enjoy this test.

In the workbook I am trying to make a macro that "searches" another input sheet's name data (InputInvoice) and compares it to its concatenated output format (Output) where it then inserts one of either two words in a column (Paid or Unpaid) if the column on the other sheet for that name (InputInvoice) has at least one entry that is => 200. This macro will be run after I run the other macro to format the entries that already exist.

Further Description (corresponding to attached sheet):
If name on InputInvoice is the same as the concatenated version on Output,
And if Amount.InputInvoice is => 200 at least once,
Then enter "Paid" in Output.COLUMN(Z)
Where the concatenated form of Output.First+Middle and Output.Last+Suffix is the same as InputInvoice.First,Middle and InputInvoice.Last,Suffix
Else Output.COLUMN(Z) is "Unpaid";

**Note that duplicate payments can exist for each person but so long as one is greater than 200 then it is considered paid in the output.

I attached a sheet showing the desired output and examples of the inputs. If anyone would like to give it a try I would be very thankful. I will try to work on it as well and read about methods that could do this.

Thank You,
Sisu

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const formulaBase As String = _
"MATCH(1,(InputMain!A<in>=InputInvoice!A1:A<against>)*" & _
"(InputMain!B<in>=InputInvoice!B1:B<against>)*" & _
"(InputMain!C<in>=InputInvoice!C1:C<against>)*(" & _
"InputMain!D<in>=InputInvoice!D1<against>),0)"
Dim formulaMatch As String
Dim rowMatch As Long
Dim shMain As Worksheet
Dim shInvoice As Worksheet
Dim shOutput As Worksheet
Dim Lastrow As Long
Dim Lastrow2 As Long
Dim Nextrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set shMain = Worksheets("InputMain")
Set shInvoice = Worksheets("InputInvoice")
Set shOutput = Worksheets("Output")
With shInvoice

Lastrow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
formulaMatch = Replace(formulaBase, "<against>", Lastrow2)
End With
With shMain

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Nextrow = 1
For i = 3 To Lastrow 'Lastrow to 1 Step -1

Debug.Assert i <> 10
rowMatch = 0
On Error Resume Next
rowMatch = Application.Evaluate(Replace(formulaMatch, "<in>", i))
On Error GoTo 0

Nextrow = Nextrow + 1
shOutput.Cells(Nextrow, "A").Value = .Cells(i, "C").Value2 _
& IIf(.Cells(i, "D").Value2 <> "", ", ", "") _
& .Cells(i, "D").Value2
shOutput.Cells(Nextrow, "B").Value = .Cells(i, "A").Value2 _
& IIf(.Cells(i, "B").Value2 <> "", ", ", "") _
& .Cells(i, "B").Value2
If rowMatch > 0 Then

If shInvoice.Cells(rowMatch, "G").Value >= 200 Then

shOutput.Cells(Nextrow, "Z").Value2 = "Paid"
Else

shOutput.Cells(Nextrow, "Z").Value2 = "Unpaid"
End If
Else

shOutput.Cells(Nextrow, "Z").Value2 = "Unpaid"
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[Sisu]

This looks like it will work!

Thank you very much xld! I wish you the best for everything you do.

-Sisu