How to transfer large volumes of name address data but filtering other irregular data

**Aussie62** · 08-14-2014, 02:06 AM

I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped. Help! This task is urgent and I don't have much time

BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
Fax: 07 5777 3622 Web: www.bDD.com.au
AIR COMPRESSORS:
AIR CONDITIONING - AUTOMOTIVE (PARTS-SERVICE-GAS):
CLOTHING:
PANEL SHOP & SPRAY EQUIPMENT:

**rcm** · 08-14-2014, 02:17 AM

do all non desirable line end with :?

**Aussie62** · 08-14-2014, 02:18 AM

Yes it seems to

**Aussie62** · 08-14-2014, 02:19 AM

Thank you for helping me

**Aussie62** · 08-14-2014, 02:33 AM

I can send a sample if this helps. I dont know how the excel spreadsheet im working from was formatted originally

**Aussie62** · 08-14-2014, 02:45 AM

Actually the data goes on beyond :

CV JOINTS & BOOTS: Supplying quality new & remanufactured ..........

Sorry this is so difficult

**macropod** · 08-14-2014, 03:30 AM

Are these data already in Excel, or in a text file, for example? Are there any empty rows between records?

**Aussie62** · 08-14-2014, 03:32 AM

Yes data is already in an excel spreadsheet but not compiled by me

**Aussie62** · 08-14-2014, 03:34 AM

I guess I am trying to clean up the data before I transfer it to another excel spreadsheet

**Aussie62** · 08-14-2014, 03:35 AM

No there are no empty rows between records

**Aussie62** · 08-14-2014, 03:43 AM

there is basically no consistency in how the data is formatted

**macropod** · 08-14-2014, 04:23 AM

Originally Posted by Aussie62

No there are no empty rows between records

That pretty much makes it impossible for a macro to determine where one record ends and the next one begins...

If you insert at least one empty row between records, the following macro can be used for a clean-up:

Please Login or Register  to view this content.

If you have other parts of the records you might want to keep (e.g. Mob: 0401 123 456), you could add more parameters like "Mob:" to the two Select Case statements.

**Aussie62** · 08-14-2014, 07:18 PM

Thank you macropod. I have never used a macro before so this is abit daunting

**macropod** · 08-14-2014, 07:35 PM

See: http://www.contextures.com/xlvba01.html

**Aussie62** · 08-18-2014, 07:25 PM

Thank you Macropod for all your help. It's been invaluable.

**peterpeterson** · 12-04-2017, 04:02 PM

How can I calculate the volume of a sphere with Excel and use functions in xlsm format? Please halve me I'm already desperate!

**63falcondude** · 12-04-2017, 04:53 PM

peterpeterson, unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Not only that, but your post has nothing to do with the topic of this thread.

How to transfer large volumes of name address data but filtering other irregular data

LinkBack

Thread Tools

Rate This Thread

Display

How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Re: How to transfer large volumes of name address data but filtering other irregular data

Thread Information

Users Browsing this Thread

Similar Threads

Filtering Large Data

Excel 2007 : Filtering large amounts of data

Need help inputting LARGE volumes of data

filtering out dups in address col. keeping the other data to the left and right of it

data filtering based on last two digits of large number

Bookmarks

Bookmarks

Posting Permissions