Combining Different Data

[Jenawade]

A coworker is trying to combine some data and I didn't have any suggestions for her. Worksheet1 has data in columns A & B, hundreds of lines long, then worksheet2 has 3 more columns of data (might have been 4), also several hundred lines long. She needs to combine then into one worksheet. I don't remember the exact column headers, but basically, worksheet1 has someting like site and dept, and worksheet2 grade, status, OUC. For each line on worksheet1, she needs it multiplied enough times to be paired up with the lines on worksheet2. Is there an easy way to do this?

FROM THESE 2 LISTS:

Excel Workbook
	A	B	C	D	E	F
1	SITE	DEPT	*	GRADE	STATUS	OUC
2	LIV	029	*	8	I	54
3	WIX	531	*	8	A	89
4	FAR	268	*	9	A	523
5	*	*	*	7	I	61
6	*	*	*	16	A	47
Sheet1

INTO ONE LIST:

Excel Workbook
	I	J	K	L	M
1	SITE	DEPT	GRADE	STATUS	OUC
2	LIV	029	8	I	54
3	LIV	029	8	A	89
4	LIV	029	9	A	523
5	LIV	029	7	I	61
6	LIV	029	16	A	47
7	WIX	531	8	I	54
8	WIX	531	8	A	89
9	WIX	531	9	A	523
10	WIX	531	7	I	61
11	WIX	531	16	A	47
12	FAR	268	8	I	54
13	FAR	268	8	A	89
14	FAR	268	9	A	523
15	FAR	268	7	I	61
16	FAR	268	16	A	47
Sheet1

 

[venkat1926]

not clear. many details missing what is the logic in choosing data from sheet2

for e.g. in sheet for first "LIV" (row s)t takes the first LIV in sheet 2
for sheet 1 row 3, WIX it takes the seond WIX data from sheet2
for sheet1 row 4 FAR it takes the thrid row of FIR
A5 and A6 are star signs what are they

Tentatively I think you have to use vlookup. This will take data from the first occurrence of site.

see sample sheet and formula in D E and F
the formula in D2 is written first and copied down and right

Excel Workbook
	A	B	C	D	E	F
1	SITE	DEPT	*	GRADE	STATUS	OUC
2	LIV	29	*	8	I	54
3	WIX	531	*	8	I	54
4	FAR	268	*	8	I	54
Sheet1

 

[Haseeb Avarakkan]

Hello Jen, Try this.

Firstly define these names.

Activate Sheet1.

Name: List1
Refers to: =Sheet1!$A$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A))

Activate Sheet2.

Name: List2
Refers to: =Sheet2!$A$2:INDEX(Sheet2!$A:$Z,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1))

Assume, in all sheet Row 1 contains heading & Row 2 to down contains the data.

In the new sheet.

B1 = ROWS(List2)

A3:E3, headings, Site, Dept, Grade...

A4, Copy to B4, then copy down.

=INDEX(List1,INT((ROWS(A$4:A4)-1)/$B$1)+1,COLUMNS($A4:A4))

C4, Copy across & down.

=INDEX(List2,MOD(ROWS(C$4:C4)-1,$B$1)+1,COLUMNS($C4:C4))

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Long</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;;">SITE</td><td style="font-weight: bold;text-align: center;;">DEPT</td><td style="font-weight: bold;text-align: center;;">GRADE</td><td style="font-weight: bold;text-align: center;;">STATUS</td><td style="font-weight: bold;text-align: center;;">OUC</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">LIV</td><td style="text-align: right;;">29</td><td style="text-align: right;;">8</td><td style=";">I</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">LIV</td><td style="text-align: right;;">29</td><td style="text-align: right;;">8</td><td style=";">A</td><td style="text-align: right;;">89</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">LIV</td><td style="text-align: right;;">29</td><td style="text-align: right;;">9</td><td style=";">A</td><td style="text-align: right;;">523</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">LIV</td><td style="text-align: right;;">29</td><td style="text-align: right;;">7</td><td style=";">I</td><td style="text-align: right;;">61</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">LIV</td><td style="text-align: right;;">29</td><td style="text-align: right;;">16</td><td style=";">A</td><td style="text-align: right;;">47</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">WIX</td><td style="text-align: right;;">531</td><td style="text-align: right;;">8</td><td style=";">I</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">WIX</td><td style="text-align: right;;">531</td><td style="text-align: right;;">8</td><td style=";">A</td><td style="text-align: right;;">89</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">WIX</td><td style="text-align: right;;">531</td><td style="text-align: right;;">9</td><td style=";">A</td><td style="text-align: right;;">523</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">WIX</td><td style="text-align: right;;">531</td><td style="text-align: right;;">7</td><td style=";">I</td><td style="text-align: right;;">61</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">WIX</td><td style="text-align: right;;">531</td><td style="text-align: right;;">16</td><td style=";">A</td><td style="text-align: right;;">47</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">FAR</td><td style="text-align: right;;">268</td><td style="text-align: right;;">8</td><td style=";">I</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">FAR</td><td style="text-align: right;;">268</td><td style="text-align: right;;">8</td><td style=";">A</td><td style="text-align: right;;">89</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">FAR</td><td style="text-align: right;;">268</td><td style="text-align: right;;">9</td><td style=";">A</td><td style="text-align: right;;">523</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">FAR</td><td style="text-align: right;;">268</td><td style="text-align: right;;">7</td><td style=";">I</td><td style="text-align: right;;">61</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">FAR</td><td style="text-align: right;;">268</td><td style="text-align: right;;">16</td><td style=";">A</td><td style="text-align: right;;">47</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br />

 

[cgcamal]

Hi Jenawade,

Try with:

Input:

Excel Workbook
	A	B
1	SITE	DEPT
2	LIV	29
3	WIX	531
4	FAR	268
Sheet1

Excel Workbook
	A	B	C
1	GRADE	STATUS	OUC
2	8	I	54
3	8	A	89
4	9	A	523
5	7	I	61
6	16	A	47
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H
1	SITE	DEPT		SITE	DEPT	GRADE	STATUS	OUC
2	LIV	29		LIV	29	8	I	54
3	WIX	531		LIV	29	8	A	89
4	FAR	268		LIV	29	9	A	523
5				LIV	29	7	I	61
6				LIV	29	16	A	47
7				WIX	531	8	I	54
8				WIX	531	8	A	89
9				WIX	531	9	A	523
10				WIX	531	7	I	61
11				WIX	531	16	A	47
12				FAR	268	8	I	54
13				FAR	268	8	A	89
14				FAR	268	9	A	523
15				FAR	268	7	I	61
16				FAR	268	16	A	47
???

#VALUE!

Excel 2010

Cell Formulas
Range	Formula
D1	=A1
D2	=IFERROR(INDEX($A$2:$A$4,CEILING(ROWS(A$1:A1)/(COUNTA(Sheet2!A:A)-1),1)),"")
E1	=B1
E2	=IFERROR(INDEX($B$2:$B$4,CEILING(ROWS(B$1:B1)/(COUNTA(Sheet2!A:A)-1),1)),"")
F1	=Sheet2!A1
G1	=Sheet2!B1
H1	=Sheet2!C1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself