ADDRESS LISTS

Quinnshouse

New Member
Joined
Nov 18, 2005
Messages
5
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi,

have a look at the following:
Book1
ABCDEFGHI
1DataRecord NumberRecord12345
2a11abc  
3b12defgh
4c13i    
5 4jk   
6d25lmn  
7e2
8f2
9g2
10h2
11 
12i3
13 
14j4
15k4
16 
17l5
18m5
19n5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,473
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=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
ABCDEFGHI
1Data1Record
2a11abc  
3b12defgh
4c13i    
524jk   
6d25lmn  
7e2
8f2
9g2
10h2
113
12i3
134
14j4
15k4
165
17l5
18m5
19n5
Address List
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,129
Members
412,305
Latest member
Mozz
Top