I have 5 worksheets in an Excel 2016 spreadsheet, each containing a list of investments. All of the sheets have columns in common (e.g. description, quantity, value), but these columns are arranged differently in each sheet (e.g. the quantity may be column A in the first sheet, but column C or D in the others). Each row contains details of a different investment, and the same investment may appear in more than one worksheet.
I want to produce a single consolidated list of all investments from these worksheets, with a sum of quantities and valuations for each.
I don't want to re-order the columns in each sheet, as they are copied and pasted regularly from an external source.
I've been trying out pivot tables with the Multiple consolidation ranges option, but can't get it to work because of the inconsistency in the column layouts between worksheets.
Can anyone suggest the best way of getting a consolidated list from these worksheets? I also have access to MS Access 2016, if that would be of any use?
Bookmarks