How to Track movement of Duplicate Code from One sheet to another one.

moumita77

I have 2 sheets 1st is Asset Master sheet and 2nd is Material Movement sheet. An index match formula is implemented on Material movement sheet to get data from Asset Master sheet like Asset code, Asset name, Asset description. However there another Index match formula applied on the 1st sheet i.e. Asset Master sheet to get other details from Material Movement sheet. There is an case for circular reference but its been tackled. There are more than one asset code present in the Material movement sheet, which makes it difficult to fetch the latest Asset code and populate in the 1st sheet i.e. Asset Master sheet. The Index Match Formula is applied on first data of that particular Asset Code on the Material Movement sheet. My question is, How do we get the latest information using the Index match formula of the same Asset code.

Attach 2 pictures one of 2nd sheet Material movement where there are 2 transaction of the same asset code where the latest " To Location" is km but the formula is only fetching the older data of the same asset code of "To Location" i.e. My home on the Asset Material sheet which been circled with blue color.

Paul Newcome

Try this instead:

=INDEX(COLLECT({Location}, {Code}, @cell = [Asset Code]@row), COUNTIFS({Code}, @cell = [Asset Code]@row))

Basically the INDEX/COLLECT creates a list of all locations for that Asset Code. Then we count how many times that Asset Code is present in the other sheet, and that number dropped into the "row index" portion of the INDEX function will have it pull the last one from the COLLECT list.

Paul Newcome

Try this instead:

=INDEX(COLLECT({Location}, {Code}, @cell = [Asset Code]@row), COUNTIFS({Code}, @cell = [Asset Code]@row))

Basically the INDEX/COLLECT creates a list of all locations for that Asset Code. Then we count how many times that Asset Code is present in the other sheet, and that number dropped into the "row index" portion of the INDEX function will have it pull the last one from the COLLECT list.

moumita77

Thanks Paul for guidance and explanation. The formula works perfectly fine.

moumita77

@Paul Newcome Just one more question in the Asset Master data sheet once the "Current Location Final" gets transferred from Material Movement sheet to Asset Master Data sheet. How to interchange the cell value of "Current Location Final" to "Current Location" in Asset Master Data.

For e.g. Once the "Current location Final" gets populated using the Index Match Formula from Material Movement. How to interchange the cell value of "Current Location final" to "Current location" Cell value.