1

Need to retrieve matching Date, Type, ID, and reference Cost based on user input Date Range. In Data Set 1 and Data Set 2 inputs are not sorted, and the match conditions need to look at if it falls within the Date Range first, check if the Type matches second, and if the IDs match third. If all (3) of these conditions pass it will list them in the Desired Output and pull the associated Cost. If the Cost occur on the same date, it will add them together. Prefer an Excel 2019 formula-based approach over VBA.

enter image description here

Data Set 1 Data Set 2 Date Range Example of Desired Output
Date Type ID Cost Date Type ID Cost 1/1/25 2/28/25 Date Type ID Cost
2/18/25 Apple 123 $5 2/18/25 Apple 456 $30 User Input 1/16/25 Banana 104 $75
2/21/25 Pear 102 $10 1/29/25 Pear 789 $35 1/28/25 Pear 789 $15
1/28/25 Pear 789 $15 1/28/25 Mango 852 $40 1/29/25 Pear 789 $35
2/8/25 Apple 879 $20 2/8/25 Apple 123 $45 2/8/25 Apple 123 $45
1/16/25 Banana 104 $25 1/16/25 Banana 104 $50 2/18/25 Apple 123 $5
7/5/25 Berry 222 $45 10/8/25 Berry 145 $8
8/5/25 Melon 456 $13 5/4/25 Melon 654 $45
3/8/25 Kiwi 852 $18 6/9/25 Kiwi 321 $15
Improve this question
3
  • 1
    Since Excel 2019 does not have FILTER, you can use SUMIFS combined with INDEX and MATCH to get results. Commented Mar 6 at 22:39
  • 1
    Use a Pivot Table instead Commented Mar 6 at 22:53
  • How would you solve it using FILTER? I might just buy excel 2021 just for that feature. Commented Mar 7 at 21:19

2 Answers 2

Reset to default
2

This can also be accomplished using Power Query.

The downside is that you will have to refresh after making any changes in the Datasets.
This could be automated using an event-triggered macro.

To use Power Query

  • Select the table area (first 8 columns) containing only the Data sets and
    Home => Format as Table
    • Change the Table Name to Fruits.
  • Select the Date Range dates and Home => Format as Table.
    • Change that table name to StartEnd
  • Select some sell in the Fruits table.
  • Data => Get&Transform => from Table/Range
  • When the PQ Editor opens: Home => Advanced Editor
  • Paste the M Code below in place of what you see
  • Read the comments and explore the Applied Steps to understand the algorithm
let

//Change Lines 1 and 3 below to reflect actual table names
    #"User Input" = Excel.CurrentWorkbook(){[Name="StartEnd"]}[Content],
    #"Start/End" = Table.TransformColumnTypes(#"User Input",{{"Start", type date},{"End", type date}}),
    Source = Excel.CurrentWorkbook(){[Name="Fruits"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Type", type text}, {"ID", Int64.Type}, {"Cost", Currency.Type}, 
        {"Date2", type date}, {"Type3", type text}, {"ID4", Int64.Type}, {"Cost5", Currency.Type}}),

//Split and filter the data sets
    #"Split Data Sets" = 
        List.Accumulate(
             {0,1},
             {},
             (s,c)=>s & {
                 Table.SelectRows(
                    Table.RenameColumns(
                        Table.SelectColumns(#"Changed Type",
                            List.Range(Table.ColumnNames(#"Changed Type"),c*4,4)),
                        List.Zip(
                            {List.Range(Table.ColumnNames(#"Changed Type"),c*4,4), 
                            List.FirstN(Table.ColumnNames(#"Changed Type"),4)})),
                each [Date]>=#"Start/End"[Start]{0} and [Date]<=#"Start/End"[End]{0})
                    }),

//Join Tables based on Type and ID
    #"Join Table" = Table.NestedJoin(#"Split Data Sets"{0},{"Type","ID"}, #"Split Data Sets"{1},{"Type","ID"},"Join",JoinKind.Inner),

//Stack the tables
    #"Stack Tables" = Table.Combine({Table.RemoveColumns(#"Join Table",{"Join"})} & #"Join Table"[Join]),
    #"Grouped Rows" = Table.Group(#"Stack Tables", {"Date", "Type", "ID"}, {{"Cost", each List.Sum([Cost]), Currency.Type}}),

//Sort by Date
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Interesting thanks for the feedback. I've used PQ a couple times it is very powerful. Any idea how to solve this problem w/ a formula or is PQ the only way (or easiest way) to solve?
@mjac definitely the easiest way in 2019. Using formulas I would try looking at MATCH and AGGREGATE functions. Think it would be pretty complicated.
1

If your output section has for example:

Time Range Start: M3

Time Range End: N3

Type: O4

ID: P4

Use this formula to sum the costs from both sheets based on conditions:

=SUMIFS(F:F, A:A, ">="&$M$3, A:A, "<="&$N$3, D:D, O4, E:E, P4) +

SUMIFS(K:K, H:H, ">="&$M$3, H:H, "<="&$N$3, I:I, O4, J:J, P4)

The first SUMIFS checks Sheet1 (A:A for Date, D:D for Type, E:E for ID, F:F for Cost).

The second SUMIFS checks Sheet2 (H:H for Date, I:I for Type, J:J for ID, K:K for Cost).

Assuming the first output row for Date is in cell Q4, use:

=IFERROR(INDEX(A:A, MATCH(1, (A:A >= $M$3) * (A:A <= $N$3) * (D:D = O4) * (E:E = P4), 0)), "")

Improve this answer

3 Comments

I follow your logic here. But the issue is the Type and the ID are not given to search for. The Output examples in the picture only have the Time Ranges the function must search for matches of the Type and ID on its own. The algo needs to scan the Time Range then look for matches between Sheet1 and Sheet2 if it finds matches then list them in the Output.
@mjac, maybe I misunderstood you, but the same logic can be applied.
I clarified the question more and modified the picture and eliminated the sheets to make it more straight forward.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.