Importing TXT file into Excel with random line breaks

[da_houghsta]

Hi all,

I am currently doing a project with work, to give you the back story we have an online store that I am trying to quicken the process of updating quantities to match the in-store POS system. Right now I have it set up so that I can do an export from MYOB and an export from Shopify, which has barcodes and by using VLOOKUP, it looks for the barcode in the shop export and records the number 17 rows across which is the qty. It works and takes probably 10-15min to do the lot, much better than manually doing each product individually.

Now, my issue is, that since there is a lot of staff that enter in products at the store, for some strange reason at the end of some product descriptions, there is a line break. When I export all the products (about 14000 barcodes), this puts the data to the right of the product description onto the next row. When I run the VLOOKUP code, it returns a "0" as there is nothing in that cell. I am still new to all of this, this is the code I have been using

=VLOOKUP(D2,'[Stock 170416]Stock'!A:Q,17,FALSE)

There are just too many of these to fix in the system, for now, as it's over 1000 (but will be done eventually), so is there a way, after importing into excel, to get it to search and if no data in Column C, it is to cut all the data from the row below, column A to AF and paste into the correct spot? I tried doing it manually in the txt file and took a very long time. The other option I thought of was to amend the code above so that if the cell is empty, it returns the number in the 15th column of the row below. Does this make sense? Obviously the best way is to fix MYOB in the first place so that it doesn't export the data with the line breaks in the product description, but I need something that will work for the time being.

Thank you!

Ben

[JBeaucaire]

If you can export from the program, spend the time to first fix the exported descriptions then immediately import them BACK into the program to update the original faulty descriptions. Then your next export won't have this problem any longer.

[da_houghsta]

If you can export from the program, spend the time to first fix the exported descriptions then immediately import them BACK into the program to update the original faulty descriptions. Then your next export won't have this problem any longer.

Yep, I aggree this is the best way, but it's 1104 products out of 14000. It would have to be done out of opening hours as it takes a couple hours to fix and for now just looking for something to work in the meantime. Appreciate it though. I'm also not sure what's caused this in the first place and it may happen on new products that will be entered in.
I did find something that worked by the way,

=IF(VLOOKUP(AM1,A:Q,17,0)="",INDEX(A:Q,MATCH(AM1,A:A,0)+1,15),VLOOKUP(AM1,A:Q,17,0))

Barcode is in Column A, QTY in Q and the barcode list that I needed values for was in AM. Then I copy the AN list and paste into the other exported spreadsheet, overriding the numbers there.

(I ended up copying and pasting the barcodes into an empty column on the spreadsheet to make it easier on me). Basically it looks for the cell, if it's occupied it returns the value. But if it's empty, it returns the value 1 row down and 2 columns to the left (product description is in column B and it's at the end of this that I get the random line break, meaning the QTY column is in O, not Q. Ive run it through about 1000 barcodes so far and haven't noticed an error. Took me a while to work out and trial and error. ..

[JBeaucaire]

Trial and error is a fine path to "attaboy". Good job. Thanks for posting your resolution.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.