Updating equipment locations

Question

Hello!I'm trying to create a equipment tracker that lists field numbers for equipment under columns labeled as specific locations.&nbsp;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".&nbsp; 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!

tripl1337 · Answer

Here are examples of the above post.&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FIELD #&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TO6-JunTRIPLETTGT12D1D146-JunTRIPLETTHY01C3D86-JunTRIPLETTB405D2D116-JunTRIPLETTC118D7C36-JunTRIPLETTGT05C11C76-JunTRIPLETTHT27C14D156-JunTRIPLETTHS06D15D26-JunTRIPLETTNC90D17C36-JunTRIPLETTB190D8C76-JunTRIPLETTFL07C9SHOP6-JunTRIPLETTFL14C7SHOP&nbsp;&nbsp; &nbsp; &nbsp; D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D11&nbsp; ( Small example of columns )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

riny_van_eekelen · Answer

TripL1337&nbsp;I believe you can achieve this by pivoting the data based on the TO column. The attached workbook contains four examples.&nbsp;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.&nbsp;The second option is to use Power Pivot, using a DAX measure that will allow to put texts into the value field.&nbsp;The third example is done by Power Query and the last one uses dynamic arrays formulae.&nbsp;Hopefully, you'll find one of these useful.

Forum Discussion

Updating equipment locations

2 Replies

6-Jun	TRIPLETT	GT12	D1	D14
6-Jun	TRIPLETT	HY01	C3	D8
6-Jun	TRIPLETT	B405	D2	D11
6-Jun	TRIPLETT	C118	D7	C3
6-Jun	TRIPLETT	GT05	C11	C7
6-Jun	TRIPLETT	HT27	C14	D15
6-Jun	TRIPLETT	HS06	D15	D2
6-Jun	TRIPLETT	NC90	D17	C3
6-Jun	TRIPLETT	B190	D8	C7
6-Jun	TRIPLETT	FL07	C9	SHOP
6-Jun	TRIPLETT	FL14	C7	SHOP