Viewing 6 posts - 1 through 6 (of 6 total)
  • Excel help needed
  • northernmatt
    Full Member

    I have a spreadsheet containing just shy of 10k products that all have a varying price increase. I can copy it all into one sheet but once I try to put that into our POS system it goes into meltdown as it can’t handle the amount of data being thrown at it.

    What I want to do is split the sheet down into sheets with only around 500 items per sheet. Ideally in individual files so I can process them into the POS system one by one. I’ve had a google but all I can see is how to do it by column data.

    Help me out please as I don’t want to have to sit here copying and pasting all day.

    edlong
    Free Member

    If you’re splitting 10k into 500 item blocks, that’s only 20 chunks, I can’t see how you could turn that into an all day task – you’re not in the public sector are you 😉

    Various ways of doing it – save the master list 20 times and remove a different 19/20ths from each one – copy 500 line chunks from the master list into 19 empty spreadsheets (and probably the header row as well). Slice into 20 different sheets in the same file and then send each sheet into a new workbook. Not sure any one is quicker, but none should take terribly wrong, unless I’m missing something from the brief?

    PlopNofear
    Free Member

    Could do some VBA to copy and paste rows to new sheets.

    Otherwise I’d add in a new column with row count, add a filter in. Filter in chunks of 500 and copy and paste that way. Probably the quickest way.

    Stoner
    Free Member

    I would create an “Uploader” template file.

    At the top of the template, set some fields to the filename and sheet name of the master product list workbook.

    Have one cell as a free variable number.

    You can then make a load of copies of the template

    Then use INDIRECT and OFFSET (populated by the location field names at the top of the sheet) across the worksheet to return a batch of data from the master product list in 500 row chunks, using the free variable number to specify the start of the batch. Change that number in each of the uploader sheets.

    Once each uploader sheet is populated, then ctrl+a, ctrl+c, Alt e v s,

    and then its a clean data sheet of small file size ready for squooshing into your system. It may work without the copy & pasting, but indirect and offset take up a lot of memory.

    Shouldnt take more than 5 mins to do.

    northernmatt
    Full Member

    Cheers guys.

    edlong – not public sector but I’m good a procrastination so I’d be well suited to it 😀

    gearfreak
    Free Member

    Public sector would need to employ someone on a temporary contract to retype all the data into the POS. This would take at least 3 months to get the position through HR and recruitment, by which time the data would be out of date and the position would be made permanent.

Viewing 6 posts - 1 through 6 (of 6 total)

The topic ‘Excel help needed’ is closed to new replies.