Forum Discussion
Combining and sorting workbooks based on a single common column header.
Is there a way to combine and sort the columns from 2 workbooks so that in the example below, I could get the "Send Paper Bill" column tied to the rest of the account data in spreadsheet one regardless of the order of the account numbers? Basically, we're exporting spreadsheets from a reporting system and our billing company won't provide a single data source where all of the databases would be together based on the account number.
I've intentionally minimized data for this example. If there's a way to do this, we will be combining multiple spread sheets with varying numbers of columns, but all will have the Account Number column.
Thank you
10 Replies
- Daniel1945 - Why VLOOKUP is not a preferred choice here ?
- I was under the impression that Vlookup would only work if I was trying to pull data from a single column in the second workbook. While my original example did only contain one column of data that needed to be pulled, many of our workbooks will have more than one.
If you consider such option that could be done with Power Query. All spreadsheets from the billing system could be added / removed from separate folder. Power Query in separate Excel analyses all existing in folder spreadsheets and generates the results.
Details it's better to discuss having sample files.
Good afternoon,
From what I can find, it does appear this should be completed with Power Query. I haven't found any instructions that show exactly the steps to take to combine these without the data from sheet 2 ending up below the data from sheet 1 on the resulting table. I've added 2 small test files in case that helps.Here's a screenshot of what I can get if I instruct Power Query to keep all columns from both sheets. It's closer to what I need, but still not matching the data up across the rows.
May I clarify what is the logic of working with files.
- At any moment do you have only one Customer Info file and one PaperLess file, or you work with multiple files added to the folder from time to time?
- If multiple files is information about the same account shall be taken on the latest date of the file, or they are combined?
- What exactly "variable number of columns" means, is that for both type of files or only one and which columns are always fixed by name and position?
