pivot table with 2 sheets

catch22

New Member
Joined
Nov 4, 2002
Messages
37
hevy, i've got a problem. I've got 2 sheets:

sheet1:
Book1
ABCDEFG
1MARKETCDMOBILENBRCHARGECDBEGINTSENDTSSTATUSINSERVICETS
2MUM224602970ABDL05/03/1999 00:00AM31/12/9999 00:00AMO05/03/1999 00:00AM
3MUM224602377ABDL05/03/1999 00:00AM06/12/2000 08:20PMC05/03/1999 00:00AM
4MUM224602379ABDL05/03/1999 00:00AM06/12/2000 08:23PMC05/03/1999 00:00AM
5MUM224602521ABDL05/03/1999 00:00AM22/04/1999 11:38AMC05/03/1999 00:00AM
6MUM224602725ABDL05/03/1999 00:00AM31/12/9999 00:00AMO05/03/1999 00:00AM
7MUM224602247ABDL05/03/1999 00:00AM21/08/1999 02:01PMC05/03/1999 00:00AM
8MUM224602248ABDL05/03/1999 00:00AM21/08/1999 02:02PMC05/03/1999 00:00AM
9MUM224602249ABDL05/03/1999 00:00AM21/08/1999 02:02PMC05/03/1999 00:00AM
10MUM224602250ABDL05/03/1999 00:00AM21/08/1999 02:03PMC05/03/1999 00:00AM
11MUM224603160ABDL05/03/1999 00:00AM31/12/9999 00:00AMO05/03/1999 00:00AM
12MUM224603161ABDL05/03/1999 00:00AM31/12/9999 00:00AMO05/03/1999 00:00AM
13MUM222307127ABDL05/03/1999 00:00AM26/04/2000 04:53PMC05/03/1999 00:00AM
14MUM222307128ABDL05/03/1999 00:00AM14/03/2001 05:39PMC05/03/1999 00:00AM
Sheet1


sheet2:
Book1
ABCDEFG
15MARKETCDMOBILENBRCHARGECDBEGINTSENDTSSTATUSINSERVICETS
16NMU0227908131ABDL11/2/98 0:009/4/01 14:13C11/2/98 0:00
17NMU0227908132ABDL11/2/98 0:009/4/01 14:14C11/2/98 0:00
18NMU0227903585ABDL11/2/98 0:009999-12-31 00:00:00.000O11/2/98 0:00
19NMU0227901925ABDL11/2/98 0:0012/2/98 23:59C11/2/98 0:00
20NMU0227901927ABDL11/2/98 0:009999-12-31 00:00:00.000O11/2/98 0:00
21NMU0227901926ABDL11/2/98 0:005/28/02 19:28C11/2/98 0:00
22NMU0227901381ABDL11/2/98 0:0011/2/98 23:59C11/1/98 2:09
23NMU0227905661ABDL11/2/98 0:009999-12-31 00:00:00.000O11/1/98 3:48
24NMU0227901075ABDL11/2/98 0:0011/8/01 16:20C11/1/98 6:22
25NMU0227901076ABDL11/2/98 0:009999-12-31 00:00:00.000O11/1/98 6:39
26NMU0227901077ABDL11/2/98 0:009999-12-31 00:00:00.000O11/1/98 6:47
27NMU0227903875ABDL11/2/98 3:429/4/02 18:19C11/2/98 3:40
28NMU0227903876ABDL11/2/98 3:529/4/02 18:21C11/2/98 3:49
29NMU0227903877ABDL11/2/98 3:599999-12-31 00:00:00.000O11/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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

catch22

New Member
Joined
Nov 4, 2002
Messages
37
oops...the end of the pivot table did not come...hence here it is:
Book1
ABCD
1Count of MOBILENBRSTATUS
2MARKETCDCOGrand Total
3MUM9413
4NMU8614
5Grand Total171027
Sheet5


I have tried multiple consolidation ranges, but i think i'm doing something wrong as it is not giving the above result...pls help
 

catch22

New Member
Joined
Nov 4, 2002
Messages
37
hello...i just wanted a solution for this one. Did'nt get it till now...hence have brought this up once again in the forum. Could anyone please please help a damsel in distress? :)...gee thanks a ton
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Hey - so you think flattery gets you special attention....? ha ha

How many rows are we talking about in each sheet?

As the formats look identical and you have combined less than 65536 rows then I would suggest you merge the data into one sheet and run the pivot off that.

Is that viable?
 

catch22

New Member
Joined
Nov 4, 2002
Messages
37
sweety boy...you got it dead right...it does get me special attention ( though i dont use it as a tool to get help.

As for the pivot table, I know that it can be done by pasting the data below in the 1st sheet itself and then running it. But what if I have many sheets? It's a real pain in the butt, pasting the data 1 below the other. That was my main problem. Anyway, no probs...if there is no way out of doing it directly from the pivot table, i guess, i will have to live with the misery of getting it all into one sheet( copy>paste, copy>paste, copy>paste ):). thanks a ton for the reply....appreciated it a lot. (you may not believe me, but it's true:)
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Dearest...

Creating a Macro to merge the data for you is pretty straight forward...just create a list of the sheets that you want to use.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top