Forum Discussion

marty007's avatar
marty007
Brass Contributor
Jul 07, 2021

Riny_van_Eekelen 

After I posted my question, I realized it is a lot more complicated than I thought. Here are the details. I have attached the spreadsheet. However; I understand if this is too much work for you and you would rather not work on this any further. I do appreciate your original response but I asked the wrong question, sorry.

 

I am trying to populate the Region column in the myFinl_Data tab                                  

I need 3 regions, Northern, Southern, Home                                                                                

I have a Store_Id that rolls up to a Parent_ID in the Stores tab                                                    

The Store_IDs 2,3,4,7,8,11,15,16,17,18,19,22.29,31 need to be in the Northern region which is Parent_ID 21                                                                                                                            

The Store-IDs 5,6,10,12,13,14,20,23,24,25,28,27,28,30,32 need to be in the Southern region which is Parent_ID 9                                                                                                            

The Store-ID 1 needs to be in the Home region which is Parent_ID 0                                         

The Store_IDs 9,21 also need to be in the Home region which is Parent_ID 1                           

 

Parent_ID 21= (Northern)   If city= Baltimore, Raleigh,Washington,Wilmington enter Northern 

Parent_ID 9=(Southern)                  if city= Philadelphia,New York,Jersey City enter Southern   

 

Note: Jersey City appears as Parent_ID 9,0,and 1. The Regional offices (Parent_ID 1) are supposed to roll up into the Home region (Parent_ID 0)                                                                  

My thought was to change the name of Jersey City with Parent_IDs 0 or 1 to JerseyCity M, to avoid conflict with the other Jersey City store                                                                              

0,1=Jersey City M, region Home   

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 07, 2021

marty007 

If I understood the logic correctly, you may define helper range for the regions

and use

=IFERROR(INDEX($H$21:$H$23, MATCH( INDEX(Stores!$B$2:$B$33, MATCH(D2,Stores!$A$2:$A$33,0)), $G$21:$G$23, 0)), "not defined")

Please check attached.

      • marty007's avatar
        marty007
        Brass Contributor
        Jul 09, 2021
        Thank you very much. this helps me try to make sense of the logic. I appreciate all your help.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 09, 2021

      marty007 

      Perhaps that will be bit easier if we convert data in structured tables. Let name them

      Stores

       

      Data and Regions

      Convert formula to use structured references and apply some formatting

      =IFERROR(
    INDEX(Regions[Region],
          MATCH( INDEX(Stores[Parent_ID],
                 MATCH( [@[Store_ID]],
                         Stores[Store_ID],0)
                 ),
                 Regions[Parent ID], 0)
    ),
 "not defined")

      Internal MATCH returns position of current Store_ID in table Stores.

      Taking that position INDEX returns value of Parent_ID in table Stores

      Next MATCH finds position of returned Parent_ID in the table Regions

      Upper INDEX returns Region value in Regions table for the record number returned by previous MATCH

      Finally we wrap by IFERROR which returns some text if nothing was found, i.e. internal formulae returned an error.

       

      Please check in another two sheets in attached file.

Resources