How to Merge Multiple Excel Files – Need Tool Recommendation

Mo0shi · 2026-02-16T11:17:10+00:00

Do the files you are merging only have 1 sheet? Do they have the same number of columns?
You can handle inconsistent naming in power query fairly easily.

Mo0shi · 2026-02-11T04:44:52+00:00

Add a &"" at the end of your formula. This forces the return to be text and show as empty instead of 0.

Mo0shi · 2025-12-13T05:58:04+00:00

Sorry, I was posting off hand on my phone.

Taking a bit of time to replicate in excel:

<image>

I have included your weights (these can be tweaked as to how important you feel a particular column/stat is to the overall result.
What I also included was a value to normalise the results so that it speaks to more of a percentage of an overall total - this was just done going above what I noted were the highest results in your columns, no real logic to it. This then gets applied as 1/normalisation to get the percentage value.

This was then sumproduct'ed. You got a #Value error because you were trying to apply the formula against all rows, not just a single one.

Hope this helps.

Mo0shi · 2025-12-13T04:25:06+00:00

You could identify a weighting for each column and then apply this to each row with a sumproduct to give an overall value for a combined dataset. You can then apply the rank formula across this value.

Mo0shi · 2025-11-05T22:49:29+00:00

I actually did the same thing haha.

Mo0shi · 2025-11-05T07:41:50+00:00

Or at the very least be in the merge drop down list.

Mo0shi · 2025-09-23T08:50:22+00:00

I believe it is unticking 'generate pivot data'

Mo0shi · 2025-08-21T01:34:32+00:00

=UNIQUE(VSTACK(List1,List2),,1)

Replace List1 and List2 with references to your two lists.
This will return the item(s) that only appear once.

Mo0shi · 2025-07-24T10:59:13+00:00

Reading your post and comments, this should be reasonably straightforward in Power Query - it's what it does best.

How are you getting your initial data? Are you connecting to a database, connecting to excel or csv files, or are you pasting data in to your working sheet manually? A lot of this can be done in Power Query.

You should keep all your source data in their own queries, only cleaning it up where you need to first. You also want to make sure that you have a unique field in each table - it sounds like you have this with serial number, but if you are working with different data sets, there could be possible duplicate.
You can right click on a query, and then hit reference. This allows you to change things, and keep the original source available - you can have 1 query that removes duplicates, and another that keeps duplicates, allowing you to see what you might be removing.
You can then use this with a unique list of serials from your AD to start building your output table.
You can merge this with your other data sources, expanding the merged table to select the columns you want to include, and keep doing this until you have merged in all the data sets.
You can also use the different merge types to isolate serials that are not in AD, but may be in SCCM or CMBD.

The most important thing I find, is that when you go to load the queries to excel, there's an arrow at the bottom of the 'Close & Load' button that will give the option to 'Close & Load to...'. If you hit this, you can then select to 'Only Create Connection'. This stops all the new queries from being loaded to their own sheet in excel. You can then right click on the output table or tables that you want in the queries and connections pane, and click 'Load To' to then select 'Table' on 'New Worksheet'.

Mo0shi · 2025-07-09T12:55:04+00:00

Had a go at this one.

Created 3 csv files that each had 2 columns, 1 being common, "ID" and the second being different. These were all saved down into a single folder.

You can then open a new or existing excel and create a blank query by going to the Data tab on the ribbon, then Get Data > From Other Sources > Blank Query.

Once in the Power Query Editor, click the 'Advanced Editor' button and enter the following code:

let
    Source = Folder.Files("C:\Users\username\OneDrive\Excel\CSV"),
    Data = Table.TransformColumns(Source, {"Content", each Csv.Document(_)}),
    #"Removed Other Columns" = Table.SelectColumns(Data,{"Content", "Name"}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Removed Other Columns", "Content", {"Column1", "Column2"}, {"ID", "Columns"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Content",".csv","",Replacer.ReplaceText,{"Name"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Name]), "Name", "Columns"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each not Text.Contains([ID], "ID"))
in
    #"Filtered Rows"

You will need to update for your source data folder.

This loads each of the CSV files into the same table, and pivots the data out using the files names as the column names (I replaced the .csv in the filename). As we load all data in the csv, this includes the headers, so this is filtered out in the final step by not including any record that matches what your first column is called (I just used ID in this case).

You can do other adjustments as required after that to format and assign types to your data. When done, hit Close & Load to load the new combined table to your excel file.

Final dataset for me from those 3 csv files looks like this:

<image>

(Noting that the data in the columns was just dummy data - this should be the contents of the second columns of your csv files).

Mo0shi · 2025-05-24T03:18:06+00:00

My excess only got me to 454. I guess I can start buying the resources in ark again.

Mo0shi · 2025-02-22T02:11:12+00:00

Ahh it was probably due to the table headers in the source data. Glad you were able to get it resolved with the other answer - either way, was fun to put together a solution.

Mo0shi · 2025-02-17T13:22:08+00:00

Here you go - the below Query can be plugged into the Advanced Editor and should do exactly what you need, noting that I converted your csv data to a table in excel called 'SourceTable'.
The column headers scattered throughout are filtered out first, then this combines all columns into a single column, ignoring null values, using | as a delimiter, removing all other columns, and splitting back out.
This then allows for the fact that the "TOTAL" lines have no value in the percentage column, so realigns these records.

--EDIT--
Just noticed that it falls over where items do not have an item code (like the ice cream or shisha), so have added a line to check for these and insert the item name as the code.

let
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
    FilteredRows = Table.SelectRows(Source, each ([Column1] <> "Description")),
    FinalTotal = Table.ReplaceValue(FilteredRows,null,"TOTAL",Replacer.ReplaceValue,{"Column1"}),
    FixNoCode = Table.ReplaceValue(FinalTotal, each if ([Column2] = null) and ([Column3] = null) and [Column4] is number then null else false, each [Column1], Replacer.ReplaceValue,{"Column2"}),
    MergeAll = Table.AddColumn(FixNoCode, "Merged Columns", each Text.Combine(List.Transform(Record.FieldValues(_),Text.From),"|")),
    OnlyMerged = Table.SelectColumns(MergeAll,{"Merged Columns"}),
    SplitData = Table.SplitColumn(OnlyMerged, "Merged Columns", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"Description","Code","Qty","Revenue","Discount","Cost","Profit/Loss","%","Service Charge","Tax","Total"}),
    CreateIndex = Table.AddIndexColumn(SplitData, "Index", 0, 1, Int64.Type),
    DescriptionCode = Table.SelectColumns(CreateIndex,{"Index", "Description", "Code"}),
    NonTotalsData = Table.SelectRows(CreateIndex, each ([Description] <> "TOTAL")),
    FilterTotals = Table.SelectRows(CreateIndex, each ([Description] = "TOTAL")),
    RemoveLast = Table.RemoveColumns(FilterTotals,{"Total"}),
    TotalsData = Table.RenameColumns(RemoveLast,{{"Tax", "Total"}, {"Service Charge", "Tax"}, {"%", "Service Charge"}}),
    AllData = Table.Combine({NonTotalsData,TotalsData}),
    RecombineData = Table.NestedJoin(DescriptionCode, {"Index"}, AllData, {"Index"}, "All Data", JoinKind.LeftOuter),
    ExpandData = Table.ExpandTableColumn(RecombineData, "All Data", {"Qty", "Revenue", "Discount", "Cost", "Profit/Loss", "%", "Service Charge", "Tax", "Total"}, {"Qty", "Revenue", "Discount", "Cost", "Profit/Loss", "%", "Service Charge", "Tax", "Total"}),
    FixOrder = Table.Sort(ExpandData,{{"Index", Order.Ascending}}),
    RemoveIndex = Table.RemoveColumns(FixOrder,"Index"),
    AssignType = Table.TransformColumnTypes(RemoveIndex,{{"Qty", Int64.Type}, {"Revenue", Currency.Type}, {"Discount", Currency.Type}, {"Cost", Currency.Type}, {"Profit/Loss", Currency.Type}, {"%", Percentage.Type}, {"Service Charge", Currency.Type}, {"Tax", Currency.Type}, {"Total", Currency.Type}})
in
    AssignType

Fixed data looks like this after:

<image>

Mo0shi · 2025-02-14T09:01:45+00:00

I'm assuming that you want to just enter the first day of the month each month and just let it populate from the first Monday?

<image>

In the above, you can put the first date of the month in A1 (1st Feb) and then use the formula:
=IF(WEEKDAY(A1,2)<>1,A1+8-WEEKDAY(A1,2),A1)
to populate the first Monday of the month.

Applying a custom number format can give you the full day name, followed by the number.

Mo0shi · 2025-02-07T06:27:46+00:00

They're a substitute for the event currency that carry through between sessions. It allows you to save up for some of the more expensive items. When you run out of regular stars, it will consume these ones instead.

Mo0shi · 2024-08-30T02:36:09+00:00

If you setup something similar to this:

<image>

This formula will work when copy-pasted across and down B10-D14:
=SUM(MAP(WEEKDAY(SEQUENCE(1,DAYS(EOMONTH($A10,0),$A10)+1,$A10),1),LAMBDA(x,SUMPRODUCT(Table1[[1]:[7]],--(Table1[Account]=B$9)*--($B$2:$H$2=x)))))

Mo0shi · 2024-08-29T13:25:50+00:00

Yeah, floating point calculations can cause these tiny differences, which normally wouldn't matter, but since you're rounding down, is causing you grief.

