Get result with code replacing the formula

    Hello Experts


    I am trying to get the invoice number from Sheet A which contain "-Total" to the B Sheet with the help of a formula. I am getting a lot of blank cells in sheet B in between rows. After many attempts I am not able to get it with a formula. I am not so good at coding. I need your expertise in 2 issues to get the values with the help of a code.

    I need your help to get the full values of the invoice number cell even if it starts from 0 for instance, 001-Total in Sheet A should display as 001 in the B sheet. If possible get the ID along with the Invoice number.

    I would really appreciate it if anyone can help me to correct the issue and instead of a formula. make it possible to get the result with the help of a code.

    Test Data.xlsx

  • Go to Best Answer

    Here is a power query solution


    Code
    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1],"Total") then [Column1] else null),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Custom] <> null)),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","-Total","",Replacer.ReplaceText,{"Custom"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Custom"})
in
#"Removed Other Columns"
    • Best Answer

    Try the attached, click the button on sheet A


    Code assigned to the button

    kjBox. Thank you very much for sharing the code. It is perfect. I didn't understand the Table part. Is it actually necessary to convert it into table ? Can you please comment at each line of code.? I try to understand and learn from the comments and that will be helpful for me to edit and use the same for different projects and different rows and columns too.

    If I want to add one more row say column 5 from sheet A which is in date format dd-mm-yyyy to column 2 in B Sheet then it will be easier for me to edit the code and get the result. Please share the solution for the date and the rest of the columns I will try to edit myself referring your code if they are commented.

    My original data has 18 columns in sheet A and N number of rows out of which I have to get data from sheet A to sheet B in around 10 columns in different columnar format and the rest of the columns contain formula.

    alansidman thanks for the response. I actually don't have any idea about power query. I use only formulas and VBA code for my projects. Thanks once again.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.



    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.



    - Follow this link for an introduction to Power Query functionality.



    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Your file has an Excel Table on Sheet A already, not essential to have an Excel Table on Sheet B, though it would be better if it were one.


    I can edit the code to include a Date column. But I suggest it would be far simpler if you post a sample of your actual workbook and indicate exactly which columns need to be transferred to Sheet B.


    About 5 rows of data will be sufficient,you can use dummy data, but keep any formulas and formatting.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Thanks 1

    KjBox. I have succeeded in getting the required data in the columnar format as needed. If I face any problem I will let you know. Thanks man,

    ok np, well done

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    KjBox. Is there any way I can share an attachment in private.? The code will show an error if I share a dummy data and the original data I will have to avoid privacy issues. Please let me know..

    You can attach a file to a private message.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Where is the option to send a private message ? Please send me a private message. That way I will know how to send private message and I will reply back with the file.

    I could not find the private message. So, I am sharing the link of the file here. Please help me to solve the problem mentioned in the code button sheet.

    to KjBox.xlsm
    drive.google.com

    KjBox. If you are looking into it and have any doubts to clear please leave a message. I am working on the progress bar and if you can just correct and get the invoice number error right, it will be a great help.

    I have the file, I will see what I cam do.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Thanks KjBox. Please see that if you edit the code, it should be compatible to old versions of excel too.

    ok


    If you need to send a PM the click the "double chat bubble" icon at top right of page, then click the "+" to start a new message.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Quote from KjBox

    I have the file, I will see what I cam do.

    Hello KjBox. I have replaced the sum product formulas with sumifs and the time taken to generate xml now is less than 1/2 the time taken earlier.. That leaves only one issue to be solved -the date like invoice number issue. Hope you are still trying to resolve it. 🤞

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login