I have a list of cars in Stockyards A1,A2, A3
Col A €“ Make, Col B Qty in Nos, Col C - Stockyard

Honda 2 A1
Toyota 1 A1
Land Rover 3 A2
Ford 6 A2
BMW 2 A3
Mercedes 1 A2
Honda 2 A3
BMW 1 A3
Ford 5 A2
Toyota 3 A1

Using VBA I would like to see a summary of Cars Stock, that should look as
under :

BMW 3
Ford 11
Honda 4
Land Rover 3
Mercedes 1
Toyota 4
Grand Total 26

Any help will be greatly appreciated. I would extend the logic for 25 Models
of cars
spread across 120 Stock yards.

Hi Anath,

This is a perfect set of data to use in a pivot-table, which will give
you the answers you need without requiring VBA.

Give it a try.
Highlight your data and click on: DATA/PIVOT TABLE/FINISH

Hope this helps

Greg

Ananth wrote:
I have a list of cars in Stockyards A1,A2, A3
Col A - Make, Col B Qty in Nos, Col C - Stockyard

Honda 2 A1
Toyota 1 A1
Land Rover 3 A2
Ford 6 A2
BMW 2 A3
Mercedes 1 A2
Honda 2 A3
BMW 1 A3
Ford 5 A2
Toyota 3 A1

Using VBA I would like to see a summary of Cars Stock, that should look as
under :

BMW 3
Ford 11
Honda 4
Land Rover 3
Mercedes 1
Toyota 4
Grand Total 26

Any help will be greatly appreciated. I would extend the logic for 25 Models
of cars
spread across 120 Stock yards.

In another part of your spreadsheet, say H1, enter

=A1

In H2

=IF(ISERROR(MATCH(0,COUNTIF(H$1:H1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(H$1:H1,$A$1:$A$2
0&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy H2 down as far as you need, leave some spare for new values, they will
initially show as blank

in I1 enter

=IF(H1="","",SUMIF($A$1:$A$10,H1,$B$1:$B$10))

and copy that down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ananth" wrote in message
...
I have a list of cars in Stockyards A1,A2, A3
Col A - Make, Col B Qty in Nos, Col C - Stockyard

Honda 2 A1
Toyota 1 A1
Land Rover 3 A2
Ford 6 A2
BMW 2 A3
Mercedes 1 A2
Honda 2 A3
BMW 1 A3
Ford 5 A2
Toyota 3 A1

Using VBA I would like to see a summary of Cars Stock, that should look as
under :

BMW 3
Ford 11
Honda 4
Land Rover 3
Mercedes 1
Toyota 4
Grand Total 26

Any help will be greatly appreciated. I would extend the logic for 25
Models
of cars
spread across 120 Stock yards.

I am aware that this can be achieved by PIVOT and by set of compex formulas.
I am just learning VBA and I thought that this can be a starter

"Greg Glynn" wrote:

Hi Anath,

This is a perfect set of data to use in a pivot-table, which will give
you the answers you need without requiring VBA.

Give it a try.
Highlight your data and click on: DATA/PIVOT TABLE/FINISH

Hope this helps

Greg

Ananth wrote:
I have a list of cars in Stockyards A1,A2, A3
Col A - Make, Col B Qty in Nos, Col C - Stockyard

Honda 2 A1
Toyota 1 A1
Land Rover 3 A2
Ford 6 A2
BMW 2 A3
Mercedes 1 A2
Honda 2 A3
BMW 1 A3
Ford 5 A2
Toyota 3 A1

Using VBA I would like to see a summary of Cars Stock, that should look as
under :

BMW 3
Ford 11
Honda 4
Land Rover 3
Mercedes 1
Toyota 4
Grand Total 26

Any help will be greatly appreciated. I would extend the logic for 25 Models
of cars
spread across 120 Stock yards.

see my response, but it ain't VBA.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ananth" wrote in message
...
I am aware that this can be achieved by PIVOT and by set of compex
formulas.
I am just learning VBA and I thought that this can be a starter

"Greg Glynn" wrote:

Hi Anath,

This is a perfect set of data to use in a pivot-table, which will give
you the answers you need without requiring VBA.

Give it a try.
Highlight your data and click on: DATA/PIVOT TABLE/FINISH

Hope this helps

Greg

Ananth wrote:
I have a list of cars in Stockyards A1,A2, A3
Col A - Make, Col B Qty in Nos, Col C - Stockyard

Honda 2 A1
Toyota 1 A1
Land Rover 3 A2
Ford 6 A2
BMW 2 A3
Mercedes 1 A2
Honda 2 A3
BMW 1 A3
Ford 5 A2
Toyota 3 A1

Using VBA I would like to see a summary of Cars Stock, that should
look as
under :

BMW 3
Ford 11
Honda 4
Land Rover 3
Mercedes 1
Toyota 4
Grand Total 26

Any help will be greatly appreciated. I would extend the logic for 25
Models
of cars
spread across 120 Stock yards.