I have been trying to use :subtotlas, consolidate, IF, & pivottables to get a
spreadsheet of inventory #'s (same part # in various cells) and want to
aggregate/consolidate those to 1 record with a TOTAL QTY value that I can
then use to import to a program.

I have tried the if command and get no values
I have not been able to successfully use the consolidate function
the subtotals function kind of works BUT does not give me the summed total
on 1 line with the part # for export
And pivot table method returns a COUNT on the qty field NOT a sum of all
same part # ???

Suggestions please I am stuck

Hi,

Can you give us an example?

Thanks,
--
Farhad Hodjat


"Marty Marshfield" wrote:

> I have been trying to use :subtotlas, consolidate, IF, & pivottables to get a
> spreadsheet of inventory #'s (same part # in various cells) and want to
> aggregate/consolidate those to 1 record with a TOTAL QTY value that I can
> then use to import to a program.
>
> I have tried the if command and get no values
> I have not been able to successfully use the consolidate function
> the subtotals function kind of works BUT does not give me the summed total
> on 1 line with the part # for export
> And pivot table method returns a COUNT on the qty field NOT a sum of all
> same part # ???
>
> Suggestions please I am stuck

PARTNO QTY
aa 2
bb 1
cc 3
aa 1
cc 10
aa 6

I have a list like this of 9000 cells and want to aggregate the unique part
#'s into 1 line with part # and TOTAL QTY
So the result from above would be like:

aa = 9
bb = 1
cc = 13

Any detailed explanation on how to do this is appreciated. the closest I
came was using data/subtotals but even that was not right in that the TOTALS
line did not contain the TOTAL and I could not see how to get just the unique
lines with the total qty


"Farhad" wrote:

> Hi,
>
> Can you give us an example?
>
> Thanks,
> --
> Farhad Hodjat
>
>
> "Marty Marshfield" wrote:
>
> > I have been trying to use :subtotlas, consolidate, IF, & pivottables to get a
> > spreadsheet of inventory #'s (same part # in various cells) and want to
> > aggregate/consolidate those to 1 record with a TOTAL QTY value that I can
> > then use to import to a program.
> >
> > I have tried the if command and get no values
> > I have not been able to successfully use the consolidate function
> > the subtotals function kind of works BUT does not give me the summed total
> > on 1 line with the part # for export
> > And pivot table method returns a COUNT on the qty field NOT a sum of all
> > same part # ???
> >
> > Suggestions please I am stuck

My preference would be a pivot table. Place your cursor in the middle of the
source data somewhere and select Data|Pivot Table -> a wizard will pop up. In
all likelyhood you can just select finish and it will make all of the correct
choices for you. A new sheet will be created with a pivot table on it. Drag
the Part numbers field to the left had column and the Quantity to the middle.
That shoud do it for you... for more info see

http://pubs.logicalexpressions.com/P...cle.asp?ID=553
--
HTH...

Jim Thomlinson


"Marty Marshfield" wrote:

> PARTNO QTY
> aa 2
> bb 1
> cc 3
> aa 1
> cc 10
> aa 6
>
> I have a list like this of 9000 cells and want to aggregate the unique part
> #'s into 1 line with part # and TOTAL QTY
> So the result from above would be like:
>
> aa = 9
> bb = 1
> cc = 13
>
> Any detailed explanation on how to do this is appreciated. the closest I
> came was using data/subtotals but even that was not right in that the TOTALS
> line did not contain the TOTAL and I could not see how to get just the unique
> lines with the total qty
>
>
> "Farhad" wrote:
>
> > Hi,
> >
> > Can you give us an example?
> >
> > Thanks,
> > --
> > Farhad Hodjat
> >
> >
> > "Marty Marshfield" wrote:
> >
> > > I have been trying to use :subtotlas, consolidate, IF, & pivottables to get a
> > > spreadsheet of inventory #'s (same part # in various cells) and want to
> > > aggregate/consolidate those to 1 record with a TOTAL QTY value that I can
> > > then use to import to a program.
> > >
> > > I have tried the if command and get no values
> > > I have not been able to successfully use the consolidate function
> > > the subtotals function kind of works BUT does not give me the summed total
> > > on 1 line with the part # for export
> > > And pivot table method returns a COUNT on the qty field NOT a sum of all
> > > same part # ???
> > >
> > > Suggestions please I am stuck

I cannot get the QTY output to be a sum of the part #, it keeps doing count
and the when i dbl click the QTY field in data and change to SUM it shows 0
in all cells?

I have tried to change this several ways and cannot seem to get it to work??

Help

"Jim Thomlinson" wrote:

> My preference would be a pivot table. Place your cursor in the middle of the
> source data somewhere and select Data|Pivot Table -> a wizard will pop up. In
> all likelyhood you can just select finish and it will make all of the correct
> choices for you. A new sheet will be created with a pivot table on it. Drag
> the Part numbers field to the left had column and the Quantity to the middle.
> That shoud do it for you... for more info see
>
> http://pubs.logicalexpressions.com/P...cle.asp?ID=553
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Marty Marshfield" wrote:
>
> > PARTNO QTY
> > aa 2
> > bb 1
> > cc 3
> > aa 1
> > cc 10
> > aa 6
> >
> > I have a list like this of 9000 cells and want to aggregate the unique part
> > #'s into 1 line with part # and TOTAL QTY
> > So the result from above would be like:
> >
> > aa = 9
> > bb = 1
> > cc = 13
> >
> > Any detailed explanation on how to do this is appreciated. the closest I
> > came was using data/subtotals but even that was not right in that the TOTALS
> > line did not contain the TOTAL and I could not see how to get just the unique
> > lines with the total qty
> >
> >
> > "Farhad" wrote:
> >
> > > Hi,
> > >
> > > Can you give us an example?
> > >
> > > Thanks,
> > > --
> > > Farhad Hodjat
> > >
> > >
> > > "Marty Marshfield" wrote:
> > >
> > > > I have been trying to use :subtotlas, consolidate, IF, & pivottables to get a
> > > > spreadsheet of inventory #'s (same part # in various cells) and want to
> > > > aggregate/consolidate those to 1 record with a TOTAL QTY value that I can
> > > > then use to import to a program.
> > > >
> > > > I have tried the if command and get no values
> > > > I have not been able to successfully use the consolidate function
> > > > the subtotals function kind of works BUT does not give me the summed total
> > > > on 1 line with the part # for export
> > > > And pivot table method returns a COUNT on the qty field NOT a sum of all
> > > > same part # ???
> > > >
> > > > Suggestions please I am stuck