Combine Duplicate data from different worksheets base on cell value

[iriex]

Please can I get some assistance on the following:
1. Look for duplicates rows throughout the different worksheets using the value under column A (excluding A1).
2. If there are duplicates, ensure that all the possible values combine are listed (excluding duplicate values but including different values). The values for each of the rows (start at C3) are in every other cell and the other cells are just counts (starting at D4) and every other cell depending on the amount of data.
3. Also, for each row, list the name of the worksheets in the same cells that apply to the same data. If the data only appears to be in only one of the worksheet, then just list that worksheet.
4. Data should be listed in ascending order using the numbers in Column A (exluding headinger in A1) and Value should be in ascending order per row as well.
5. Exclude Count data on the consolidated sheet.

Sheet 1[TABLE="width: 581"]
<tbody>[TR]
[TD]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]School#
[/TD]
[TD]Description
[/TD]
[TD]Value
[/TD]
[TD]Count
[/TD]
[TD]Value
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Cali
[/TD]
[TD]Apple
[/TD]
[TD]9990
[/TD]
[TD]Grapes
[/TD]
[TD]7999
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]Texas
[/TD]
[TD]BBQ
[/TD]
[TD]8888
[/TD]
[TD]Grits
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]53
[/TD]
[TD]Mexico
[/TD]
[TD]Tacos
[/TD]
[TD]10000
[/TD]
[TD]Avacados
[/TD]
[TD]6574
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Sheet 2[TABLE="width: 581"]
<tbody>[TR]
[TD]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]School#
[/TD]
[TD]Description
[/TD]
[TD]Value
[/TD]
[TD]Count
[/TD]
[TD]Value
[/TD]
[TD]Count
[/TD]
[TD]Value
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Cali
[/TD]
[TD]Orange
[/TD]
[TD]9990
[/TD]
[TD]Grapes
[/TD]
[TD]7999
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]Texas
[/TD]
[TD]Hot Sauce
[/TD]
[TD]8888
[/TD]
[TD]Grits
[/TD]
[TD]5000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53
[/TD]
[TD]Mexico
[/TD]
[TD]Tacos
[/TD]
[TD]10000
[/TD]
[TD]Avacados
[/TD]
[TD]6574
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]New York
[/TD]
[TD]Hot Dogs
[/TD]
[TD]8768
[/TD]
[TD]Pizza
[/TD]
[TD]23432
[/TD]
[TD]Pasta
[/TD]
[TD]1500
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Expected Outcome (Consolidated Data)

• Data Under School# is sorted A-Z as well as data in Value per row and the sheet the data appears in is listed as well.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]School#
[/TD]
[TD]Description
[/TD]
[TD]Value
[/TD]
[TD]Sheet
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]New York
[/TD]
[TD]Hot Dogs
Pasta
Pizza
[/TD]
[TD]Sheet2
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Cali
[/TD]
[TD]Apple
Grapes
Orange
[/TD]
[TD]Sheet1
Sheet2
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]Texas
[/TD]
[TD]BBQ
Grits
Hot Sauce
[/TD]
[TD]Sheet1
Sheet2
[/TD]
[/TR]
[TR]
[TD]53
[/TD]
[TD]Mexico
[/TD]
[TD]Avacados
Tacos
[/TD]
[TD]Sheet1
Sheet2
[/TD]
[/TR]
</tbody>[/TABLE]

Once I can get that outcome, then I could filter base on the data preferably by Sheet. For example, I would filter contain=Sheet1, my expected outcome would show everything expect the Row with School#=1.

Other important things to note:

• My excel document has 13 and counting different worksheets, not including the consolidated worksheet.
• One row data has gone up to column GR (so there is no limitation on how much data one can expect in a row).

This is the macro script i have so far, which I found online as I have no experience at all with macros (willing to learn though). This so far only combines all the data from the different worksheets into one, which is just a start but not my final outcome. I would glady appreciate it if someone can help me with this, as it would take me quite some time to figure out on my own which I'm trying to avoid and need to complete as soon as possible.

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", After:=sh.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, MatchCase:=False).Row
    On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", After:=sh.Range("A1"), Lookat:=xlPart, _
                            LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    On Error GoTo 0
End Function

Sub CopyDataWithHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Add a new summary worksheet.
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

' Fill in the start row.
StartRow = 2

' Loop through all worksheets and copy the data to the
' summary worksheet.
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> DestSh.Name Then

        ' Find the last row with data on the summary
        ' and source worksheets.
        Last = LastRow(DestSh)
        shLast = LastRow(sh)

        ' If source worksheet is not empty and if the last
        ' row >= StartRow, copy the range.
        If shLast > 0 And shLast >= StartRow Then
            'Set the range that you want to copy
            Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

           ' Test to see whether there are enough rows in the summary
           ' worksheet to copy all the data.
            If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
               MsgBox "There are not enough rows in the " & _
               "summary worksheet to place the data."
               GoTo ExitTheSub
            End If

            ' This statement copies values and formats.
            CopyRng.Copy
            With DestSh.Cells(Last + 1, "A")
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End With

        End If

    End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    ' AutoFit the column width in the summary sheet.
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Thanks.