Excel help?

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.

2 Answers

  • DEBS
    Lv 7
    7 months 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.)


    • Commenter avatarLog in to reply to the answers
  • 7 months 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 excelhelp@comcast.net. More specific information will be needed; sheet names, data column references, and consolidation sheet name.

    • Commenter avatarLog in to reply to the answers
Still have questions? Get answers by asking now.