• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 50
  • Last Modified:

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
0
ADRIANA P
Asked:
ADRIANA P
  • 2
1 Solution
 
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 ChongCommented:
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
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong Great Job !! Working as needed  Expert !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now