Combine Tab with Heading Lookup

[jp16]

Hi, attached is a simple example, I have 3 tab where there is data. The data come from different source. I wan a code where it can combine all the data and put in respective heading. Refer result tab. The code which create new heading wherever there is new heading in the data tab. Data would be put under their respective heading tab. Can it be done?

[jp16]

Hi. Anyone canhelp?

[nilem]

Hi jp16,
try this

Please Login or Register  to view this content.

[jp16]

Wow!!! Never knew its so complicated. I am thinking of looping each worksheet. If count of heading in data tab is 0 in "result" , then add in heading. Then loop thru the data sheet to last row of data sheet, den if heading match, den put the value into "result". Is what i say possible?

[Marc L]

Another way like you wrote as a beginner starter
(to avoid the false forum message « Access Denied - Sucuri Website Firewall »
- as a VBA code is not any SQL injection ! -
I add a space between chars "c" & "e" of some Replace statements so just delete it …) :

Sub Demo1() 
      Const F = "IF(ISNA(MATCH(#,¤,0)),§,MATCH(#,¤,0))", H = "IF(ISNA(MATCH(#,¤,0)),#)" 
        Dim N&, S$, A$, V, C& 
    With Worksheets(1) 
           .UsedRange.Clear 
            Application.ScreenUpdating = False 
            Worksheets(2).UsedRange.Copy .[A1] 
        For N = 3 To Worksheets.Count 
                S = .UsedRange.Rows(1).Address(External:=True) 
                A = Worksheets(N).UsedRange.Rows(1).Address(External:=True) 
                V = Filter(Evaluate(Replac e(Replace(H, "¤", S), "#", A)), False, False) 
            If UBound(V) > -1 Then 
               .[A1].End(xlToRight)(1, 2).Resize(, UBound(V) + 1).Value = V 
                S = .UsedRange.Rows(1).Address(External:=True) 
            End If 
            With Worksheets(N).UsedRange 
                C = .Columns.Count + 1 
                V = Evaluate(Replace(Replac e(Replace(F, "¤", A), "#", S), "§", C)) 
                V = Application.Index(.Resize(, C).Value, Evaluate("ROW(2:" & .Rows.Count & ")"), V) 
            End With 
                .Cells(.UsedRange.Rows.Count + 1, 1).Resize(UBound(V), UBound(V, 2)).Value = V 
        Next 
    End With 
         Application.ScreenUpdating = True 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[jp16]

Coz the number of data sheet varies. Its not fix at 3 or 4. Can be more and can be less. Hence i need the vba to go thru all the sheet.

[Marc L]

As you already got it ‼ Next time you should better try before writing anything …