NEED CONSOLIDATE DATA

HAVE  DATA
OF 3 SHEET NEED BE SHOW IN ONE SHEET

1-BY WEEK NUMBER

THEN BY

2- DAY

THEN  BY

AM PM


ALLDTA  sheet is where all consolidate
1234567_.xlsx
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tom CieslikIT EngineerCommented:
If you will put consolidate word in Excel Help you'll get instruction how consolidate works with multiple sheets

Capture.JPG
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Tom Cieslik thanks for the fast reponse

I prefer an VBA solution  like it take the data to the new sheet after  hit an button

But thanks for the great suggestion.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try something like this:

Sub test()
    Dim ws(2) As Worksheet, wsAll As Worksheet, startRow(2) As Integer, tValue(2) As String
    Dim Row As Integer, lastRow
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set ws(0) = Sheets("DTA2")
    Set ws(1) = Sheets("DTA3")
    Set ws(2) = Sheets("DTA4")
    Set wsAll = Sheets("ALLDTA")
    
    startRow(0) = 4
    startRow(1) = 4
    startRow(2) = 4
    Row = 4
    
    'Delete previous rows
    lastRow = wsAll.Cells(wsAll.Rows.Count, "C").End(xlUp).Row
    wsAll.Rows(Row & ":" & lastRow).Delete
    
    tValue(0) = getSorted(ws(0).Cells(startRow(0), "C"))
    tValue(1) = getSorted(ws(1).Cells(startRow(1), "C"))
    tValue(2) = getSorted(ws(2).Cells(startRow(2), "C"))
    
    'Do While tValue(0) <> "" Or tValue(1) <> "" Or tValue(2) <> ""
    Do Until tValue(0) = "" And tValue(1) = "" And tValue(2) = ""
    
        'Debug.Print Row & " = " & tValue(0) & ":" & tValue(1) & ":" & tValue(2) & " = > " & getMin(tValue(0), tValue(1), tValue(2))
        
        Select Case getMin(tValue(0), tValue(1), tValue(2))
        Case 1:
            ws(0).Range("C" & startRow(0) & ":U" & startRow(0)).Copy
            wsAll.Range("C" & Row & ":U" & Row).PasteSpecial xlPasteAll
            wsAll.Cells(Row, "B") = ws(0).Name
            startRow(0) = startRow(0) + 1
            tValue(0) = getSorted(ws(0).Cells(startRow(0), "C"))
            Row = Row + 1
            Application.StatusBar = "Running " & Row & " ..."
        Case 2:
            ws(1).Range("C" & startRow(1) & ":U" & startRow(1)).Copy
            wsAll.Range("C" & Row & ":U" & Row).PasteSpecial xlPasteAll
            wsAll.Cells(Row, "B") = ws(1).Name
            startRow(1) = startRow(1) + 1
            tValue(1) = getSorted(ws(1).Cells(startRow(1), "C"))
            Row = Row + 1
            Application.StatusBar = "Running " & Row & " ..."
        Case 3:
            ws(2).Range("C" & startRow(2) & ":U" & startRow(2)).Copy
            wsAll.Range("C" & Row & ":U" & Row).PasteSpecial xlPasteAll
            wsAll.Cells(Row, "B") = ws(2).Name
            startRow(2) = startRow(2) + 1
            tValue(2) = getSorted(ws(2).Cells(startRow(2), "C"))
            Row = Row + 1
            Application.StatusBar = "Running " & Row & " ..."
        End Select
    Loop
    wsAll.Cells(3, 2).Select
    
    Application.StatusBar = "Finished"
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Function getSorted(v As String) As String
    If Len(v) = 6 Then
        getSorted = Right(v, 4) & "0" & Left(v, 2)
    ElseIf Len(v) = 7 Then
        getSorted = Right(v, 4) & Left(v, 3)
    Else
        getSorted = v
    End If
End Function

Function getMin(v1 As String, v2 As String, v3 As String) As Integer
    Dim t1 As String, t2 As String, t3 As String
    t1 = v1
    t2 = v2
    t3 = v3
    
    If t1 = "" Then t1 = "XXXXXXX"
    If t2 = "" Then t2 = "XXXXXXX"
    If t3 = "" Then t3 = "XXXXXXX"
    
    If t1 <= t2 And t1 <= t3 Then
        getMin = 1
    ElseIf t2 <= t1 And t2 <= t3 Then
        getMin = 2
    Else
        getMin = 3
    End If
End Function

Open in new window

1234567_b.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong Great Job !! Working as needed  Expert !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.