I was tasked with working on something for work and the summary of what I need to do is as follows:
I have 4 data sets (one on each tab of a spreadsheet) that I need to consolidate. Each set has over 40 columns and there are many similar columns but just not in a standard order.
I want all of the data sets to filter out everything ageing less than 90 days and then everything > 90 days will be exported to a new tab (so 4 sets of data ultimately combined into 1). Is there a way to do this?
The other issue is since the columns are not matching for all 4 tabs, it will be difficult to consolidate the results. Is there also a way to define what fields can be exported to the new consolidated tab?
Any help would be greatly appreciated.
- DEBSLv 73 weeks ago
Putting VBA aside, you're looking at a manual process which, if this is a one-time exercise, is likely quicker than any automated way to do it.
If it were me, I'd
1. insert a a row above the columns and then label each with a number. Match that number for the corresponding column on each of the 4 tabs.
2. Sort each tab by the numbers you put in so that the wanted columns are all in the same order.
3 Sort each tab by date. Copy and paste the desired data into you new sheet making sure you have the header with it. (Probably 8 copy and pastes.)
- garbo7441Lv 73 weeks ago
This would be quite simple to do using a VBA event handler. Upon initiating, the code would evaluate each row, in each column, for all four sheets. All items >90 (I infer older than 90 days) would be copied to the next available row in the consolidation sheet. Double clicking any cell in any of the four sheets would create a clean, new data extract in the consolidation sheet.
If you wish to have a workable solution, no charge, please email to email@example.com. More specific information will be needed; sheet names, data column references, and consolidation sheet name.