Hopefully an easy solution

[keef2]

Hi there,

Just curious of the best way to fill the data into the appropriate cells. I cannot think of the best formula to write to carry this info into the new format needed. Thanks in advance!

Here is a sample of a small portion of my data list:

Working Job List.xlsx
	I	J
1	JOB NAME	LOCATION
2	South Lake Industrial #3260	600 Darling Drive, Vernon Hills, IL
3	Northpoint - Avenue O (Commerce Park Bldg B) #3313	3300 East 122nd Street, Chicago, IL, 60617
4	Syngenta Project Mustang #3332	2125 State Route 38, Malta, IL, 60150
5	Army Trail Trade Center Bldg 1 #3340.1	55 W Army Trail Road, Glendale Heights, IL, 60139
6	Army Trail Trade Center Bldg 2 #3340.2	85 W Arny Trail Road, Glendale Heights, IL, 60139
7	North Aurora Golf Course Bldg A #3348	314 Kingswood Drive, North Aurora, IL, 60542
8	Cold Summit Development #3352	7101 S Leamington Avenue, Bedford Park, IL, 60638
9	AMZ XLFC (ORD6) Project Pie #3358	11400 Venture Ct, Huntley, IL, 60142
10	LPC Palatine Spec 350K SF Distribution #3371	623 E Algonquin Road, Palatine, IL, 60173
11	LPC Oak Forest Spec 660K #3372	**NEED**, Oak Forest, IL
12	LPC Aurora OD #3374	4275 Ferry Road, Aurora, IL, 60563
13	LPC Aurora - Diehl Road #3375	2800 W Diehl Road, Aurora, IL, 60502
14	Project River #3376	23301 South Central Avenue, University Park, IL, 60484
15	ORD Fuel Company LLC Building #3377	10000 W O'Hare Street, Chicago, IL, 60666
16	PRG - Laraway & Marcella - 639K SF #3382	275 Laraway Road, Joliet, IL
17	Gas N Wash C Store Roofing & ACM Panels #3387.1	1930 Airway Ct, New Lenox, IL, 60451
Job# Order

Cell Formulas
Range	Formula
I2:I17	I2	=TEXTJOIN(" #",TRUE,B2,A2)
J2:J17	J2	=TEXTJOIN(", ",TRUE,C2,D2,E2,F2)

Here is the format I am looking for:

Working Job List.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	JOB NAME:										TIME:
2	LOCATION:
3	JOB NAME:										TIME:
4	LOCATION:
5	JOB NAME:										TIME:
6	LOCATION:
7	JOB NAME:										TIME:
8	LOCATION:
9	JOB NAME:										TIME:
10	LOCATION:
11	JOB NAME:										TIME:
12	LOCATION:
13	JOB NAME:										TIME:
14	LOCATION:
15	JOB NAME:										TIME:
16	LOCATION:
17	JOB NAME:										TIME:
18	LOCATION:
19	JOB NAME:										TIME:
20	LOCATION:
Sheet1

 

[keef2]

This is what i did to get it all one row then just did simple = formula to carry down to the format i want. Is there a better way just curious?

Working Job List.xlsx
	S
7	South Lake Industrial #3260
8	600 Darling Drive, Vernon Hills, IL
9	Northpoint - Avenue O (Commerce Park Bldg B) #3313
10	3300 East 122nd Street, Chicago, IL, 60617
11	Syngenta Project Mustang #3332
12	2125 State Route 38, Malta, IL, 60150
13	Army Trail Trade Center Bldg 1 #3340.1
14	55 W Army Trail Road, Glendale Heights, IL, 60139
15	Army Trail Trade Center Bldg 2 #3340.2
16	85 W Arny Trail Road, Glendale Heights, IL, 60139
17	North Aurora Golf Course Bldg A #3348
18	314 Kingswood Drive, North Aurora, IL, 60542
19	Cold Summit Development #3352
20	7101 S Leamington Avenue, Bedford Park, IL, 60638
21	AMZ XLFC (ORD6) Project Pie #3358
22	11400 Venture Ct, Huntley, IL, 60142
23	LPC Palatine Spec 350K SF Distribution #3371
24	623 E Algonquin Road, Palatine, IL, 60173
25	LPC Oak Forest Spec 660K #3372
26	**NEED**, Oak Forest, IL
27	LPC Aurora OD #3374
Job# Order

Cell Formulas
Range	Formula
S7:S156	S7	=TRANSPOSE(TOROW(I2:J76))
Dynamic array formulas.

 

[Fluff]

You could use

=TOCOL(I2:J76)

 

[fjns]

Hi, try this formula for S7
=INDEX(I:J,INT((ROW()-3)/2),MOD(ROW()-1,2)+1)

JobList.xlsx