Updating equipment locations

New Contributor

Hello!

I'm trying to create a equipment tracker that lists field numbers for equipment under columns labeled as specific locations.

 

Each column has a name such as D1, D2 etc..

On a separate sheet, data is logged every day which shows what units were moved with a 'From column and a 'To' column. For example..

Each row with have Field # "GT01" From "D1" To "D11".  I need the location sheet to add the Field # to each location column when the data is enter. However, I also need it to remove the Field # from the previous location, specified in the "From" column. If anyone has any ideas how to do this, I'd greatly appreciate the help. Thank you!

2 Replies

Here are examples of the above post. 

                                                                               FIELD #               FROM                     TO

6-JunTRIPLETTGT12D1D14
6-JunTRIPLETTHY01C3D8
6-JunTRIPLETTB405D2D11
6-JunTRIPLETTC118D7C3
6-JunTRIPLETTGT05C11C7
6-JunTRIPLETTHT27C14D15
6-JunTRIPLETTHS06D15D2
6-JunTRIPLETTNC90D17C3
6-JunTRIPLETTB190D8C7
6-JunTRIPLETTFL07C9SHOP
6-JunTRIPLETTFL14C7SHOP

 

      D1            D2           D7            D8            D9            D11  ( Small example of columns )

      
      
      
      

 

@TripL1337 I believe you can achieve this by pivoting the data based on the TO column. The attached workbook contains four examples.

 

The first is a regular pivot table that marks each field in the correct column with the number one as regular pivot tables can't hold texts in the value field.

 

The second option is to use Power Pivot, using a DAX measure that will allow to put texts into the value field.

 

The third example is done by Power Query and the last one uses dynamic arrays formulae.

 

Hopefully, you'll find one of these useful.