Consolidating/Grouping Data
 

Hi all,

I have a large list of data, example shown here;


Code
-------------------

M/C PARTS BATCH QT
WB01 01810-11004 392528 87
WB01 01810-11004 392528 27
WB01 01821-00505 392202 32
WB01 01821-00505 392789 84
WB01 01821-00505 392789 60
WB01 01821-00505 392789 85
WB01 01821-00505 392790 80
WB01 01821-00505 392790 44

-------------------


....the list goes on. How can I group this data by M/C, Parts, Batc
and then total up the Qty? So the list would end up as follows;



Code
-------------------

M/C PARTS BATCH QT
WB01 01810-11004 392528 114
WB01 01821-00505 392202 32
WB01 01821-00505 392789 229
WB01 01821-00505 392790 124

-------------------


The data above has been group and a total produced....

Any ideas how I can do this? TI

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52348

Consolidating/Grouping Data
 

Hi,

I don't know, how it is called in the english version because i´ve the
german version of office.

i think you can find an menue-option called "Data" and
below there must be an option like "part result"

there you can declare Groups and SUM-Fields

I think you´ve do run this 3 times, 1 for each group-level...

first group M/C and sum QTY
secound group PARTS ans sum QTY
third group BATCH an sum QTY

best regards
Jörg


"gti_jobert"
schrieb im Newsbeitrag
...

Hi all,

I have a large list of data, example shown here;


Code:
--------------------

M/C PARTS BATCH QTY
WB01 01810-11004 392528 87
WB01 01810-11004 392528 27
WB01 01821-00505 392202 32
WB01 01821-00505 392789 84
WB01 01821-00505 392789 60
WB01 01821-00505 392789 85
WB01 01821-00505 392790 80
WB01 01821-00505 392790 44

--------------------


...the list goes on. How can I group this data by M/C, Parts, Batch
and then total up the Qty? So the list would end up as follows;



Code:
--------------------

M/C PARTS BATCH QTY
WB01 01810-11004 392528 114
WB01 01821-00505 392202 32
WB01 01821-00505 392789 229
WB01 01821-00505 392790 124

--------------------


The data above has been group and a total produced....

Any ideas how I can do this? TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=523485

Consolidating/Grouping Data
 

Thanks for your reply, but its not what i'm looking for.

I have now advanced filtered my range (without the Qty) to obtain all
the unique values within the range - this is now what I want except I
am missing the sub total values for the unique values.

Any Ideas?


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=523485

Consolidating/Grouping Data
 

If M/C,Parts and Batch are ALREADY sorted Ascending, you are ready to go
straight to Data Subtotaling and in 1st box "For each change in"
enter "Batch" (no quotes) Sum Ck Qty
OK out


You can further Highlight the range A1:B13, then at the menu Edit, Goto..
Select Blanks, OK
the first blank cell in the range should be the active cell;
enter (into it) = (then arrow your cursor up to the cell just above and
hold-down the Control key at the same time you press the enter key.
Wa-La...

Now Select the Level 2 grouping versus the default Level 3.

Your list should look like you want it to..

HTH,
Jim May

"Jörg" <Joerg wrote in message
...
Hi,

I don't know, how it is called in the english version because i´ve the
german version of office.

i think you can find an menue-option called "Data" and
below there must be an option like "part result"

there you can declare Groups and SUM-Fields

I think you´ve do run this 3 times, 1 for each group-level...

first group M/C and sum QTY
secound group PARTS ans sum QTY
third group BATCH an sum QTY

best regards
Jörg


"gti_jobert"
schrieb im Newsbeitrag
...

Hi all,

I have a large list of data, example shown here;


Code:
--------------------

M/C PARTS BATCH QTY
WB01 01810-11004 392528 87
WB01 01810-11004 392528 27
WB01 01821-00505 392202 32
WB01 01821-00505 392789 84
WB01 01821-00505 392789 60
WB01 01821-00505 392789 85
WB01 01821-00505 392790 80
WB01 01821-00505 392790 44

--------------------


...the list goes on. How can I group this data by M/C, Parts, Batch
and then total up the Qty? So the list would end up as follows;



Code:
--------------------

M/C PARTS BATCH QTY
WB01 01810-11004 392528 114
WB01 01821-00505 392202 32
WB01 01821-00505 392789 229
WB01 01821-00505 392790 124

--------------------


The data above has been group and a total produced....

Any ideas how I can do this? TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread:
http://www.excelforum.com/showthread...hreadid=523485

Consolidating/Grouping Data
 

Data=Subtotal does exactly what you describe.

If you want to do it manually as you suggest, then

=sumproduct(--($A$1:$A$2000=G1),--($B$1:$B$2000=H1),--($C$1:$C$2000=I1),$D$1:$D$2000)

then drag fill down the column.

where your unique values are in G,H and I and source data shown in A, B, C, D

--
Regards,
Tom Ogilvy


"gti_jobert" wrote:


Thanks for your reply, but its not what i'm looking for.

I have now advanced filtered my range (without the Qty) to obtain all
the unique values within the range - this is now what I want except I
am missing the sub total values for the unique values.

Any Ideas?


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=523485