+ Reply to Thread
Results 1 to 5 of 5

Export report from software to excel and cells don't work with sumifs and vlookup

  1. #1
    Registered User
    Join Date
    11-21-2017
    Location
    Australia
    MS-Off Ver
    Excel 2016 MSO 32bit
    Posts
    3

    Export report from software to excel and cells don't work with sumifs and vlookup

    Hi everyone,

    I'm from Australia and we use an accounting software that can export data in excel format which is fine.

    However, when I want to work with the data in the cells the cells don't work, like if the export gave them a weird format that I can't see and I can't get rid of.

    I have tried everything, copy/paste special, copy/paste to new excel sheets, trim, concatenate, &, clear format and I cant solve the problem.

    Please find attached 2 files showing a small example.

    1. MYOB sample - is the file I am working with column A and B, and the rows 7 - 12 have this weird problem but rows 15, 16 and 17 work just fine
    2. COASUM1 - MYOB - is the actual export from this software called MYOB

    If someone could help me making rows 7-12 workable for sumifs and vlookups I would really really appreciate it

    All the best,
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,130

    Re: Export report from software to excel and cells don't work with sumifs and vlookup

    welcome to the forum. perhaps you could be clearer by inputting the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

    i'm guessing you want to remove the prevailing spaces seen in rows 8 to 12.
    go to cell B8.
    click inside the formula bar and copy just one of the space you see by pressing CTRL + C
    press ESC to exit the formula bar
    press CTRL + H to Find and Replace
    in Find, press CTRL + V to input the space
    Replace with nothing. Press Replace All

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,730

    Re: Export report from software to excel and cells don't work with sumifs and vlookup

    There is a null value after each code, try using LEN(A7) to test.
    There are few option:
    * F2 to edit the cell
    * use helper column with =LEN(A7,6) to get correct code, then paste value back to A7
    * Use "?" to replace null string like this:
    with E5 content criteria value "4-0000"
    =SUMIF($A$7:$A$17,E5&"?",$C$7:$C$17)
    Or
    =SUMPRODUCT((LEFT($A$7:$A$17,6)=E5)*$C$7:$C$17)

  4. #4
    Registered User
    Join Date
    11-21-2017
    Location
    Australia
    MS-Off Ver
    Excel 2016 MSO 32bit
    Posts
    3

    Re: Export report from software to excel and cells don't work with sumifs and vlookup

    Hi guys,

    Thank you for your prompt replies.

    bebo021999, I tried your suggestions and it didn't work

    benishiryo, I am attaching the current file I am working on (i tried to delete and hide a lot of unuseful information). - MYOB sample II

    Basically, I want the formula on "FI7" to return an amount using sumifs that are reading from "A7" (concat) that is reading from the weird cells in "C" and "D" ("C7" and "D7" are correctly being read by the formula)

    To illustrate my point, I copy/pasted the same information that is working row 7 but in the weird cells format in row 9 and the formula is not working.

    thank you so much for your help!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-21-2017
    Location
    Australia
    MS-Off Ver
    Excel 2016 MSO 32bit
    Posts
    3

    Re: Export report from software to excel and cells don't work with sumifs and vlookup

    Thank you for the prompt reply

    I am attaching "MYOB sample II" file I am working on (I tried to delete and hide a lot of unuseful information)

    Basically, I want the formula on "FI7" to return an amount using sumifs that are reading from "A7" (concat) that is reading from the weird cells in "C" and "D" ("C7" and "D7" are correctly being read by the formula)

    To illustrate my point, I copied/pasted the same information that is nicely working row 7 but in the weird cells format in row 9 and the formula is not working.
    Its like row 9 is possessed, like the cells are dead or useless(also I am attaching part of the export from the MYOB software)

    Thanks again for your help!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 10-24-2017, 04:54 PM
  2. VLOOKUP, INDEX/MATCH, SUMIFS...cant make it work
    By kdk616 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2015, 01:34 AM
  3. Repetitive SUMIFS that don't work with new cells
    By Jp4Real in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-19-2014, 11:14 PM
  4. help with macros to export report to excel
    By Jacobmat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2014, 06:23 PM
  5. Export report from PDF to Excel
    By mkvassh in forum Excel General
    Replies: 1
    Last Post: 09-03-2009, 07:47 AM
  6. How can I export address data from Excel to Palm Pilot software?
    By Stephanie at Bright Prospect in forum Excel General
    Replies: 1
    Last Post: 06-14-2006, 03:00 AM
  7. Export Access Report to Excel
    By poppy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2005, 04:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1