title: Excel - Compare tables from different sheets with dynamic column criterias
body: <p>I need to be able to compare tables from two different worksheets/workbooks. These tables will have different number of columns but the user should be able to pick and choose which columns are unique and which columns are quantity columns.</p>
  <p>For examples:
  Table1, the user chooses columns A, B, and D to be unique, and column F to be the quantity column.
  Table2, the user chooses columns B, C, and E to be unique, and column G to be the quantity column.</p>
  <p>The code will then <strong>find</strong> a row with columns A, B and D from Table1 that matches a row with columns B, C, and E from Table2, <strong>get the difference</strong> in column F from Table1 and column G from Table2 and <strong>paste</strong> it in a new column at the end of Table2.</p>
  <p>Not sure how to utilize the ArrayList as I don't know how to compare all the ArrayList items simultaneously. Would appreciate if you could suggest a method of approaching this problem.</p>
  <p>Note : the amount of unique columns chosen should be dynamic (there can be between 1 to 10 unique columns) and will be the same for both Table1 and Table2. Below is my very rough code.</p>
  <pre><code>Sub compare_tables()
  
  Dim wbMain As Workbook
  Dim wsMain As Worksheet
  
  Set wbMain = ActiveWorkbook
  Set wsMain = wbMain.ActiveSheet
  
  ' Select the Excel file to compare with
  
  ' Open the Excel file
  
  Dim wbSub As Workbook
  Dim wsSub As Worksheet
  
  Set wbSub = ActiveWorkbook
  
  ' Select the sheet number
  Dim subsheet As Long
  
  subsheet = InputBox(&quot;Please input the sheet number for the reference table.&quot;)
  
  Set wsSub = wbSub.Sheets(subsheet)
  
  ' User inputs the unique columns (number or letter) in Table A and have it stored in UniqueList1
  ' User inputs the quantity column (number or letter) in Table A and have it stored in QuantityList1
  
  ' User inputs the unique columns (number or letter) in Table A and have it stored in UniqueList2
  ' User inputs the quatity column (number or letter) in Table A and have it stored in QuantityList2
  
  ' Pseudo loop : Dim rowCount as Long
  ' mainRowCount = wbMain.UsedRange.Rows.Count
  ' subColCount = wsSub.UsedRange.Col.Count
  ' Dim i As Int
  ' Dim l As Int
  ' Dim diff As Int
  ' For i = 1 To rowCount
  '   For l = 1 To rowCount
  '       If wsMain.Cells(i, UniqueList1(all)) = wsSub.Cells(l, UniqueList2(all))
  '       diff = wsMain.Cells(i, QuantityList1) - wsSub(l, QuantityList2)
  '       wsSub.Cells(l, subColCount + 1) = diff
  '   Next
  ' Next
  
  End Sub
  </code></pre>