kurthatlevik: D365FO – Some nice excel tricks

[Blog bot]

Источник: https://kurthatlevik.com/2018/06/01/...-excel-tricks/
==============

When working with importing master data into Dynamics 365 you will experience that they are available in different data entities. In a typical retail project you would need to import data like released products, item barcodes, external item numbers price. It is also common that we get the master data in many files and in different formats. It is therefore quite beneficial to know a few tricks so that it becomes easer to work with loads of data. Here are my tips.

Export all/selected rows (You should know this!)


From any grid in D365FO you can export selected/all rows to excel by right clicking on the grid. The tip is therefore to make a personalization to the grid, so that it contains the fields you want to export to excel.



Then Excel opens with the selected columns. (PS! This export is limited to 10.000 rows)



Use excel to create a filter


Let’s say we have a excel spread sheet with item numbers, and want to filter in D365FO on these items. Here is a very valuable tip.

1. Copy the items column from excel and paste as rows in a new excel sheet.(Transpose)

1. Then copy the row, and paste into notepad
   
   
2. Then do a search, replace in notepad, where you copy the space/tab and replace it with comma (,)
   
   
   
   
3. Then copy the content here and use it on a “match” filter in D365FO
   
   
    
   
4. Then you have created a filter on the selected field. It seams the “match” filer is capable of handling quite a lot of text.
   
   

This is nice when some asks you to “Please fix these 200 items”. You then filter them and quite quickly go through them to fix it.

Learn Excel VLOOKUP


VLOOKUP is essential to learn, because it let’s you check and lookup data across multiple excel sheet. A typical scenario in the retail world is when the vendor sends a new pricelist, and you want to import them. Often this is delivered as a excel sheet with the vendor item number, item barcode and the price. Most retailers prefers to have their own item numbers. But then you have the issue of mapping the item barcode from the vendor pricelist and trying to find your own product number. Here is how I recommend my customers to do it:

1. Export all D365FO item barcodes to excel (There is an entity for this, or open the barcodes from the retail menu)
2. In the vendor excel price list, create a VLOOKUP field to lookup the D365FO product number based on the item barcode.
   
   
3. Then you can create an excel sheet where you have your own product number, and you can import them using “open in excel” or through a data management import job.
    
   
    
   

Happy weekend friends !



Источник: https://kurthatlevik.com/2018/06/01/...-excel-tricks/