Jun 12 2022 08:33 AM
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!
Jun 12 2022 07:36 PM - edited Jun 12 2022 07:37 PM
Here are examples of the above post.
FIELD # FROM TO
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 |
D1 D2 D7 D8 D9 D11 ( Small example of columns )
Jun 12 2022 10:50 PM
@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.