Hi Charlesdh thanks again for helping me
, thanks for the new version, the code does the correct concatenation but actually without the
index/match the code cannot match the exact concatenation with the correct row in sht1
My comments:
- the index/match formula in sht1-H is meant to match the right combination of vendor-plant-material between the rows in sht1 and sht2, without this formula the concatenation of sht2 is not matched with the correct correspondent row in sht1. This is crucial as the order of the rows is random and the same material can have different plants and different vendors (that is why sort A-Z the materials wouldn't work)
- in my opinion the easiest way would be to have always all the rows in sht2 concatenated in sht2-G, and then index/match formula in sht1-H looking for the exact combination Vendor-Plant-Material and reporting the concatenation when the exact match is found. That way, no matter the order or the quantity of the materials, we will always have at the end of the check the correct concatenation reported in the correct row in sht1 for the comparison.
- weirdly the function at the end of the code that makes the check " currency sensitive doesn't work anymore with both version of you codes :
Code:
Function CurAdj(WkVal As Range) As String
Dim WkF As String
WkF = WkVal.NumberFormat
CurAdj = IIf(InStr(1, WkF, "$$") <> 0, "$", IIf(InStr(1, WkF, "$¤") <> 0, "¤", "")) & WkVal
End Function