Results 1 to 8 of 8

Thread: Help me with this one please!!

  1. #1

    Help me with this one please!!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Okay so I have a brain teaser for you Excel Wizards:

    I have these two attached spreadsheets that I need in this format:

    Vendor's Name /Address1/ Address2 to mail merge and send letters

    Simple as that HOWEVER, all the data is in one vertical line, mixed in with telephone numbers, and other random data. the vendor's name is indicated with a small box.

    Can you please help me out?

    Thanks!
    Attached Files Attached Files

  2. #2
    You dont explain the problem or your expected results, just problem there, need more clarify

    thanks

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    First I don't think you should be publishing vendor details on an open forum.
    Second I don't think that this is going to be solved by simple formulas but you will need to use code.

    I would suggest read the whole block into an array
    Then find the start and end of each block
    Then look for
    a) Is the block, ASC(120) in it. If so the vendor ID is to the right (maybe :-) )
    b) Does it contain PO Box?
    c) Does it have P; or F; in it (and some of them have both on one line)
    d) Is it all numbers and -, yes its tel or fax
    e) Get a list of all postcodes and see if the post code matches
    f) look for St, Dr, Ave etc.
    g) Failing that addresses, address 2 etc.
    That's just some of the "Rules" on a quick look

    Once you know which of the "rules" it's easy to pack it into a new sheet

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Hello
    I agrree with everything Wizard has said, and would add the following:
    1. Your main problem is that your data is not "computer friendly" due to a lack of consistency. Usually, this affects a quite small proportion of a typical dataset.
    2. If your free to make changes without breaking things you could try sorting the data in order to enable you to pick out data thats not needed, or that needs
    an amendment to make it consistent.
    3. To do this, using EHMC as an example, number the rows from 1 to 14807 in col C to preserve your original sort order.
    4. Sort Cols A to C using Col A as the key.
    5. Inspect Col A and either:
    (a) Ignore records and unwanted empty rows by adding a couple of zeros to the number in Col C
    (b) Correct any inconsistencies that stand out
    (c) Re-Sort A to C using C as the Key.

  5. #5

    Thanks Guys but a little more help PLEASE!

    Thank you very much for your help WizzardofOz I agree I should not have posted vendor information but didnt know how else to get my point across. I have never used an array before, is there a way the array can be explained in lay-man terms? I am sorry for the hassle.

    Hercules1946- Thanks for your help as well, once I do a sort to filter out unwanted cells, I cant sort back because the address information does not have any unique identifying information. is there anyway to make this data in equal horizontal cell breaks perhaps every four cells? then I can run an offset formula.

    Please advise- thanks again evertone!

  6. #6
    Quote Originally Posted by oball23 View Post
    Okay so I have a brain teaser for you Excel Wizards:

    I have these two attached spreadsheets that I need in this format:

    Vendor's Name /Address1/ Address2 to mail merge and send letters

    Simple as that HOWEVER, all the data is in one vertical line, mixed in with telephone numbers, and other random data. the vendor's name is indicated with a small box.

    Can you please help me out?

    Thanks!
    For a start, the data layout is quite hideous which does not help. One thing to do is make sure that all the phone numbers appear below the detail for each vendor. This makes the next bit easier. Where there is a phone number don't leave a gap otherwise leave one blank row.

    Add a couple of columns to the left of your data - let's give them headings "group" and "sequence".
    In A2 type the number 1
    In A3 type the formula =IF(LEFT(D3,1)="V",A2+1,A2) and copy down. Each group of lines for 1 vendor will have a different number.
    In B2 type formula =IF(A2=A1,B1+1,1) this will give each line in the group a sequential row number.
    Copy/paste special values both columns A and B and sort on column B.
    Cut and paste the newly grouped data to the right of the top sequence group. This will finally give you all your data in single rows allowing you to mail merge easily.

    Hopefully sample file attached

    Excelmate
    Attached Files Attached Files

  7. #7

    Talking

    Quote Originally Posted by excelmate View Post
    for a start, the data layout is quite hideous which does not help. One thing to do is make sure that all the phone numbers appear below the detail for each vendor. This makes the next bit easier. Where there is a phone number don't leave a gap otherwise leave one blank row.

    Add a couple of columns to the left of your data - let's give them headings "group" and "sequence".
    In a2 type the number 1
    in a3 type the formula =if(left(d3,1)="v",a2+1,a2) and copy down. Each group of lines for 1 vendor will have a different number.
    In b2 type formula =if(a2=a1,b1+1,1) this will give each line in the group a sequential row number.
    Copy/paste special values both columns a and b and sort on column b.
    Cut and paste the newly grouped data to the right of the top sequence group. This will finally give you all your data in single rows allowing you to mail merge easily.

    Hopefully sample file attached

    excelmate
    wow you are amazing - i am so thankful to you!

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by oball23 View Post
    Hercules1946- Thanks for your help as well, once I do a sort to filter out unwanted cells, I cant sort back because the address information does not have any unique identifying information. is there anyway to make this data in equal horizontal cell breaks perhaps every four cells? then I can run an offset formula.

    Please advise- thanks again evertone!
    If you follow my instructions, you can sort the data back in the original sequence. An extra numbered col C is suggested to make this possible. I did miss one thing though - To ensure unwanted data is moved to the bottom on re-sort, as there are 14807 rows you would need to add 100,000 to the sequence number in Col C instead of my suggestion to add two zeros.

Tags for this Thread

Posting Permissions

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