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
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.

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

      DonMirabella 

      I added measure in Power Pivot, preliminary assigned in it Text type to Number column

      • mtarler's avatar
        mtarler
        Silver Contributor
        Oct 16, 2020

        DonMirabella  another option if you need the numbers in separate cells.

        use this equation (which is probably similar or same as previously posted to find the unique names):

                    Note: I defined dRange as column B on the Source Tab

        = IFERROR(OFFSET(dRange,AGGREGATE(15,7, ROW(dRange)/(--(COUNTIF(OFFSET(dRange,ROW(dRange)-1,0,ROWS(dRange),1),dRange)=1)), ROW())-1,0),"")

         

        and then paste this and drag down and to the right as far as needed:

        = IFERROR(OFFSET(dRange,AGGREGATE(15,7, ROW(dRange)/(--(dRange=$L1)), COLUMN()-COLUMN($L$1))-1,-1),"") 

        This assumes the Unique list column is in L1 and this is in column M

Resources