Column to table

[jlajla24]

I have a list of data that's all in a column

The current format is
Company name
(empty cell)
Street address
City, State, Zip
Phone number
(empty cell)
(empty cell)
REPEAT ABOVE

I have about 1000 rows in this one column. That yields about 165 individual entities. Is there an easy way to convert this data to a table with a header for each of these entries? That way each entities information would be on a single row that becomes sortable? It's less than ideal in the current format. Thanks!

 

[Toadstool]

Hi Jlajla24,

This should do what you ask

Jlajla24.xlsx
	A	B	C	D	E	F
1	ACME Ltd		Company	Address	City, State, Zip	Phone number
2			ACME Ltd	22 Arcane Ave	Waco, TX 62626	222-455-4555
3	22 Arcane Ave		Widget Inc	1 WidgetLane	Houson, TX, 34343	333-555-8882
4	Waco, TX 62626		Fildon Corp	1 Fildon House	Hendin, TX, 22222	333-555-8383
5	222-455-4555
6
7
8	Widget Inc
9
10	1 WidgetLane
11	Houson, TX, 34343
12	333-555-8882
13
14
15	Fildon Corp
16
17	1 Fildon House
18	Hendin, TX, 22222
19	333-555-8383
20
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=IF(INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-6,1)="","",INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-6,1))
D2:D14	D2	=IF(INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-6,1)="","",INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-4,1))
E2:E14	E2	=IF(INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-6,1)="","",INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-3,1))
F2:F14	F2	=IF(INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-6,1)="","",INDEX($A$1:$A$9999,((ROW()-ROW($I$1))*7)-2,1))

 

[jlajla24]

Absolutely perfect solution. I was able to use this across several worksheets that had different patterns. Thank you so very much.

 

[Toadstool]

Absolutely perfect solution. I was able to use this across several worksheets that had different patterns. Thank you so very much.

You're welcome.
Thanks for the feedback but be aware I left a bug in... That first check for empty cells should, of course, use the same -6, -4, -3 and -2 values.