Question about Dynamics Business Central & Excel

asked 2023-01-11T21:41:38.91+00:00
Melissa Obrien 0 Reputation points

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

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
60 questions
Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
1,563 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,333 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. answered 2023-01-12T07:11:13.5266667+00:00
    Emily Hua-MSFT 21,526 Reputation points Microsoft Employee

    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.

    Capture32

    Capture33

    Capture34

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

    Capture35

    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.



  2. answered 2023-01-16T15:41:42.74+00:00
    Melissa Obrien 0 Reputation points

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

    Screenshot 2023-01-16 09.25.44

    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


  3. answered 2023-01-26T16:31:20.4733333+00:00
    Melissa Obrien 0 Reputation points

    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!

    No comments