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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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
54,322
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,991
Messages
5,834,769
Members
430,319
Latest member
Excelhelppll

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top