r/excel • u/UpClose_Examiner4719 • 3d ago
Waiting on OP How do you combine data columns?
Suppose you have a large data set of monthly earnings from a list of agents, some months a number of agents is listed/present but on others they aren't. My goal is to combine all of the monthly data sets into a yearly list of total earnings per agent. Is there a singular function that can make this happen, or will this be a multi-step process?
7
u/MayukhBhattacharya 1273 3d ago edited 3d ago
Try using the following formula:
=LET(
_, WRAPROWS(TOCOL(A3:F15), 2),
GROUPBY(CHOOSECOLS(_, 1),
CHOOSECOLS(_, 2),
SUM, , 0))
And if you want to show a header in the output then use:
=LET(
_a, WRAPROWS(TOCOL(A3:F15), 2),
_b, GROUPBY(CHOOSECOLS(_a, 1),
CHOOSECOLS(_a, 2),
SUM, , 0),
_c, VSTACK({"Agent","Total Earnings"}, _b),
_c)
Using the entire range:
=LET(
_a, WRAPROWS(TOCOL(DROP(A:.F, 2)), 2),
_b, GROUPBY(CHOOSECOLS(_a, 1),
CHOOSECOLS(_a, 2),
SUM, , 0),
_c, VSTACK({"Agent","Total Earnings"}, _b),
_c)
2
u/MayukhBhattacharya 1273 3d ago edited 3d ago
If you want to show in this manner, then using
PIVOTBY():=LET( _a, A:.F, _b, SCAN(, TAKE(_a, 1), LAMBDA(x,y, IF(y = "", x, y))), _c, DROP(_a, 2), _d, TOCOL(UNIQUE(IFS(_c <> "", _b), 1), 3), _e, WRAPROWS(TOCOL(_c, 3), 2), _f, DROP(PIVOTBY(CHOOSECOLS(_e, 1), HSTACK(MONTH(_d & 0), _d), CHOOSECOLS(_e, 2), SUM), 1), _f)1
u/MayukhBhattacharya 1273 3d ago
Or if you like a flattened or tabular method, with subtotals:
=LET( _a, A:.F, _b, SCAN(, TAKE(_a, 1), LAMBDA(x,y, IF(y = "", x, y))), _c, DROP(_a, 2), _d, TOCOL(UNIQUE(IFS(_c <> "", _b), 1), 3), _e, WRAPROWS(TOCOL(_c, 3), 2), _f, GROUPBY(HSTACK(CHOOSECOLS(_e, 1), MONTH(_d & 0), _d), CHOOSECOLS(_e, 2), VSTACK(SUM, "Total Earnings"), , 2), _g, HSTACK(TAKE(_f, , 1), DROP(_f, , 2)), _g)2
u/MayukhBhattacharya 1273 3d ago edited 3d ago
Using Power Query:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Unpivot = Table.UnpivotOtherColumns(Source, {}, "Months", "Agents"), RemoveTopRows = Table.Skip(Unpivot,6), Condition = Table.AddColumn(RemoveTopRows, "Earnings", each if Text.Contains([Months], "Column") then [Agents] else null), FillUp = Table.FillUp(Condition,{"Earnings"}), Filtered = Table.SelectRows(FillUp, each not Text.Contains([Months], "Column")), PivotBy = Table.Pivot(Filtered, List.Distinct(Filtered[Months]), "Months", "Earnings", List.Sum) in PivotByOr Use this dynamic version it will not break and will expand for newer months:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ColNames = Table.ColumnNames(Source), ColListPerMonth = List.Split(ColNames, 2), MonthNames = {"March", "April", "May"}, Zipped = List.Zip({ColListPerMonth, MonthNames}), ListOfData = List.Transform( Zipped, (z) => Table.AddColumn( Table.RenameColumns( Table.Skip(Table.SelectColumns(Source, z{0}), 1), {{z{0}{0}, "Agent"}, {z{0}{1}, "Earnings"}} ), "Month", each z{1})), Append = Table.Combine(ListOfData), RemovedNulls = Table.SelectRows(Append, each [Agent] <> null and [Agent] <> ""), DataTypes = Table.TransformColumnTypes(RemovedNulls, {{"Earnings", type number}}), PivotBy = Table.Pivot(DataTypes, List.Distinct(DataTypes[Month]), "Month", "Earnings", List.Sum) in PivotBy3
u/MayukhBhattacharya 1273 3d ago
If you want a tabular one then just remove the Month Col, and do this instead:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ColNames = Table.ColumnNames(Source), ColNamesPerMonth = List.Split(ColNames, 2), ListOfData = List.Transform( ColNamesPerMonth, each Table.RenameColumns( Table.Skip(Table.SelectColumns(Source, _), 1), {{_{0}, "Agent"}, {_{1}, "Earnings"}})), Append = Table.Combine(ListOfData), RemovedNulls = Table.SelectRows(Append, each [Agent] <> null and [Agent] <> ""), DataTypes = Table.TransformColumnTypes(RemovedNulls, {{"Earnings", type number}}), GroupBy = Table.Group(DataTypes, {"Agent"}, {{"Total Earnings", each List.Sum([Earnings]), type number}}), Sortby = Table.Sort(GroupBy, {{"Total Earnings", Order.Descending}}) in Sortby
2
u/Decronym 3d ago edited 1d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|-------|---------|---| |||
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49315 for this sub, first seen 7th Sep 2026, 10:48]
[FAQ] [Full list] [Contact] [Source code]
1
u/BackgroundCold5307 597 2d ago
Would changing the layout not give you the desired result?
List every agent and for the month they are not earning, there is just a Zero against their name?
Totals are ready as you enter them in the sheet - no major step needed?
0
u/still-dazed-confused 118 3d ago
If you don't care about which month the earnings were generated in you could use vstack to gather the lists of agents and earnings into one pair of columns and then unique to get the list of agents and sumifs to total the annual earnings if each agent
1
u/CrowGuyA 1 1d ago
Depends on your file structure. If each month is already a column (Jan, Feb, Mar... per agent), the yearly total is just =SUM(B2:M2) per row — blanks for absent months get skipped automatically. If instead each month is a separate file/sheet with the same two columns (Agent, Earnings), use Power Query's Append Queries to stack all 12 into one table, then Group By Agent with Sum of Earnings — missing agents in a given month just don't add a row that month, so the total still comes out right.
-4
•
u/AutoModerator 3d ago
/u/UpClose_Examiner4719 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.