Posts by wsuengineer

    I need help mapping data from a source excel file to a new format. I have attached an excel file with the general formatting and some narrative (top row), but I need to incorporate some error checking and also need to export CSV files that would all be best done via some coding. Here is how I need it to work:

    • I place an unformatted (source) excel file (currently this is the same as the “source data” sheet in the workbook) in the same folder as the template excel file. The template workbook contains the code.
    • I open the template excel file and enter some information on “global data” sheet. After I manually enter the information, I hit a button that formats the source excel file. Remember the source excel file is contained in the same folder as the template. The formatting is shown and explained in the top row and seen on the “formatted data” sheet.
    • After the data gets formatted, I need to create a PO export. There will be up to 5 PO export csv files. Each csv export will be formatted as shown on the “po export” sheet. I need a PO export for each unique value in column M (start ship date) from the source data. The cvs files will be named: Global Data B1 and the value of the ship date (e.g. Global Sweatshirts – 10.7.2020). The data contained in each PO export will the data associated with that ship date.
    • After the PO exports are created, I need to export the formatted data as a csv. During the export I need to check the UPC against the UPC database. If the formatted data contains a upc that already exists in the UPC database, then that row needs to be removed from the export. After duplicates are deleted then all of the new UPC codes need to be added to the end of the upc database. I also need to export the UPC data base sheet (with the new data) with todays date to create a backup.
    • The template file needs to be maintained though this process as I will run this file on the daily to map data and maintain an updated UPC database.

    See attached file for further clarification.

    I have a spread-sheet that reports transactions from multiple vendors and then a balance. Any vendor that has a balance of zero i would like removed from the spreadsheet along with the corresponding rows of transactions. I have a few thousand rows with vendors and balances, but I only need to look at ones with a balance (either positive or negative), so removing vendors without a balance will clean up spreadsheet so it can be evaluated. See the attached example for specifics.

    sample data.xlsx

    Transaction ID: 4NS81174KL2792718

    Re: Search A Cell For Mutilple Text Values

    Thanks you for your replies. Both methods work perfect but incase some else looks at this thread later there are a couple things I noticed:
    VB Method
    • Really easy
    • Case sensitive
    • can only do about 100 entires, otherwise it takes a while to run
    Method with Built in excel functions
    • Extremely fast (did 30000 entries in a matter of seconds)
    • Can adjust case sensitivity
    • MUST push F4 after wordlist
    • can have search two or more consecutive words

    Once again thanks for your time and effort

    I am trying to find a way to search a cell or a combination of cells, for key words that are in a separate list. The separate list is dynamic, meaning it will be changed, expanded, or contracted at various times.

    I would also like the search to return a specified numeric value to a different cell if it finds one of these words. If it does not find one of the words I would like it to return a separate specified value.

    I attached an example to help make things more clear.