Help merging!!!! Asap

[Miss King]

Hey please can anyone help me, I need to merge like over 3000 entries into one document... I have two excel documents with different information in each but want to merge it together...
For example - " l " will be used as a column.

Document one has...

1 l 15
2 l 20
6 l 25
7 l 30

Document two has...

1 l 2
2 l 3
4 l 4
5 l 5

I want to merge it to one document which will look like this...

1 l 15 l 2
2 l 20 l 3
4 l 0 l 4
5 l 0 l 5
6 l 25 l
7 l 30 l

PLEASE PLEASE HELP - NEED IT ASAP!!!!

Thank you

 

[Matty]

Welcome to the Board,

A 'quick and dirty way' would be to do something like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Number	Doc 1	Doc 2	Filter		Number	Doc 1		Number	Doc 2
2	1	15	2	Data		1	15		1	2
3	2	20	3	Data		2	20		2	3
4	3	0	0	No Data		6	25		4	4
5	4	0	4	Data		7	30		5	5
6	5	0	5	Data
7	6	25	0	Data
8	7	30	0	Data
9	8	0	0	No Data
10	9	0	0	No Data
11	10	0	0	No Data
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(ISNA(MATCH(A2,F$2:F$5,0)),0,VLOOKUP(A2,F$2:G$5,2,0))
B3	=IF(ISNA(MATCH(A3,F$2:F$5,0)),0,VLOOKUP(A3,F$2:G$5,2,0))
B4	=IF(ISNA(MATCH(A4,F$2:F$5,0)),0,VLOOKUP(A4,F$2:G$5,2,0))
B5	=IF(ISNA(MATCH(A5,F$2:F$5,0)),0,VLOOKUP(A5,F$2:G$5,2,0))
B6	=IF(ISNA(MATCH(A6,F$2:F$5,0)),0,VLOOKUP(A6,F$2:G$5,2,0))
B7	=IF(ISNA(MATCH(A7,F$2:F$5,0)),0,VLOOKUP(A7,F$2:G$5,2,0))
B8	=IF(ISNA(MATCH(A8,F$2:F$5,0)),0,VLOOKUP(A8,F$2:G$5,2,0))
B9	=IF(ISNA(MATCH(A9,F$2:F$5,0)),0,VLOOKUP(A9,F$2:G$5,2,0))
B10	=IF(ISNA(MATCH(A10,F$2:F$5,0)),0,VLOOKUP(A10,F$2:G$5,2,0))
B11	=IF(ISNA(MATCH(A11,F$2:F$5,0)),0,VLOOKUP(A11,F$2:G$5,2,0))
C2	=IF(ISNA(MATCH(A2,I$2:I$5,0)),0,VLOOKUP(A2,I$2:J$5,2,0))
C3	=IF(ISNA(MATCH(A3,I$2:I$5,0)),0,VLOOKUP(A3,I$2:J$5,2,0))
C4	=IF(ISNA(MATCH(A4,I$2:I$5,0)),0,VLOOKUP(A4,I$2:J$5,2,0))
C5	=IF(ISNA(MATCH(A5,I$2:I$5,0)),0,VLOOKUP(A5,I$2:J$5,2,0))
C6	=IF(ISNA(MATCH(A6,I$2:I$5,0)),0,VLOOKUP(A6,I$2:J$5,2,0))
C7	=IF(ISNA(MATCH(A7,I$2:I$5,0)),0,VLOOKUP(A7,I$2:J$5,2,0))
C8	=IF(ISNA(MATCH(A8,I$2:I$5,0)),0,VLOOKUP(A8,I$2:J$5,2,0))
C9	=IF(ISNA(MATCH(A9,I$2:I$5,0)),0,VLOOKUP(A9,I$2:J$5,2,0))
C10	=IF(ISNA(MATCH(A10,I$2:I$5,0)),0,VLOOKUP(A10,I$2:J$5,2,0))
C11	=IF(ISNA(MATCH(A11,I$2:I$5,0)),0,VLOOKUP(A11,I$2:J$5,2,0))
D2	=IF(AND(B2=0,C2=0),"No Data","Data")
D3	=IF(AND(B3=0,C3=0),"No Data","Data")
D4	=IF(AND(B4=0,C4=0),"No Data","Data")
D5	=IF(AND(B5=0,C5=0),"No Data","Data")
D6	=IF(AND(B6=0,C6=0),"No Data","Data")
D7	=IF(AND(B7=0,C7=0),"No Data","Data")
D8	=IF(AND(B8=0,C8=0),"No Data","Data")
D9	=IF(AND(B9=0,C9=0),"No Data","Data")
D10	=IF(AND(B10=0,C10=0),"No Data","Data")
D11	=IF(AND(B11=0,C11=0),"No Data","Data")

Then filter on Column D with "No Data" and delete those Rows. Obviously, in reality, the lookup data wouldn't be to the right of the created list, as it would get deleted when you deleted the Rows...

Does this help?

Matty

 

[VoG]

Similar: B1 and C1 filled down

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">15</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">20</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td><td > </td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">5</td><td > </td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6</td><td style="text-align:right; ">25</td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">7</td><td style="text-align:right; ">30</td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">8</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">9</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">10</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=IF(ISNUMBER<span style=' color:008000; '>(MATCH<span style=' color:#0000ff; '>(A1,Sheet1!A:A,0)</span>)</span>,INDEX<span style=' color:008000; '>(Sheet1!B:B,MATCH<span style=' color:#0000ff; '>(A1,Sheet1!A:A,0)</span>)</span>,"")</td></tr><tr><td >C1</td><td >=IF(ISNUMBER<span style=' color:008000; '>(MATCH<span style=' color:#0000ff; '>(A1,Sheet2!A:A,0)</span>)</span>,INDEX<span style=' color:008000; '>(Sheet2!B:B,MATCH<span style=' color:#0000ff; '>(A1,Sheet2!A:A,0)</span>)</span>,"")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Doogs]

Thanks for this!