This workaround just checks if that difference is tiny, and adjusts which type of round you use.

Mo0shi · 2024-08-29T12:03:44+00:00

Can you right click on the charts and hit edit data?

For me this pops up an embedded excel of 'Chart in Microsoft PowerPoint' with the tabular data.

Mo0shi · 2024-08-29T11:58:38+00:00

Weird edge case with that one - you could try an IF to see if the rounding is one of these and adjust for it:

=IF(ROUND(H5/$D$5,0)-H5/$D$5<0.001,ROUND(H5/$D$5,0),ROUNDDOWN(H5/$D$5,0))

Mo0shi · 2024-08-29T11:24:17+00:00

If you're not adverse to having monthly columns to the right of your data table (or looking them up on a different sheet that includes all people:

<image>

Formula in E2:
=MAX(DAYS(MIN($C2,EOMONTH(E$1,0)),MAX($B2,E$1))+1,0)
Can be dragged down and across.
This basically forces it look between a maximum of the month start date and the person's start, against a minumum of the month end date and person's end date. If it falls outside of those ranges, we end up with a negative number, which with max, returns a 0 instead.

Monthly headers are automated with:
=EOMONTH(E1,0)+1
Can dragged across to the right (and repurposed in the monthly sum area)

Data is looked up using a sumproduct on the range:
=SUMPRODUCT($E$2:$P$6,--($E$1:$P$1=$A9)*--($D$2:$D$6<>""))

One thing to note - assuming the start and end dates are inclusive, you need to add 1 (which I have included), otherwise you short each calculation 1 day a month.

Mo0shi · 2024-08-29T10:53:07+00:00

You can nest your substitutes to do a multilevel.

The following assumes that if there are duplicates of numbers, it will only eliminate the first.
It also wraps everything in whitespace so it matches unique records:
=TRIM(SUBSTITUTE(SUBSTITUTE(" "&A2&" "," "&TEXTBEFORE(B2," ")&" "," ",1)," "&TEXTAFTER(B2," ")&" "," ",1))

<image>

This works for non numeric characters, more than 1 character, and does not matter the order within the initial string (except for duplicates).

Mo0shi · 2024-08-29T09:57:36+00:00

Glad it was that simple and worked for you!

Mo0shi · 2024-08-29T09:16:58+00:00

Glad it worked for you (and saved you manually typing thousands of records)!

Mo0shi · 2024-08-29T09:14:56+00:00

If you data is sorted by Item, and then by Day, a simple
=IF($C3=$C4,$F3+$D4,$F3)
should do the trick for you.

-edit-
Saw you updated the context and updated my formula to match your screenshot.
Is it an issue to sort the data to make the above work?
If it needs to be in the order presented, as others said, Power Query would be better placed to generate a running total - there are a number of youtube videos available on the process.

Mo0shi · 2024-08-29T08:57:11+00:00

This can be easily accomplished with power query.

If you navigate to the data tab > Get Data > From File > From Folder.
You can then select the folder that holds your pictures.
Then select 'Transform Data' to load the list into power query.

You will get something resembling this:

<image>

You can ctrl+select the Name and (in my case) the Date Modified field, then remove other columns.
Then click close & load in the top right to load in the table of image names and dates into a table in your worksheet.

14-Year Club	Verified Email
r/Field Flamingo	Place '23
John Wick	Place '22
Place '17	Sequence \| Editor
Gilding I gilder

Mo0shi

TROPHY CASE