return without duplication, data from several guides

[marreco]

Hi.

how to return without duplication, the data from each column "A" of all the different worksheet tab "analytical"
data must be put on the "analytical" column "A"

[RHCPgergo]

Hi marreco,

please try this macro.

Option Explicit

' props to JP
' http://www.jpsoftwaretech.com/finding-values-in-an-array-without-looping/
Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
' checks if valueToFind is found in arr, no loop!
  IsInArray = (UBound(Filter(arr, valueToFind)) > -1)
End Function

Sub Test()
     
Dim Sh As Worksheet

Dim MyArray() As Variant
Dim Lr As Long, a As Long, i As Long
Dim isThere As Boolean

ReDim MyArray(0)
a = 0

For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Name <> "analytical" Then
        Lr = Sh.Range("A65536").End(xlUp).Row
        For i = 4 To Lr
            isThere = IsInArray(MyArray, Sh.Range("A" & i).Value)
            If isThere = False Then
                ReDim Preserve MyArray(a)
                MyArray(a) = Sh.Range("A" & i).Value
                a = a + 1
            End If
        Next i
    End If
Next Sh

For i = 3 To a + 2
    Sheets("analytical").Range("A" & i) = MyArray(i - 3)
Next i

End Sub

[xladept]

Hi Friend Marreco,

Try this:

Sub MarMos(): Dim ws As Worksheet, wa As Worksheet
Dim Vlr As Single, R As Range, S As Range, c As Integer
Set wa = Workbooks("ColumnAllSheets").Worksheets("analytical")
For Each R In wa.Range("A3:A" & wa.Range("A1").End(xlDown).row)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "RESUMO VENDAS" Then GoTo GetNextSheet
If ws.Name = wa.Name Then GoTo GetNextSheet
If ws.Range("A4") = "" Then GoTo GetNextSheet

For Each S In ws.Range("A4:A" & ws.Range("A3").End(xlDown).row)
If S = R Then Vlr = Vlr + ws.Range("B" & S.row)
Next

If Vlr Then
c = 1: Do Until wa.Cells(2, c) = UCase(Left(ws.Name, 3)): c = c + 1: Loop
wa.Cells(R.row, c) = Vlr: Vlr = 0: End If
GetNextSheet: Next: Next: End Sub

[marreco]

Hi.
I'm happy for the answers!!

thank you very much!!

[xladept]

You're welcome!