Creating and using a pivot table for this purpose

**jilaba** · 01-26-2021, 09:21 AM

Ref. Attachment FORUMQ.XLSX

In Sheet "Entry" column D has Crop Sublist Entries.
Blue Colored table has Crop Group Definition in Column G, sub group ie, the entries of D column are classified in H:O columns.

Using pivot table (OR something else) i want to get a abstract list as in Sheet "Result".

ie, if the Crops entered in D column of Sheet ENTRY, it should search in blue color table and .....

1. C Column entries to be concatenated as per the grouping.
2. D column entries to be condensed as per Crop Group.
3. E column Entries to be summed as per crop Group.

How to do this? any way without programing knowldege?

thanks in anticipation.

**hansolu** · 01-27-2021, 02:40 AM

Hi

I tranformed both, the entry and crop group into tables and loaded into Powerquery. Then in Powerquery I unpivot the Group table and merge it with the entrys. After merge I create a new custom column with

Please Login or Register  to view this content.

Then I extract the values out of the new List and choose the seperator for the values. Rest is just to format the column headers, remove the unused column.

The rawdata itself is not clean, meaning after merge some items are not found as the merge is case sensitive. So I added a steps for the Entry and Crop table to make them uppercase first.

Thats all possible with the UI of powerquery and without programming.

Good luck

**jilaba** · 01-27-2021, 03:42 AM

Originally Posted by hansolu

Hi

Great. Thanks a lot. I thought i could use the idea in my worksheets simply, like understanding and applying formulas. But i do not know how to use powerquery. Moreover my office version is not compatibe with powerquery. Can you please help me by creating a pivot table, like if i could paste in input table, when refreshed i get the results?

**hansolu** · 01-27-2021, 06:29 AM

Hi,

in 2013 you can also download the powerquery addin as far as i know. Did you try this?
Otherwise, as I dont use 2013 I cant help.

If you want to build the pivot table only with formulas, thats a bit tricky, specially in 2013.

**jilaba** · 01-27-2021, 06:44 AM

Hi hansolu,

Thanks for your solution, answer, time. You got my point simply, In short, I am looking for formula based solution.

Creating and using a pivot table for this purpose

LinkBack

Thread Tools

Rate This Thread

Display

Creating and using a pivot table for this purpose

Re: Creating and using a pivot table for this purpose

Re: Creating and using a pivot table for this purpose

Re: Creating and using a pivot table for this purpose

Re: Creating and using a pivot table for this purpose

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Power pivot or pivot table for connecting data and creating calculated fields

[SOLVED] Need Help: Creating Simple MRP for Production Purpose

Creating a Pivot Table from Power Pivot Data Model?

creating a table that will link to a pivot table and change as the pivot table is changed

creating a table that will link to a pivot table and change as the pivot table is changed

PIVOT Calculation for Certain Purpose

Pivot Table - Automated Data... Learning Purpose

Bookmarks

Bookmarks

Posting Permissions