formula/marco to run "IF" to adjust rows

[gpicot]

Hi.

I have exported names and addresses from another program into excel spreadsheet for a few thousand people. The data runs vertically down the sheet for each person as follows:

Cell A1 - Name
Cell A2 - Address Line 1
Cell A3 - Address Line 2

Cell A5 - Name
Cell A6 - Address Line 1
Cell A7 - Address Line 2

Lets call the three cells an 'address block'. For each name, i would like there to be six rows until the next address block starts. In some cases it is already six however sometimes its five or four. To manually format it will take me days because there are thousands.

I would like to know of a formula/macro that can either insert or delete a row accordingly to comply with the above. Something like a macro with an if statement - i'm not too sure. Any help would be greatly appreciated


Im using Excel 2007 version.

 

[iliace]

One technique to do this, I believe was described by Mr. Excel somewhere, makes this relatively painless, especially if the number of lines for each customer is the same and they're in the same order, and you can tell where the new customer begins.

I can't find it right now, but it goes something like this.

The approach is: you create two columns, one called Group, another Sequence. The Group column numbers each customer; the sequence column numbers each line of information for each customer. You need some kind of a condition for going to the next group; I used blank line in a list (looks like you can do the same), in the following example:

Excel Workbook
	A	B	C
1	Group	Sequence	*
2	1	1	Montgomery County Executive
3	1	2	Ike Leggett
4	1	3	Executive Office Building
5	1	4	101 Monroe Street, 2nd Floor
6	1	5	Rockville, MD 20850
7	1	6	(240) 777-2500
8	1	7	FAX (240) 777-2544
9	1	8	*
10	2	1	Montgomery County Council
11	2	2	Stella B. Werner
12	2	3	Council Office Building
13	2	4	100 Maryland Avenue
14	2	5	Rockville, MD 20850
15	2	6	(240) 777-7900
16	2	7	FAX: (240) 777-7989
17	2	8	*
18	3	1	*
sheet (2)

Then you filter for each sequence number, copy the data cells, and paste them on a new sheet, each in its own column. The result for my example looks like this:

<b>sheet (3)</b><table border="1" cellspacing="0" cellpadding="0" style="font-family:Tahoma,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:89px;" /><col style="width:105px;" /><col style="width:69px;" /><col style="width:87px;" /><col style="width:73px;" /><col style="width:94px;" /><col style="width:98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; ">Montgomery County Executive</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; ">Ike Leggett</td><td style="font-family:Arial; font-size:10pt; ">Executive Office Building</td><td style="font-family:Arial; font-size:10pt; ">101 Monroe Street, 2nd Floor</td><td style="font-family:Arial; font-size:10pt; ">Rockville, MD 20850</td><td style="font-family:Arial; font-size:10pt; ">(240) 777-2500</td><td style="font-family:Arial; font-size:10pt; ">FAX (240) 777-2544</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; ">Montgomery County Council</td><td style="font-family:Arial; font-size:10pt; ">Stella B. Werner</td><td style="font-family:Arial; font-size:10pt; ">Council Office Building</td><td style="font-family:Arial; font-size:10pt; ">100 Maryland Avenue</td><td style="font-family:Arial; font-size:10pt; ">Rockville, MD 20850</td><td style="font-family:Arial; font-size:10pt; ">(240) 777-7900</td><td style="font-family:Arial; font-size:10pt; ">FAX: (240) 777-7989</td></tr></table>

It's advisable to copy the group number for each sequence number, so that you can later check to make sure none of the data got mixed up.

 

[gpicot]

Thankyou for replying iliace i like your solution. However, this poses one problem as the number of rows between each group differs. So when i tried appplying this it started a new group on a blank row. Would you know of a solution to get around this?
thanks

 

[iliace]

Are any of the rows identified? In other words, you'll have to look for some pattern that will always tell you what each line is. For example, each blank line is followed by the name of the next person. Each phone number begins with a parenthesis for area code. Each line before the phone number is an address line. Each fax number is preceded or followed by the word "fax". Whenever there's an e-mail address, that cell will contain a @ symbol. The last address line always has the form of [city], [state] [zip]. And so forth.

Draw out those rules. Then, come up with formulas to identify each of them (or post back for help if you cannot). And follow the same process.

Another possibility is, if you can identify each "group" of information (meaning, each person), is to filter for those who have fewer or more sequence numbers, and to insert placeholders for those. If there are only a few, this will work. If about 50% of the records are missing, say, 2nd address line, it's not worth the effort.