hevy, i've got a problem. I've got 2 sheets:
sheet1:
sheet2:
I want to create a pivot table that will combine the 2 sheets to get the desired result like this:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE align=center cellPadding=0 cellSpacing=0><TBODY><TR><TD bgColor=#0c266b colSpan=5 style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid"><TABLE align=center border=0 width="100%"><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1</FONT></TD><TD align=right style="COLOR: #ffffff; FONT-FAMILY: caption; FONT-SIZE: 9pt">___Running: xl97 : OS = Windows 98</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD bgColor=#d4d0c8 colSpan=5 style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; HEIGHT: 25px"><TABLE align=center border=0 width="100%" VALIGN="MIDDLE"><TBODY><TR><TD style="COLOR: #000000; FONT-FAMILY: caption; FONT-SIZE: 10pt">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD align=right vAlign=center><FORM name=formCb059465><INPUT name=btCb290334 onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula"></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD bgColor=white colSpan=5 style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid"><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD align=middle bgColor=white style="WIDTH: 60px"><SELECT name=sltNb142651 onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value"><OPTION selected value=27>A1</OPTION></SELECT></TD><TD align=right bgColor=#d4d0c8 width="3%">=</TD><TD align=left bgColor=white><INPUT name=txbFb965317 size=80 value="Count of MOBILENBR"></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid" width="2%"></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>A</CENTER></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>B</CENTER></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>C</CENTER></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>D</CENTER></TD></TR><TR><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; FONT-SIZE: 10pt" width="2%"><CENTER>1</CENTER></TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: left; VERTICAL-ALIGN: bottom">Count of MOBILENBR</TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: left; VERTICAL-ALIGN: bottom">STATUS</TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: right; VERTICAL-ALIGN: bottom"></TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: right; VERTICAL-ALIGN: bottom"></TD></TR><TR><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; FONT-SIZE: 10pt" width="2%"><CENTER>2</CENTER></TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: left; VERTICAL-ALIGN: bottom">MARKETCD</TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid; COLOR
sheet1:
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | MARKETCD | MOBILENBR | CHARGECD | BEGINTS | ENDTS | STATUS | INSERVICETS | ||
2 | MUM | 224602970 | ABDL | 05/03/1999 00:00AM | 31/12/9999 00:00AM | O | 05/03/1999 00:00AM | ||
3 | MUM | 224602377 | ABDL | 05/03/1999 00:00AM | 06/12/2000 08:20PM | C | 05/03/1999 00:00AM | ||
4 | MUM | 224602379 | ABDL | 05/03/1999 00:00AM | 06/12/2000 08:23PM | C | 05/03/1999 00:00AM | ||
5 | MUM | 224602521 | ABDL | 05/03/1999 00:00AM | 22/04/1999 11:38AM | C | 05/03/1999 00:00AM | ||
6 | MUM | 224602725 | ABDL | 05/03/1999 00:00AM | 31/12/9999 00:00AM | O | 05/03/1999 00:00AM | ||
7 | MUM | 224602247 | ABDL | 05/03/1999 00:00AM | 21/08/1999 02:01PM | C | 05/03/1999 00:00AM | ||
8 | MUM | 224602248 | ABDL | 05/03/1999 00:00AM | 21/08/1999 02:02PM | C | 05/03/1999 00:00AM | ||
9 | MUM | 224602249 | ABDL | 05/03/1999 00:00AM | 21/08/1999 02:02PM | C | 05/03/1999 00:00AM | ||
10 | MUM | 224602250 | ABDL | 05/03/1999 00:00AM | 21/08/1999 02:03PM | C | 05/03/1999 00:00AM | ||
11 | MUM | 224603160 | ABDL | 05/03/1999 00:00AM | 31/12/9999 00:00AM | O | 05/03/1999 00:00AM | ||
12 | MUM | 224603161 | ABDL | 05/03/1999 00:00AM | 31/12/9999 00:00AM | O | 05/03/1999 00:00AM | ||
13 | MUM | 222307127 | ABDL | 05/03/1999 00:00AM | 26/04/2000 04:53PM | C | 05/03/1999 00:00AM | ||
14 | MUM | 222307128 | ABDL | 05/03/1999 00:00AM | 14/03/2001 05:39PM | C | 05/03/1999 00:00AM | ||
Sheet1 |
sheet2:
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
15 | MARKETCD | MOBILENBR | CHARGECD | BEGINTS | ENDTS | STATUS | INSERVICETS | ||
16 | NMU | 0227908131 | ABDL | 11/2/98 0:00 | 9/4/01 14:13 | C | 11/2/98 0:00 | ||
17 | NMU | 0227908132 | ABDL | 11/2/98 0:00 | 9/4/01 14:14 | C | 11/2/98 0:00 | ||
18 | NMU | 0227903585 | ABDL | 11/2/98 0:00 | 9999-12-31 00:00:00.000 | O | 11/2/98 0:00 | ||
19 | NMU | 0227901925 | ABDL | 11/2/98 0:00 | 12/2/98 23:59 | C | 11/2/98 0:00 | ||
20 | NMU | 0227901927 | ABDL | 11/2/98 0:00 | 9999-12-31 00:00:00.000 | O | 11/2/98 0:00 | ||
21 | NMU | 0227901926 | ABDL | 11/2/98 0:00 | 5/28/02 19:28 | C | 11/2/98 0:00 | ||
22 | NMU | 0227901381 | ABDL | 11/2/98 0:00 | 11/2/98 23:59 | C | 11/1/98 2:09 | ||
23 | NMU | 0227905661 | ABDL | 11/2/98 0:00 | 9999-12-31 00:00:00.000 | O | 11/1/98 3:48 | ||
24 | NMU | 0227901075 | ABDL | 11/2/98 0:00 | 11/8/01 16:20 | C | 11/1/98 6:22 | ||
25 | NMU | 0227901076 | ABDL | 11/2/98 0:00 | 9999-12-31 00:00:00.000 | O | 11/1/98 6:39 | ||
26 | NMU | 0227901077 | ABDL | 11/2/98 0:00 | 9999-12-31 00:00:00.000 | O | 11/1/98 6:47 | ||
27 | NMU | 0227903875 | ABDL | 11/2/98 3:42 | 9/4/02 18:19 | C | 11/2/98 3:40 | ||
28 | NMU | 0227903876 | ABDL | 11/2/98 3:52 | 9/4/02 18:21 | C | 11/2/98 3:49 | ||
29 | NMU | 0227903877 | ABDL | 11/2/98 3:59 | 9999-12-31 00:00:00.000 | O | 11/2/98 3:57 | ||
Sheet1 |
I want to create a pivot table that will combine the 2 sheets to get the desired result like this:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE align=center cellPadding=0 cellSpacing=0><TBODY><TR><TD bgColor=#0c266b colSpan=5 style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid"><TABLE align=center border=0 width="100%"><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1</FONT></TD><TD align=right style="COLOR: #ffffff; FONT-FAMILY: caption; FONT-SIZE: 9pt">___Running: xl97 : OS = Windows 98</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD bgColor=#d4d0c8 colSpan=5 style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; HEIGHT: 25px"><TABLE align=center border=0 width="100%" VALIGN="MIDDLE"><TBODY><TR><TD style="COLOR: #000000; FONT-FAMILY: caption; FONT-SIZE: 10pt">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD align=right vAlign=center><FORM name=formCb059465><INPUT name=btCb290334 onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula"></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD bgColor=white colSpan=5 style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid"><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD align=middle bgColor=white style="WIDTH: 60px"><SELECT name=sltNb142651 onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value"><OPTION selected value=27>A1</OPTION></SELECT></TD><TD align=right bgColor=#d4d0c8 width="3%">=</TD><TD align=left bgColor=white><INPUT name=txbFb965317 size=80 value="Count of MOBILENBR"></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid" width="2%"></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>A</CENTER></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>B</CENTER></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>C</CENTER></TD><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: black; FONT-FAMILY: menu; FONT-SIZE: 10pt"><CENTER>D</CENTER></TD></TR><TR><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; FONT-SIZE: 10pt" width="2%"><CENTER>1</CENTER></TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: left; VERTICAL-ALIGN: bottom">Count of MOBILENBR</TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: left; VERTICAL-ALIGN: bottom">STATUS</TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: right; VERTICAL-ALIGN: bottom"></TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: right; VERTICAL-ALIGN: bottom"></TD></TR><TR><TD align=middle style="BACKGROUND-COLOR: #d4d0c8; BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; FONT-SIZE: 10pt" width="2%"><CENTER>2</CENTER></TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: Arial; FONT-SIZE: 10pt; TEXT-ALIGN: left; VERTICAL-ALIGN: bottom">MARKETCD</TD><TD style="BACKGROUND-COLOR: #e0f4ea; BORDER-BOTTOM: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid; COLOR