pivot table with 2 sheets

C

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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:)
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
294
awoohaw
awoohaw
E
  • Question
Adding a custom message.
Replies
1
Views
418
Micron
M
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
311
VBAEXCELNew
V
F
  • Question
Need a formula.
Replies
1
Views
289
Peter_SSs
Peter_SSs
I
  • Solved
Calculate total hours into days, hours, minutes
Replies
3
Views
382
Issie_52
I

Forum statistics

Threads
1,214,405
Messages
6,119,315
Members
448,886
Latest member
GBCTeacher

Share this page

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
Back
Top