Sum Unique Values (Diff Row & Column ) from Different Sh

[Kamal Subhani]

Book1
	A	B	C	D
1	Product	Quality1	Quality3	Quality4
2	A	10	10	10
3	B	20	20	10
4	F	30	10	10
5	E	10	20	30
Sheet1

Book1
	A	B	C	D
1	Product	Quality3	Quality2	Quality5
2	C	10	10	10
3	D	20	20	10
4	A	30	10	10
Sheet10

Book1
	A	B	C	D	E	F
1	Product	Quality1	Quality2	Quality3	Quality4	Quality5
2	A	10	40	10	10	10
3	B	20	20	10	0	0
4	C	0	10	0	10	10
5	D	0	20	0	20	10
6	E	10	20	30	0	0
7	F	30	10	10	0	0
Main

In actual I have more than 10 sheets.

In the main sheet the Unique Products and the Unique quality will be displayed with the help of the formula

In the main sheet the total of each product according to qualties should be dispalyed


Plz help.

I think u undertand my problem.

 

[Kamal Subhani]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Dear Juan Pablo González

Instead of helping a new bie in the excel and the forum my Topic Locked


Is It fair I started a new Topic Because one Board Master told me to do. He said that to many posts which make it difficult to read so start a new Thread .

thats why i started a new One

but thats locked

Still My Problem Not solved

I Think That if Aladin is available he is only one who will help me out



Reagrds and soory for posting Duplicate Juan Pablo González

 

[Andrew Poulsom]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Do you want to try the Pivot Table approach?

Insert a worksheet at the beginning of your workbook. From the menu choose Data|Pivot Table and Pivot Chart Report. At Step 1 check Multiple consolidation ranges and click Next twice. At Step 2b, in the Range box click the tab of your first sheet, highlight the range containing your data and click Add. Click the tab for your second sheet, highlight the range containing your data and click Add again. Repeat this process for all your sheets and click Next. At Step 4 click Layout. Double click the Count of Value button, choose Sum and click OK twice. Then click Finish.

Job done!

 

[Kamal Subhani]

Re: Sum Unique Values (Diff Row & Column ) from Differen

TThnx for the help

Old problems not resolved

New problems started to arise and me totally confused now
I have given the extra range for the additinal data to be posted in the shhet1 and sheet2 when I am making the pivot Table blank columns and rows are comming . when I write in the addtitional colums and rows in the sheet1 and sheet 2 the data is not updated automatically and not sorted sorted in the pivot table sheet

How this can be done?

 

[Andrew Poulsom]

Re: Sum Unique Values (Diff Row & Column ) from Differen

To hide the blanks click the down arrow to the right of the Row or Column button, uncheck (blank) and click OK. To update the Pivot Table for new data, right click the Pivot Table and choose Refresh Data.

 

[Kamal Subhani]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Hi Andrew Poulsom

Thats the problem I am facing
I dont want manual updates

I want to do this automatically


So I think For me formula is the better approach


so Plz help me with the formua.
What the hell I am trying to explain ,People are not getting me what I am trying to tell them


I want like the Index match Univalues, threed combination to get the desired results.

 

[NateO]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Welcome to the world of Dbs, think of it as normalization.

Record a macro to refresh and call it on a change event.

 

[Andrew Poulsom]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Actually it's not that difficult to automate the refresh. Right click the Excel icon to the left of File on the menu and choose View Code. Paste this code into the window on the right:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Then Exit Sub
    Worksheets("Sheet1").PivotTables(1).RefreshTable
End Sub

Change "Sheet1" to the name of the sheet containing the Pivot Table. Prees Alt+F11 to return to your workbook.

Now the Pivot Table will be updated whenever you change somethig on your other sheets.

 

[Kamal Subhani]

Re: Sum Unique Values (Diff Row & Column ) from Differen

I dont want to show the Grandtotal across the column and the Row what should be done .

Plz help

 

[Kamal Subhani]

Re: Sum Unique Values (Diff Row & Column ) from Differen

The quality Headers are not in the ascending or the descending order.

Book1.xls
	A	B	C	D	E	F	G	H	I
1	Page1	(All)
2
3	SumofValue	Column
4	Row	Quality1	Quality2	Quality3	Quality4	Quality5	Quality8	Quality6	GrandTotal
5	A	20	20	10	20	10		10	90
6	B	20	20	10	20	10		10	90
7	C	20	20	10	20	10	10	10	100
8	D	20	20	10	20	10	10	10	100
9	E	10	10		10		10		40
10	F	10	10		10		10		40
11	GrandTotal	100	100	40	100	40	40	40	460
Main1

Plz tell me what to do


in The Pivot Tables

 

[Andrew Poulsom]

Re: Sum Unique Values (Diff Row & Column ) from Differen

I dont want to show the Grandtotal across the column and the Row what should be done .

Plz help

Right click the Pivot Table and choose Table Options. Uncheck Grand totals for rows and columns and click OK.

For your sorting problem, right click the Column button and choose Field settings. Click Advanced, choose an Autosort option and click OK twice.