Attached file 151031 Comparisons.xlsx has three sheets with Actual, Forecast and Budget numbers for various months.
End user wants the ability to select and compare any two periods by selecting the relevant months and categories.
I have created the "choice" range in Col H of the Comparisons sheet, but can't see what formula will "find" the relevant column on the correct sheet from the list?
Any suggestions received gratefully, as ever
Ochimus
Delete row 1 in the Actual sheet, so that the 3 data sheets all have the same format. Then you can use this formula in B3 of the Comparison sheet:
=INDEX(INDIRECT("'"&RIGHT(B$1,LEN(B$1)-FIND(" ",B$1))&"'!B:G"),MATCH($A3,INDIRECT("'"&RIGHT(B$1,LEN(B$1)-FIND(" ",B$1))&"'!A:A"),0),MATCH(DATEVALUE(1&LEFT(B$1,FIND(" ",B$1)-1)),INDIRECT("'"&RIGHT(B$1,LEN(B$1)-FIND(" ",B$1))&"'!B1:G1")))
Copy this down to the bottom of your list of elements. Then you can copy the formulae from column B into column D. You will need to ensure that you choose the values from the drop-downs in B1 and D1, as it looks like you typed D1 yourself (missing hyphen).
Hope this helps.
Pete
See if the attached helps.
I've also suggested a solution using a normalised version of your data which is far more flexible and means that you can use it as a Pivot Table for other analysis.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Richard,
Appreciate the prompt suggestion, but unfortunately you are using separate selections for the category (Actual, Budget and Forecast) and the Month, which is not what the End User asked for, so it's not viable.
Ochimus
Here's the file that I set up when I was working on your problem (not sure why you've not commented on the other suggestions made to you).
Hope this helps.
Pete
By joining two different things in a single drop down, i.e. a date and a worksheet you unnecessarily complicate things. It seemed to me that it wasn't too onerous a request to ask the user to select both a worksheet and a month instead of the two being combined. However the attached simply uses the original idea and converts the actual selection to the two constituent parts of date and sheet so that the original formulae I gave you will still work.Originally Posted by Ochimus
Your sheet layout isn't exactly formula friendly, but it can be done.
First of all, you will need to delete row 1 in the 'Actual' sheet so that the dates are in row 1, the same as with the other 2 sheets.
Secondly, the criteria in B1 and D1 would need to be consistent, the formula below relies on the only space in the cell being between the year and actual / budget / forecast.
=SUMPRODUCT((INDIRECT(MID(B$1,FIND(" ",B$1)+1,100)&"!A2:A10")=$A3)*(INDIRECT(MID(B$1,FIND(" ",B$1)+1,100)&"!B1:M1")=DATEVALUE("1-"&LEFT(B$1,FIND(" ",B$1)-1)))*INDIRECT(MID(B$1,FIND(" ",B$1)+1,100)&"!B2:M10"))
1 of the main problems with how you have your data and summary set up, is that you are have different types of data and are trying to compare them. For example, in yoyr data, you have real dates (1/1/15, 2/1/15 etc), but you are trying to find data that matches Jan 15 or Feb 15. the dates are numeric, but jan 15 is text...there is no Jan 15 in your data, nor Feb 15.
Richard has attempted to put your data into a "good" fomat that makes use of exxcel's built-in functions. Having your data in the format you have, does not mean your extract cannot be done but it makes it unnecessarily complicated and involved.
Compare Richard's...
=INDEX(INDIRECT(B$1&"!$A$3:$M$5"),MATCH($A6,ACTUAL!$A$3:$A$5,FALSE),MATCH(B$4,INDIRECT(B$1&"!$A$2:$M$2"),FALSE))
with Pete's...
=INDEX(INDIRECT("'"&RIGHT(B$1,LEN(B$1)-FIND(" ",B$1))&"'!B:G"),MATCH($A3,INDIRECT("'"&RIGHT(B$1,LEN(B$1)-FIND(" ",B$1))&"'!A:A"),0),MATCH(DATEVALUE(1&LEFT(B$1,FIND(" ",B$1)-1)),INDIRECT("'"&RIGHT(B$1,LEN(B$1)-FIND(" ",B$1))&"'!B1:G1")))
Last edited by FDibbins; 10-31-2015 at 07:54 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I understand the restrictions the layout imposes, and agree it could well be improved. But what I sent reflects what the End User has stipulated.
I did nor respond immediately to Jason or Pete because I was "AFD" for a while and have spent several hours trying to get the approaches to work on the "real" file, without success because of some curious anomolies.
The attached update 151031 Comparisons v2.xlsx which is the actual column structure of the real file, although only a small section of the rows, works perfectly for the "monthly" comparisons until you select April 2017 onwards, From April - Dec the formula pulls up the annual results for 2017.
Secondly, it generates numbers on the "Indirect" bloc from the same point that don't link to any of the sheets,
And thirdly, selecting any of the Quarterly or annual summaries generates an Error Message.
I'm sure it will be a simple "tweak" that I've missed, and will be grateful as ever if someone can point the error(s) out.
Ochimus
But remember, end users rarely understand the real world, while there are exceptions, most tend to fall between housefly and pigeon where common sense is involved.
Which formula was pulling annual results instead of monthly for 2017? I can't reproduce that problem.
Were the numbers from the 'Indirect bloc' somewhere in the range of 42000 to 43000 ? Those numbers would represent the dates used with your data, today = 42309 (number of days from 1 Jan 1900).
Both formula take the month and year from the criteria cell and convert it to a real date in order to identify the correct column. Qtr doesn't represent a real date, which is why it causes errors.
This formula works with your latest example, but I wouldn't want to use it in a real world scenario. Given the amount of processing power this would need with a few thousand rows of data, it's going to more hazardous to the environment that a VW diesel!
I can understand that, we all have to live in te real world
Would you be OK with a few (hidden) helper cells that would break out the month, to shortend the formulas?
I added the columns with the red text.
With the macro below to rearange the data.
After that a pivot table.
See the attached file for the result in the sheet Output (of the sheet ACTUAL).
Sub CONVERTROWSTOCOL_Oeldere_revisted_new() Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet 'check if sheet "ouput" already exist Const strSheetName As String = "Output" Set wsTest = Nothing On Error Resume Next Set wsTest = ActiveWorkbook.Worksheets(strSheetName) On Error GoTo 0 If wsTest Is Nothing Then Worksheets.Add.Name = strSheetName End If 'set the data Set mr = Sheets("ACTUAL") 'this is the name of the source sheet Set ms = Sheets("Output") 'this is the name of the destiny sheet col = 3 'End set the data With ms .UsedRange.ClearContents .Range("A1:D1").Value = Array("Name", "Choise", "date", "value") End With rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row With mr rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row For i = 2 To rsht1 Do While .Cells(1, col).Value <> "" 'And .Cells(I, col).Value <> "" rsht2 = rsht2 + 1 ms.Range("A" & rsht2).Value = .Range("A" & i).Value ms.Range("B" & rsht2).Value = .Range("B" & i).Value ms.Range("C" & rsht2).Value = .Cells(1, col).Value ms.Range("D" & rsht2).Value = .Cells(i, col).Value col = col + 1 Loop col = 3 Next End With With ms .Range("B2:B" & .Rows.Count).SpecialCells(4).EntireRow.Delete .Columns("A:Z").EntireColumn.AutoFit End With End Sub
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Just logged on, and appreciate the input from everyone trying to help me resolve this.
If you look at the v2 file I attached, Col P of the ‘Comparisons’ sheet (Mar-17 Actual) uses Pete’s formula to call the correct data from Col AB of the ‘Actual’ sheet.
Col M (Apr-17 Actual), using the same formula, should call the data from Col AC of the ‘Actual’ sheet. But it actually calls the ‘2017 total’ data from Col BB. Same error happens if you select any criteria (Actual. Forecast or Budget) between April – December 2017. Why it happens on those columns, but not the previous ones, is baffling.
Thanks to Jason for clarifying that the formula doesn’t work with Quarterly or Annual data because it doesn’t look for “text”. Hopefully someone out there knows what to add already?
FD and oeldere suggested adding columns and helper cells to simplify things. I’m happy to take any suggestions, but will have to work out where to put them later on. Like most jobs, this is part of a larger file that pulls data from various sources and drops them into defined ranges. There is absolutely no chance the End User will agree to change the entire layout to make this section easier.
oeldere, thanks for the attachment, but would be grateful if you can explain how it work with the two "Headings" the client wants, because I can't see it at the moment?
Interestingly, I have tried the formula on the “real” file, which has over 1k rows, and had no problems with performance on my desktop which is hardly “overclocked”.
So near, yet so far. . . .
Ochimus
Last edited by Ochimus; 11-01-2015 at 08:32 AM.
Sunday morning caffeine deficiency took effect earlier, I forgot to include the formula in my last post.
Enter this formula in N2 of your V2 sample.
=SUMPRODUCT((INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!A2:A23")=$A2)*(INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B1:BB1")=IFERROR(--LEFT(N$1,FIND(" ",N$1)-1),LEFT(N$1,FIND(" ",N$1)-1)))*INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B2:BB23"))
Regarding efficiency, the problem would arise more with a large number of rows in the comparison sheet than with the volume of data in the other sheets.
Indirect is one of the 'volatile' functions, as such any formula that uses it is recalculated when any action causes recalculation, even if there is no reason for that specific formula to be recalculated.
See http://www.decisionmodels.com/calcsecretsi.htm
The file posted shows the infomation you want?oeldere, thanks for the attachment, but would be grateful if you can explain how it work with the two "Headings" the client wants, because I can't see it at the moment?
On what point you need explained?
How can you be so sure, did you suggest the solutions?There is absolutely no chance the End User will agree to change the entire layout to make this section easier.
Maybe at the beginning the end user is not fond of your solutions.
But as always, if it is also easier for them, finally will embrase your solution.
Last edited by oeldere; 11-01-2015 at 10:01 AM. Reason: changed grammatical
Attached update 151101 Comparisons v3.xlsx has what must be the longest formula I've ever experienced.
Combining Jason's "Quarter and Annual" formula with Pete's original that (almost) worked for the dates, the User can now select the sentence specifying either a Month, Quarter or Year as Actual, Budget or Forecast, and the formula pulls up the relevant data.
EXCEPT that it still pulls the 2017 Total from Col BB of each sheet if you select any month between April - Dec 17.
I've even retyped them from scratch, to no avail.
If someone can just see why it's not "doing what it says on the tin" in those nine months, I can close this out, and you can all enjoy well-deserved plaudits.
Ochimus
The first part of the formula, i.e. before the SUMPRODUCT term, is trying to find a match from the columns B to BB and returning data from those columns, but the data for that part of the formula only goes up to column AK. I suggest you amend the formula in N3 as follows (changes shown in red):
=IFERROR(INDEX(INDIRECT("'"&RIGHT(N$1,LEN(N$1)-FIND(" ",N$1))&"'!B:ak"),MATCH($A3,INDIRECT("'"&RIGHT(N$1,LEN(N$1)-FIND(" ",N$1))&"'!A:A"),0),MATCH(DATEVALUE(1&LEFT(N$1,FIND(" ",N$1)-1)),INDIRECT("'"&RIGHT(N$1,LEN(N$1)-FIND(" ",N$1))&"'!B1:ak1"))),SUMPRODUCT((INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!A2:A65")=$A3)*(INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B1:BB1")=IFERROR(--LEFT(N$1,FIND(" ",N$1)-1),LEFT(N$1,FIND(" ",N$1)-1)))*INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B2:BB65")))
In the second part of the formula, you don't really need to use the range B:BB (shown in blue), though I've left it as it as it should work okay.
You can copy the formula across and down as required.
Hope this helps.
Pete
Combining the formula is probably breaking something, the sumproduct formula works without change.
A couple of the ranges might need adjusting because some parts of your sheet move every time you post a new sample.
Using your v3 sample file from post #16, this formula works in N3
=SUMPRODUCT((INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!A2:A23")=$A3)*(INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B1:BB1")=IFERROR(--LEFT(N$1,FIND(" ",N$1)-1),LEFT(N$1,FIND(" ",N$1)-1)))*INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B2:BB23"))
After entering it, copy the cell, not the formula, and paste to P3
Many thanks for the final tweaks, which means everything functions as it should.
Can now amaze the End User tomorrow with exactly what he wants.
Hopefully the next challenge will be less convoluted
Ochimus
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks