#### Quinnshouse

##### New Member
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

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.

Mr Excel.xls
ABCDEFGHI
1Data1Record
2a11abc
3b12defgh
4c13i
524jk
6d25lmn
7e2
8f2
9g2
10h2
113
12i3
134
14j4
15k4
165
17l5
18m5
19n5

Replies
7
Views
153
Replies
2
Views
310
Replies
3
Views
143
Replies
7
Views
225
Replies
0
Views
78

1,212,133
Messages
6,106,132
Members
447,996
Latest member

### 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.

### Which adblocker are you using?

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

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