Question about Dynamics Business Central & Excel

Question

Hi- I am a beginner using Microsoft products and have been at my job 5 months. My boss gave me a new task which is to... using a an Excel master file we have which list every inventory item we have and the cost and pricing etc. All anyone know to do is to enter each item # manually in dynamics to check that the cost listed there matches our "Excel Master File" - this could take months! If I export my data from Dynamics to an excel sheet also is it possible to somehow compare the data against one another and say return any differences? I'm trying to learn Vlookup but with that don't all the columns/cells have to line up with eachother?

Can I take a chunk of item numbers at a time and somehow use the whole exported data spreadsheet as my "table array" and ask it to look for this list of numbers on the master file?

If someone could help I would really appreciate it- It is such a specific answer I can't find answers online.

Thank you in advance

Answer 1

Hi @Melissa Obrien

I will give some explanations on my formula.

This formula is based on the premise that the data structure on each sheet of master file is consistent.

Then let's split this formula =VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:b"),A2),"'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:H")),7,0).

• The first Indirect forumula: INDIRECT("'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:b" creates a reference to the column B area of the 3 sheets in my example. You can add all sheets of Master file in this formula with the same format. '[Master.xlsx] is the Master file name with symbole '; 57 DSAM5 (REXC2 30 01) is one of sheet names; '!b:b is the rang of column B on these sheets with symbole '.
• Then we use COUNTIF to count the number of times the "Iterm No." of cell A2 (Dynamics file) appears in column B of each worksheet of Master file: COUNTIF(INDIRECT("'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:b"),A2) This part returns a memory array like {0,0,1}, you can run this formula seperately to have a check. The number of times other than 0 is the worksheet where the "Iterm No." is found.
• The format like: Lookup(1,0/(conditional), range) can return a text expression corresponding to the above worksheet range, such as: '[Master.xlsx]60 RRSP (RPNA13401)'!b:H. Please note, you can change the sheet names as yours, and 'b:H can be changed as your data range on these sheets to search.
• The second INDIRECT formula: INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:b"),A2),"'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:H")) is to transfer the text expression above to be a reference, and use this as the scope of the Vlookup formula query.
• At last: Vlookup(A2, the second INDIRECT formula, column number, 0) can return the data you want. For Vlookup function, please refer to this link.

Answer 2

Hi @Melissa Obrien

I want to help you, but could you please share us a simple sample data?

As it's better to we give you formulas based on a sample, which will answer you clearer.

If I export my data from Dynamics to an excel sheet also is it possible to somehow compare the data against one another and say return any differences? I'm trying to learn Vlookup but with that don't all the columns/cells have to line up with eachother?

As you said, you can use Vlookup function to find the data from another sheet. If there are existed data, you want to compare it with returned data, please use format conditions to hightlight the differences.

------- Modify ------

Here is my sample, if it is not your need, please let me know and provide more details.

Following images are the sheets of Master file. These sheets have the same data structure which means "Part No" are all in column B, the "Mat'l Cost" are all in column H, and other information is in the cells with the same column number.

The following image is using Vlookup and Indirect functions to find the values from Master file for listed "Item No." in Dynamics file.

Formula is =VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:b"),A2),"'[Master.xlsx]"&{"57 DSAM5 (REXC2 30 01)","60 RRSP (RPNA13401)","70 SS (RENK 14)"}&"'!b:H")),7,0).

Please Note! This formula works under the case, that you need to find the Mat'l Cost values. It is an array formual, you need to press Ctrl + Shift + Enter.

Please check whether such formula can help you, if so, I will add more explanation about this formula.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 3

Thank you so much for trying to help me. Hopefully I can paste an example and explain better.

Pasted above is an example page from our "master file" I am taking each part number listed in column B and in Dynamics under "Items" I search each part number individually to make sure what we have listed in our Dynamics item card matches our material cost from the "master file". This project could take months looking up each item one at a time. Someone had mentioned to me "Vlookup" but as you can see the rows/columns do not match up at all.

I am lost and am trying to learn everything I can to do my job better and would appreciate any help at all. The company switched 1 year ago to Dynamics from Quickbooks but over the years and the different software's I have worked with I have never not had a help desk to call?

above the "master file" is the data that exported from Excel so there is an example of that also

Answer 4

Hi Emily,

Because I am so new to Excel is there any way of giving some kind instructional steps to the equation - I am so excited about this formula and yet so scared at the same time, I don't feel very confident.

Thanks for all your help!

I really appreciate it!