Common methods to consolidate in Excel include consolidating by position, by category, by formula or by using Excel’s Pivot Table feature.
Scroll past the jump to learn how to consolidate in Excel so that your information appears in a master worksheet as a reference whenever you need to generate reports.
A quick solution is to make a copy of one of the sheets and delete all irrelevant columns keeping only those you want to merge.
And then, run the Consolidate Worksheets Wizard, and select the option Copy data only for the matching columns in my first table in the list.
Since F9 replaces formulas with values, if your original data changes, then you must re-write the CONCATENATE(TRANSPOSE(…)) again.