Posts by Strawbie

    Thanks for replying to this, Carim.


    My code does indeed work perfectly for the data structure as explained in the opening post. Other text in the rtf that can be in random positions will cause the code to fail. One solution would be to have a list of all the "other texts" in the rtf file, the code could then be modified to include a check to see if any text is in the list, if it is then such text can be ignored.


    Your code is brilliant, its the data causing a problem.

    I have written a macro to delete all the random text.


    Problem now is there are instances where there are two blanks cells between customer data, not just one. There are empty text boxes in the rtf and they are being copied over as a blank cell value.


    I can delete all blank cells in column A but that breaks your code.


    Im trying to work out how to find two blanks and delete one of them. If that fails, i will try to adapt your code to work without blanks in the column


    Its a nightmare, no idea why they would store this data in an rtf!

    Hi,


    Would you mind attaching a sample file with an extraction of your workbook ..:wink:


    I would love to but it contains lots of customers personal contact details.


    I've found the problem, i think. There is some other data from the rtf that copied over at random places and that throws the script off.


    Im trying to work out how to loop backwards through the column, check values and delete cells that have value like "*page*", "member", etc.

    Hi Carim,


    Yes that is the structure. Starting in cell A1, Five cells of data then blank cell (A6) five cells of data, blank cell again.


    I have started again, new workbook and copied over just a few customers.


    Mow getting an error: subscript out of range. Debug is highlighting the line with


    y(ii, lcnt) = x(i - 1 +ii, 1)

    Hi KjBox, thank you for this.


    I tried it but its not quite working. The first row is correct. The subsequent rows are offset.


    Eg. First row starts with customer 1 number
    Second row starts with customer 2 name
    Third starts with customer 3 address
    Fourth starts with customer 4 post code
    Fifth starts customer 5 notes
    Sixth starts with customer 7 number


    Can it be adjusted?

    Hi all,


    I have been given a .rtf file with thousands of customer records that my boss wants transferred to excel. The data is not in a table, it's a strange mix of text boxes and drawn lines.


    I can copy and paste to Excel but all the data is pasted into a single column and i need to separate the data into a single row for each customer. There is some uniformity, each customer has five cells each and the customers are separated by a blank cell. My sheet looks like this:


    a1. Customer 1 account no
    a2. customer 1 name
    a3. customer 1 address
    a4. customer 1 post code
    a5. customer 1 notes
    a6. blank cell
    a7. Customer 2 account no
    a8. customer 2 name
    etc.


    I can manually select cells A1 to A5 and then copy/transpose to cell B1 then keep going for each customer but that will take a long time with the amount of data.


    Is there some way I can do this with VBA? E.g. is it possible to remove the blank cells then incrementally copy/transpose blocks of five cells for each customer into separate rows until there is no more data in column A?


    We are using Excel 2010 at work.


    Hope you can help as this is way beyond my skill level!