Consolidate data

Question

Appreciate the help

Answer 1

Hi,

sample

scenario

12 months in active workbook

month names: Jan, Feb,... Dec / change names as needed

employee names in column B, starting from row 8

total column, the last column in each sheet / month

result in a new sheet like below:

.

sample: Jan and Feb 2025

1/ Jan

.

2/ feb

.

======================

step1

Save  your Workbook with extension .xlsm (macros enabled workbook)

 ======================

Step2

2a) press ALT+F11 to open Visual Basic

2b) from the ribbon, select: Insert > Module and paste the code below on the right 

[Update-3]

'/// START VBA ///

Option Explicit

Dim cColl As New Collection

Dim ws, lo, rng, r, L, x, t, rFind1, rFind11, rFind2, nRow, nCol, v, vv, cc

Sub Get_Data_from_12months()

' ## 24/03/2025 ##

'// employee names , in column B, starting from row 9

nRow = 9

nCol = 2 ' 2=column-B

' << month sheet names / change as needed >>

v = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Application.ScreenUpdating = False

For Each vv In v

L = Sheets(vv).Cells(Rows.Count, nCol).End(xlUp).Row

On Error Resume Next

For x = nRow To L

cColl.Add Sheets(vv).Cells(x, nCol), Sheets(vv).Cells(x, nCol)

Next x

On Error GoTo 0

Next vv 'AAA

Set ws = Sheets.Add

ws.Cells(1, 1) = "Employees"

ws.Cells(1, 2).Resize(, 12) = v

ws.Cells(1, 14).Value = "TOTAL"

t = 2

For Each cc In cColl

ws.Cells(t, 1).Value = cc

t = t + 1

Next cc

ws.UsedRange.EntireColumn.AutoFit

t = 2

For Each vv In v

L = Sheets(vv).Cells(Rows.Count, nCol).End(xlUp).Row

For Each cc In cColl

Set rFind1 = Sheets(vv).Columns(nCol).Find(What:=cc, LookAt:=xlWhole)

If rFind1 Is Nothing Then

'nothing

Else

Set rFind11 = ws.Range("A:A").Find(What:=cc, LookAt:=xlWhole)

'Set rFind2 = Sheets(vv).UsedRange.Find(What:="Total*", LookAt:=xlPart)

Set rFind2 = Intersect(Sheets(vv).UsedRange, Sheets(vv).Rows("1:" & nRow - 1)).Find(What:="Total*", LookAt:=xlPart)

ws.Cells(rFind11.Row, t).Value = Sheets(vv).Cells(rFind1.Row, rFind2.Column).Value

End If

Next cc

t = t + 1

Next vv

L = ws.Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To L

ws.Cells(x, 14).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"

Next x

Set rng = ws.Range("A1").CurrentRegion

Set lo = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)

lo.ShowAutoFilterDropDown = False

lo.DataBodyRange.Value = lo.DataBodyRange.Value

With rng

.Borders.LineStyle = xlContinuous

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

End With

With ws.Sort

.SortFields.Clear

.SortFields.Add Key:=lo.ListColumns(1).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.SetRange lo.Range

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

' <

For Each r In rng

If r.Value = 0 Then r.Value = ""

Next '>>

Set cColl = Nothing

Application.ScreenUpdating = True

End Sub

'/// END VBA ///

2c) Press ALT+Q to Close Visual Basic

==========================

Step3

To run the macro, press ALT+F8,

*select '****Get_Data_from_12months'***from the list and click the run button.

or

add a button and assign the  vba macro

=========================

Answer 2

Hi,

Try this

=LET(nm,VSTACK(January:March!A2:A10),unique(FILTER(nm,nm<>"")))

Answer 3

Hi,

In cell A2 of the Summary worksheet, enter this formula

=LET(nm,VSTACK(January:March!A2:A10),FILTER(nm,nm<>""))

Hope this helps.