Hi all,
I have a list of data that is constantly added to which we use to compare pricing on.
What I am trying to achieve is that a monthly report is pulled showing items order within each calendar month and the price that they cost the last time they were ordered (historical order data is held on another sheet)
My raw data table with the history of orders is setup as below. Table named RawData
Column K is equated using formula =TEXT([@[Event Date]],"mmmm/yyyy") to create the monthly report on a second worksheet which is setup as below:
Using the dropdowns in B2 & D3 gives the Date Search result which pulls all results through from the RawData which match this in column K. Works perfectly.
In column K & L of the report table, I need to lookup the item no from Column F on the Report from Column F on the RawData table and return the most recent result. I think I have achieved this by using the formula =LOOKUP(2,1/(RawData[[#All],[Item No]]='Customer Info'!F5),RawData[[#All],[Unit Price]]) in K5.
The problem that I am facing is that the most recent result is the one that I am pulling for this report, which is not what I want. In essence I need it to pull the most recent occurence EXCLUDING any results which match the Date Search (February/2022 in this instance) in column K of the RawData table. I am really struggling to work out how to do this.
I hope I have explained this clearly enough, but let me know if more detail is required.
Thanks for any help that you can give!
I have a list of data that is constantly added to which we use to compare pricing on.
What I am trying to achieve is that a monthly report is pulled showing items order within each calendar month and the price that they cost the last time they were ordered (historical order data is held on another sheet)
My raw data table with the history of orders is setup as below. Table named RawData
Column K is equated using formula =TEXT([@[Event Date]],"mmmm/yyyy") to create the monthly report on a second worksheet which is setup as below:
Using the dropdowns in B2 & D3 gives the Date Search result which pulls all results through from the RawData which match this in column K. Works perfectly.
In column K & L of the report table, I need to lookup the item no from Column F on the Report from Column F on the RawData table and return the most recent result. I think I have achieved this by using the formula =LOOKUP(2,1/(RawData[[#All],[Item No]]='Customer Info'!F5),RawData[[#All],[Unit Price]]) in K5.
The problem that I am facing is that the most recent result is the one that I am pulling for this report, which is not what I want. In essence I need it to pull the most recent occurence EXCLUDING any results which match the Date Search (February/2022 in this instance) in column K of the RawData table. I am really struggling to work out how to do this.
I hope I have explained this clearly enough, but let me know if more detail is required.
Thanks for any help that you can give!
