Help me with this one please!!

O

oball23

New member
Joined
Oct 8, 2014
Messages
5
Reaction score
0
Points
0
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!
 

Attachments

  • EHMC VENDORS.xlsx
    211.6 KB · Views: 26
  • SOH VENDOR.xlsx
    208.9 KB · Views: 14
You dont explain the problem or your expected results, just problem there, need more clarify

thanks
 
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
 
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.
 
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!
 
oball23 said:
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!
Click to expand...

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
 

Attachments

  • SOH VENDOR_solution.xlsx
    128.8 KB · Views: 12
excelmate said:
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
Click to expand...

wow you are amazing - i am so thankful to you!
 
oball23 said:
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!
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top