add data to model option for building unique count pivots ...

[cujstoner]

Hi

hi! 

 I am using excel version 16.44 on a MAC. 

I am trying to build a pivot table that will return unique counts. The add to data model option is not available in my version. 

I am dealing with a large amount of data that has duplicates and I want to pivot and sum the unique count of values.  

Any thoughts on why from the group? 

Thank you!

Courtney 

[#2]

@cujstoner Not sure what you want to count. Currently your pivot table counts "Tag 1", not "Review ID" which is in column A.

With regard to Data Model, that's NOT supported by any Excel version for the Mac, unfortunately. Windows only!

[#3]

Yes. At the review ID level, display in a row the list of tags and then count 1 (unique or distinct) occurrence.

In the raw data the review ID is ascribed to a unique set of information. There are several rows with the same review ID and various tags that are repeated in each row. 

I had hopes of creating a pivot and do unique count on the values so I could arrive at a view of review ID, list of tags per review ID and if there was one occurrence.

Since Mac does not have this feature I was also considering building a master data table off of the raw data with a series of lookups. But I'd have to lookup along columns and rows.

any thoughts?

 @Riny_van_Eekelen 

[#4]

@cujstoner I'm sure you have a clear picture in your head with regard to the end result. Though, I'm not seeing it (yet). Perhaps you can describe the logic you wish to apply. For instance, the first 6 items in the raw data (rows 2:7) share the same Review ID. Five possible reviewers, one Sentiment per item and five possible tags. What would you want to count for this one Review ID?

[#5]

@Riny_van_Eekelen 

I find an illustration is often helpful when explaining these scenarios.  I re-uploaded my file with an illustration on sheet 1.

What I want: 

1) I'd like to take the data in row 8 to row 11 that I have highlighted in gray and extract key information and summarize it in one row.

2) I'd like to remove duplicate information which may exist in the same column or different columns to create a summary of unique info in 1 row. 

3) I want to do this for the entire file.  Pivots do not seem to work. Any thoughts?

Thank you,

 @cujstoner

[#6]

@cujstoner Try with "Get&Transform Data" (a.k.a. Power Query). The attached file contains an additional sheet called "NewTable". I believe it answers to your needs.

Edit: Oops forgot you're on a Mac. Then Power Query is out of the question, although you can extend the data and refresh the new table as the query connects to a local table. Let me see I can replicate the steps without PQ.

[#7]

@cujstoner I'm not particularly proud if it, but have a look at the attached (updated) file. In quite an ugly manner I joined unique tags per Review ID (for the first four in your list), though its presentation depends on the number of rows that exist within each group of Review ID's. And the end results doesn't look like your example either, but at least it gives you all the comments made in the Tag columns, without repeating them, for each Review ID.

Perhaps acceptable. If not, seriously consider investing I acquiring Excel for Windows. Either, on a stand-alone PC or on a virtual machine on your Mac. The latter is what I have been using the past year.

[#8]

@Riny_van_Eekelen 

thank you. I did not realize the limitation re the Mac. Your response helped greatly. thanks so much!