I've been looking at this issue for weeks now and it's doing my head in. I've asked for bits and pieces of advice, but I'm going quite spare, so thought I'd try posting the 'whole' issue.
I have a set of data (about 100 columns and 60 records).
- 28 columns are forecast dates and 28 are matching actual dates. These are 'scattered' throughout the rest of the columns. The rest of the columns are necessary for my reporting, but part of the issues I currently have. The forecast and actual columns have the matching titles e.g. "Plan (Forecast)" "Plan (Actual)". Forecast and Actual is always in brackets and always the last element of the title.
- Each record has a unique ID number. The records may be in a different order from week to week (although I could sort them). Records can be deleted and added, so the data sets may not match, row for row.
I need write code that will compare two sets of data (most commonly 'today' and 'last week') and highlight various dates:
- a forecast date today is later than the forecast date last week - red (applied to just the forecast date)
- a forecast date today is overdue (ie before today and no matching actual) - orange (applied to both the forecast and the blank actual date cell)
- a new actual date this week (ie no actual date last week) - green (applied to just the actual date)
So far ... I've got a macro to create a backup workbook every day. The macro copies the ID, forecast and actual columns to a 2nd worksheet, to give me just the information I need for running the comparison. I've used ranges to copy these columns so they are grouped: ID, all forecasts, all actuals.
My "comparison macro" has stalled
I've got to the point where
- the user nominates the two files they want to compare.
- the "today" file is opened
- the "last week" backup sheet is copied in
- the sheets are renamed "current" and "previous" (mainly so I can keep things straight in my head)
- the data on "current" is copied to a new worksheet so the columns are the same order as the "previous" worksheet (ie ID, all forecasts, all actuals, all other columns)
And that's as far as I've got; although I've taken several backwards steps at various points.
I've tried using "data from other sources" querying to create a single set of data. But I think there's too many columns. When I use just a couple of columns, it works. But if I use the full data set it says my ID fields are different and it won't link them
I figure there are two options, but I'm not having much success in working out the best way forward with either.
1. Get all the data - current and previous on a single sheet; guessing using lookups to match against ID#
2. Conditional Format directly on the current data matching against the previous data
I'm not actually looking for code here. I'm really after advice as to whether I'm tackling this the right way or not. If there's an easier/simpler/cleaner way. If there's some tool in Excel I'm overlooking.
I can't possibly be the only or the first person to need to compare historical records.
I have a set of data (about 100 columns and 60 records).
- 28 columns are forecast dates and 28 are matching actual dates. These are 'scattered' throughout the rest of the columns. The rest of the columns are necessary for my reporting, but part of the issues I currently have. The forecast and actual columns have the matching titles e.g. "Plan (Forecast)" "Plan (Actual)". Forecast and Actual is always in brackets and always the last element of the title.
- Each record has a unique ID number. The records may be in a different order from week to week (although I could sort them). Records can be deleted and added, so the data sets may not match, row for row.
I need write code that will compare two sets of data (most commonly 'today' and 'last week') and highlight various dates:
- a forecast date today is later than the forecast date last week - red (applied to just the forecast date)
- a forecast date today is overdue (ie before today and no matching actual) - orange (applied to both the forecast and the blank actual date cell)
- a new actual date this week (ie no actual date last week) - green (applied to just the actual date)
So far ... I've got a macro to create a backup workbook every day. The macro copies the ID, forecast and actual columns to a 2nd worksheet, to give me just the information I need for running the comparison. I've used ranges to copy these columns so they are grouped: ID, all forecasts, all actuals.
My "comparison macro" has stalled
I've got to the point where - the user nominates the two files they want to compare.
- the "today" file is opened
- the "last week" backup sheet is copied in
- the sheets are renamed "current" and "previous" (mainly so I can keep things straight in my head)
- the data on "current" is copied to a new worksheet so the columns are the same order as the "previous" worksheet (ie ID, all forecasts, all actuals, all other columns)
And that's as far as I've got; although I've taken several backwards steps at various points.
I've tried using "data from other sources" querying to create a single set of data. But I think there's too many columns. When I use just a couple of columns, it works. But if I use the full data set it says my ID fields are different and it won't link them
I figure there are two options, but I'm not having much success in working out the best way forward with either.
1. Get all the data - current and previous on a single sheet; guessing using lookups to match against ID#
2. Conditional Format directly on the current data matching against the previous data
I'm not actually looking for code here. I'm really after advice as to whether I'm tackling this the right way or not. If there's an easier/simpler/cleaner way. If there's some tool in Excel I'm overlooking.
I can't possibly be the only or the first person to need to compare historical records.
