Forum Discussion

DonMirabella's avatar
DonMirabella
Copper Contributor
Oct 16, 2020

Reference Question

I am trying to figure out how to use the Lookup formulas in Excel to get what I need.   I have a 2x100 table of values. The left column, "A", has a series of unique numbers. The right column. "B"...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 16, 2020

DonMirabella 

As variant you may create PivotTable on the range with adding data to data model and create the measure to add the numbers as

Numbes:=CONCATENATEX(Range,Range[Number],", ")

Result is

  • DonMirabella's avatar
    DonMirabella
    Copper Contributor
    Oct 16, 2020

    SergeiBaklan Thank you Sergei, this looks like the way to go for me. However, when I try to setup my own pivot table in my spreadsheet, the "Add this data to the Data Model" check box is greyed out.

     

    I don't see a reason why nor do I see how to change that.

     

    Don

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 16, 2020

      DonMirabella 

      Don, not sure what it could be with data model. Try formula solution, perhaps it'll be easier and not necessary to refresh data even if PivotTable works.

      • DonMirabella's avatar
        DonMirabella
        Copper Contributor
        Oct 16, 2020

        SergeiBaklan I found the solution in a few different posts. Had to save as xlsm then close and reopen.

         

        Now, I'm not able to recreate your pivot table in my sheet. I don't see the function option in Range field selection window.

         

        I'll work on this over the weekend, seems like it'll take some head scratching.

Resources