Copy select row info from sheet A to B giving totals

xlhelpme

Board Regular
Joined
Aug 17, 2010
Messages
53
As a relative newbee to this forum i would appreciate any pointers to get me started on the following problem,

I have 2 worksheets A & B (below) and would like to copy data from sheet A to sheet B entering one row for each Number in column A with totals for columns D & E final output should be as shown in Worksheet B (below).

Worksheet A
A
B
C
D
E
Number Q
Period H
Name N
Quantity S
Cost U
30220712
APR-12
Black, Mr. Paul
10.00
656
30220712
APR-12
Black, Mr. Paul
11.00
722
30220712
APR-12
Black, Mr. Paul
3.00
197
30220712
APR-12
Black, Mr. Paul
2.00
131
30220712
APR-12
Black, Mr. Paul
7.00
459
30220712
APR-12
Black, Mr. Paul
2.00
131
30220712
MAY-12
Black, Mr. Paul
4.00
263
30220712
MAY-12
Black, Mr. Paul
13.50
886
30220712
MAY-12
Black, Mr. Paul
4.00
263
30220712
MAY-12
Black, Mr. Paul
2.00
131
30220712
MAY-12
Black, Mr. Paul
2.00
131
30220712
MAY-12
Black, Mr. Paul
6.00
394
30220712
MAY-12
Black, Mr. Paul
2.00
131
30220712
JUN-12
Black, Mr. Paul
2.00
131
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
4.00
200
30225256
MAY-12
Blue, Mr. John
0.00
30225256
MAY-12
Blue, Mr. John
9.50
475
30225256
MAY-12
Blue, Mr. John
9.50
475
30225256
MAY-12
Blue, Mr. John
9.50
475
30225256
MAY-12
Blue, Mr. John
5.00
250
30225256
JUN-12
Blue, Mr. John
0.00
30225256
JUN-12
Blue, Mr. John
0.00
30225256
JUN-12
Blue, Mr. John
0.00
30225256
JUN-12
Blue, Mr. John
0.00
30225256
JUN-12
Blue, Mr. John
0.00
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
4.00
200
30225256
MAY-12
Blue, Mr. John
0.00
30225256
MAY-12
Blue, Mr. John
0.00
30225256
MAY-12
Blue, Mr. John
0.00
30225256
MAY-12
Blue, Mr. John
0.00
30225256
MAY-12
Blue, Mr. John
0.00
30225256
JUN-12
Blue, Mr. John
9.00
450
30225256
JUN-12
Blue, Mr. John
9.00
450
30225256
JUN-12
Blue, Mr. John
9.00
450
30225256
JUN-12
Blue, Mr. John
9.50
475
30225256
JUN-12
Blue, Mr. John
4.00
200
30225256
JUN-12
Blue, Mr. John
9.00
450
30225256
JUN-12
Blue, Mr. John
9.50
475
30225256
JUN-12
Blue, Mr. John
9.00
450
30225256
JUN-12
Blue, Mr. John
9.00
450
30225256
JUN-12
Blue, Mr. John
4.00
200
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
0.00
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
9.00
450
30225256
MAY-12
Blue, Mr. John
5.00
250
30209357
APR-12
Brown, Mr. Ian
1.00
53
30209357
JUN-12
Brown, Mr. Ian
1.00
53
30188049
APR-12
Green, Mr. Andrew
0.00
30188049
APR-12
Green, Mr. Andrew
0.00
30188049
APR-12
Green, Mr. Andrew
0.00
30188049
APR-12
Green, Mr. Andrew
0.00
30188049
APR-12
Green, Mr. Andrew
0.00
30225253
APR-12
Green, Mr. Andrew
8.50
425
30225253
APR-12
Green, Mr. Andrew
8.50
425
30225253
APR-12
Green, Mr. Andrew
5.00
250
30225253
APR-12
Green, Mr. Andrew
8.00
400
30225253
APR-12
Green, Mr. Andrew
9.00
450
30225253
APR-12
Green, Mr. Andrew
9.00
450
30225253
APR-12
Green, Mr. Andrew
9.00
450
30225253
APR-12
Green, Mr. Andrew
5.00
250
30225253
APR-12
Green, Mr. Andrew
8.00
400
30225253
APR-12
Green, Mr. Andrew
9.00
450
30225253
APR-12
Green, Mr. Andrew
9.00
450
30225253
APR-12
Green, Mr. Andrew
9.00
450
30225253
APR-12
Green, Mr. Andrew
5.00
250
30225253
MAY-12
Green, Mr. Andrew
8.50
425
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
8.50
425
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
5.00
250
30225253
MAY-12
Green, Mr. Andrew
8.00
400
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
6.00
300
30225253
MAY-12
Green, Mr. Andrew
8.50
425
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
4.50
225
30225253
MAY-12
Green, Mr. Andrew
8.00
400
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
5.00
250
30225253
MAY-12
Green, Mr. Andrew
9.50
475
30225253
MAY-12
Green, Mr. Andrew
10.00
500
30225253
MAY-12
Green, Mr. Andrew
9.00
450
30225253
MAY-12
Green, Mr. Andrew
6.00
300
30225253
JUN-12
Green, Mr. Andrew
0.00
30225253
JUN-12
Green, Mr. Andrew
0.00
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
6.00
300
30225253
JUN-12
Green, Mr. Andrew
7.50
375
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
5.50
275
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
9.00
450
30225253
JUN-12
Green, Mr. Andrew
4.00
200
30224223
APR-12
White, Mr. Mark
9.00
243
30224223
APR-12
White, Mr. Mark
9.00
243
30224223
APR-12
White, Mr. Mark
9.00
243
30224223
APR-12
White, Mr. Mark
9.00
243
30224223
APR-12
White, Mr. Mark
4.00
108
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
4.00
108
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
4.00
108
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
4.00
108
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
4.00
108
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
9.00
243
30224223
MAY-12
White, Mr. Mark
4.00
108
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
5.00
135
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
4.00
108
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
9.00
243
30224223
JUN-12
White, Mr. Mark
8.00
216
30224223
JUN-12
White, Mr. Mark
5.00
135

<TBODY>
</TBODY>


Worksheet B
A
B
C
D
Number Q
Name N
Quantity S
Cost U
30220712
Black, Mr. Paul
70.50
4627
30225256
Blue, Mr. John
217.50
10875
30209357
Brown, Mr. Ian
2.00
105
30188049
Green, Mr. Andrew
392.50
19625
30224223
White, Mr. Mark
334.00
9018

<TBODY>
</TBODY>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You can do this pretty easily in pivot tables, if you're wanting to use formulas instead a sumifs should do the trick.
untested, but I think my syntax is right:
in C2: =SUMIFS('Sheet A'!$D$2:'Sheet A'!$D$300,'Sheet A'!$A$2:$A$300,$A2,'Sheet A'!$C$2:Sheet1!$C$300,$B2)
assuming data in sheet 1 goes down to cell 300.
 
Upvote 0
Ánd to get all the unique numbers in collumn A for the data in sheet 2.

Select collumn A in sheet 1 and copy it in collumn A of sheet 2.

Excel 2007 => data => remove duplicates
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,291
Members
447,853
Latest member
olddutch7

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