Consolidation of range with a twist

[jobisan]

Hi Folks,


I have been trying to use pivot table consolidation from multiple sources but didnt manage to get the desired outcome.


Im happy with any hint of solution, vba or formula / wizards...


I have 3 sheets in excel where you can find similar type of data at the same locations.
This is basically a recommendation (Buy, or Hold, or Sell) for a list of securities (ISIN numbers for those who know).


So for Sheet1, the data looks like this for example, the table starts in A1:


ISIN Recommendation
FR0011124544 Buy
XS0293392105 Hold
XS0764278528 Buy
DE000A14J7G6 Sell


Then in Sheet2, i have other datas with similar format:


ISIN Recommendation
ES0211845252 Buy
ES0211845260 Buy
ES0211845294 Hold
FI4000085550 Buy
FR0010014845 Buy
FR0011036979 Sell
FR0011233451 Hold
FR0011321256 Buy


Finally in Sheet3, ocne again some data:


ISIN Recommendation
XS0103214762 Buy
XS0204938798 Sell
XS0220790934 Sell
XS0224749100 Sell
XS0254808214 Hold


--------


1) How can i consolidate the data in 1 single table located on Sheet4 ?


2) How could i filter out the ones that are duplicated by giving priority (for the recommendation value) to Sheet1 over data in Sheet2, and Sheet3, and priority to Sheet2 over Sheet3 ? I tried to think of playing w numbers instead of text for the Recommandation part, but in vain.


Thank you all for your guidance.
Jobi

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm assuming (for now at least) that Sheets 1 - 4 are in that order in the workbook.
Question 1 results will go into columns A:B of Sheet4 and Question 2 into columns E:F.
Test in a copy of your workbook.

Question 1

Sub ConsolidateAll()
  Dim i As Long
  
  Sheets(4).Columns("A:B").ClearContents
  Sheets(4).Range("A1:B1").Value = Array("ISIN", "Recommendation")
  For i = 1 To 3
    With Sheets(i)
      Intersect(.UsedRange, .Columns("A:C")).Offset(1).Copy Destination:=Sheets(4).Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
  Next i
End Sub

Question 2

Sub ConsolidateWithPriority()
  Dim d As Object
  Dim i As Long, r As Long
  Dim a As Variant
  
  Sheets(4).Columns("E:F").ClearContents
  Sheets(4).Range("E1:F1").Value = Array("ISIN", "Recommendation")
  Set d = CreateObject("Scripting.Dictionary")
  For i = 3 To 1 Step -1
    With Sheets(i)
      a = .Range("A2", .Range("B" & .Rows.Count).End(xlUp)).Value
      For r = 1 To UBound(a)
        d(a(r, 1)) = a(r, 2)
      Next r
    End With
  Next i
  Sheets(4).Range("E2:F2").Resize(d.Count).Value = Application.Transpose(Array(d.Keys, d.Items))
End Sub

 

[jobisan]

Thank you Peter ~ I will give it a try, i did simplify the problem, so ill try to work around your answer

 

[Peter_SSs]

i did simplify the problem, so ill try to work around your answer

That's good, but post back with more accurate details if you get stuck.