ADDRESS LISTS

[Quinnshouse]

OK, I need a little help.
I have 1 Column of names, address, phone#, Fax, Email, Etc.: A1,A2,A3,A4, Etc

NOW, NOT ALL ARE THE EXACT SAME AMOUNT OF INFO. some consist of name address phone. Then a blank row then next group might be name address phone email and more.

OK, One good thing is they are seperated by a blank row.

SO I need to get all info from 1st row to blank row in columns b1,c1,d1,etc.

Thanks to USER: Yogi Anand, I know how to get info from one column into rows. BUT I need something that will take all info up to next blank row, then start new row of info....

ANYBODY NOT CONFUSED?


Yogi Anand wrote:

In cell C1, put the following formula
=(("A"&(ROW()-1)*5+1&":A"&(ROW()*5-1)))
and copy this down from c2:c50 to take care of all of your 50 records.

Then in cell D1 write the following formula:
=Transpose(indirect(C1)) -- this is an array formula, clcick on cell D1, highlite cells D1:G1 and array enter the formula in cell D1
this will transpose the entries in A1:A4 to D1:G1

 

[PaddyD]

Hi,

have a look at the following:

Book1
	A	B	C	D	E	F	G	H	I
1	Data	Record Number		Record	1	2	3	4	5
2	a	1		1	a	b	c
3	b	1		2	d	e	f	g	h
4	c	1		3	i
5				4	j	k
6	d	2		5	l	m	n
7	e	2
8	f	2
9	g	2
10	h	2
11
12	i	3
13
14	j	4
15	k	4
16
17	l	5
18	m	5
19	n	5
20
Sheet1

the formula is:

=IF(COUNTIF($B$2:$B$19,$D2)>=E$1,INDEX($A$2:$A$19,SMALL(IF($B$2:$B$19=$D2,ROW($A$2:$A$19)-ROW($A$2)+1,ROW($A$19)+1),E$1)),"")

...which is entered in e2 using control + shift + enter, not just enter, then copied across / down.

Solution depends on the following:

1) setting up a new column to indicate which record set each data element belongs to, using a formula of the form:

=IF(LEN(A2),1+COUNTBLANK($A$2:A2),"")

...in b2 & copied down.

2) Setting up a matrix to return the records to that has, down the columns, a number for each entry, and across the row header, a number for each possible field, u to the maximum number of fields per record. to see what the formula is doing, see the explanation I posted here:

http://www.mrexcel.com/board2/viewtopic.php?t=158655

 

[Peter_SSs]

Quinnshouse

Welcome to the Mr Excel board!

Along similar lines to PaddyD, but a little bit different.

1. Enter a 1 in cell B1.
2. In B2 (copied down): =IF(A2="",B1+1,B1) Note that column B could be hidden if desired.
3. Enter record numbers down column D as in PaddyD's layout. Column D could be hidden if desired.
4. (Edited) Formula in E2:

=IF(COLUMN()-COLUMN($D2)<COUNTIF($B:$B,$D2),OFFSET($A$1,MATCH($D2,$B:$B,0)+COLUMN()-COLUMN($D2)-1,0),"")

5. This formula gets copied across columns far enough to cover the maximum number of entries between blank rows in column A. For the sample data, that means across 5 columns to column I.
6. Then copy row 2 formulas down far enough to cover the number of different records.

Using PaddyD's data, we get:

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1	Data	1		Record
2	a	1		1	a	b	c
3	b	1		2	d	e	f	g	h
4	c	1		3	i
5		2		4	j	k
6	d	2		5	l	m	n
7	e	2
8	f	2
9	g	2
10	h	2
11		3
12	i	3
13		4
14	j	4
15	k	4
16		5
17	l	5
18	m	5
19	n	5
Address List