In addition to a VBA solution (and I would probably use a dictionary with the SalesPerson as the key, and a class object containing the relevant information), I would suggest also looking at Power Query (available in Excel 2010+). "Unit Without DC Code", "Date", "Country", "Grade") RngOut.Resize(, 8) = Array("SalesPerson", "Company", "Product", "Unit With DC Code", _ Set rngOut = wsOut.Range(dFirstCellAddress) Key = Data(r, 1) & vbTab & Data(r, 2) & vbTab & Data(r, 3)ĭim k As Variant, wsOut As Worksheet, rngOut As Range ' composite key SalesPerson,Company,Product ' Write unique values from Data Array to Unique Dictionary.ĭim dict As Object: Set dict = CreateObject("Scripting.Dictionary")ĭim arr(3) As Variant, Key As String, tmp As Variant With wb.Worksheets(sName).Range(sFirstCellAddress) ' Write values from Source Range to Data Array. Option Explicitĭim wb As Workbook: Set wb = ThisWorkbook I have concatenated them with tab character so they can be separated later for the results sheet. You need to build the dictionary key from the first 3 fields SalesPerson,Company,Product.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |