Requesting assistance with dynamic list with 2 way lookup

Copper Contributor

I am trying to make a summary table from a larger dataset.  I've attached a fictitious sample of data that I've used to conceptualize my desired output.  I am using index small and match functions in my formula, however, the output is not as it needs to be.  

 

Can anyone review my formulas and identify why and how I can get it to list only the desired output.  I think the issue is with the nested condition in the formulas but I'm not sure what to do.

12 Replies

Hello Darnelle

 

See attached file. With a few helper cells - to reduce the complexity of the formula.

 

Wow.  Thank you very much for this response.

 

Would it be possible for you to explain the aggregate formula that you used

- particularly the latter part of the formula with the mmult and row 

---- why is it row ($1:$12)?

---- what does the exponent do?

---- what does the -6 at the end refer to?

 

I provided only a fictitious simplified sample and planned to use the response to my inquiry in my true dataset, which is a far greater set of data with more than 100 rows and with column dates headers ranging from Jan 2015 to present

- given this information, do you anticipate any changes to your response/formulas?

---- at the moment I'm thinking I may need more helper rows and tables since my desired output will be more than the one table with similar conditions

 

 

Thanks again

This truly is helpful

Q: Why is it row ($1:$12)?

A: Press F5, type C35 and ENTER.

 

Q: What does the exponent do?

A: Get a blank worksheet. Type in A1 any number. Type the formula =A1^0 in B1. Change the number in A1 several times. n^0=1.

 

Q: What does the -6 at the end refer to?

A: Your data starts in row 7 and so do the cell references in the formula. ROW() would return row numbers from 7 to 16. By subtracting 6 the smallest number would 1 - the first row in your data array.

 

Generally you should use the formula evaluation which steps to every calculation and operation in a formula.

Thanks again for the reply.

I am attempting to update the formulas you shared for use with my live data

 

Unfortunately, I don't understand the first answer that you gave.

- In the formula, you shared part of the formula had "row($1:$12)",

---- Why are these rows selected?

---- If I have more rows in my reference table, how does this part of the formula change, if at all?

---- When following the prompts you listed (Press F5, type C35 and ENTER) there wasn't any output for me, I'm not actually sure what I was supposed to see.

 

Thanks again

I look forward to your reply.

I put an explanation in cell C35.

 

Thanks again.

 

Question: how should I update the formula to match more than one column?

For example if for each time frame there is a different status for the project 

 

I've updated the attachment to show what I mean.

The formula in L32:L36 has to be amended.

=AGGREGATE(15,6,1/(1/MMULT(ROW($A$7:$A$16)*($C$18:$R$18)*($C$7:$R$16=TRUE)*($A$7:$A$16=$D$27)*($I$7:$I$16=H$26)*($J$7:$J$16=I$26);ROW($1:$16)^0)),K32)-6

Thanks again for being so responsive.

 

This new formula does get the desired output; but when the month of interest changes I would want to reference columns q and r, instead of the static reference to i and j.  How can I achieve that?

 

 

I think it's time for a complete change. No more cross tabular model but instead a record list model.

The use of Power Query is advised.

Would you agree?

 

I suppose, since my query is getting a bit complex, right?

However, I am not sure how power query works.

There may be a learning curve for that.

For the question at hand can you advise?

I will look into learning more about power query.

I had a solution but then I found a better one and then I found an ever better one and then I found an even better best one. :)

I works like this:

Copy Sheet1 (3) to Sheet1 (4).

Concatenate the three header rows in row 7.

Select A7:R17 and format as a table (CTRL-T). Give the table a name: tbl_Crosstabular.

Import the table to the query editor and transform it. See the steps in the query editor.

Give the query a name: qry_Unpivoted_table

Load the query as a connection only.

Back to the query editor and create a reference of the query and pivot it. See the steps in the query editor.

Give the query a name: qry_Pivoted_Table

Load the query into a new worksheet.

Copy the table to another new worksheet as values only.

Select the header row and the data rows and format as a table (CTRL-T). Give the table a name: tbl_Basedata.

Add the columns Difference Revenue and Difference Profit with the respective formulas.

This will be your new base data list. Add new data, change and edit data only in this list.

Delete every previous table and query. The are not needed anymore.

 

And now for the finish:

Insert a pivot table:

Divison, Month, Lead, On/Off, Difference Revenue and Difference Profit as a report filter. Alternatively insert slicers for each of the fields.

Apps in the rows area.

Actual Profit and Actual Revenue in the values area.

 

Bonus:

Load tbl_Basedata to the query editor and transform it. See the steps in the query editor.

Give the query a name: qry_Unpivoted_Basedata.

Load the query as a pivot table and add it to the data modell.

Month and Type in the columns area.

Division and Apps in the rows area.

Rightclick on the query name in the pivot table field list -> Insert measure.

Measure name: Value2.

Meaure formula:

=CONCATENATEX(qry_Unpivoted_Basedata,qry_Unpivoted_Basedata[Value],"")

Click on OK.

Add the measure to the values area.

Remove the grand totals.

 

Thank you again for the response.

 

I will have to wrap my head around all of this but so far it seems to work.

Possibly some follow-up questions to come.