Is this even possible in Excel?

Question

Hello all,The business has asked for a report where they can view side-by-side&nbsp;details of similar products produced by different vendors, for analysis purposes. The data extract I receive from my developers is in a format similar to the first tab of the attached file (named, Data Extract). The data is a snippet of potential allergens per product and whether or not those allergens are present in the product, or are present on the processing line or facility. The requested report format is on the second tab (named, Report Request).A text-based Pivot table would be a lovely, but alas, I'm outta luck (some of the data is free text; so, I can't create numeric ID's and take advantage of conditional formatting).I need to keep the solution in Excel, as there are other tabs on the report that contain related product data. Any suggestions are greatly appreciated. Thanks!Kind regards,Kelly E.

detlef_lewin · Answer

Try this great trick from Bill Jelen.https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/&nbsp;File with solution attached.&nbsp;

wyn hopkins · Answer

Hi Kelly,No attachment came through

kelly evenson · Answer

Shoot ... my apologies, Wyn. I loaded the attachment, but it somehow became unattached before I posted the question. Let's try this again.

kelly evenson · Answer

wow ... thank you kindly, Detlef! I don't have much experience with PowerQuery; so, that step took some time to figure out. And then I kept running into a circular reference error whilst creating the measure. Geesh ... somehow finally cleared that one up. But, this is great knowledge and much needed, as I currently have seven report requests that require the same&nbsp;type of formatting.Thanks again for your help!

Forum Discussion

Is this even possible in Excel?

Resources