How do I search thr'o column and put unique values in differnt sheet and sum corresponding values in

  • Thread starter Thread starter test test
  • Start date Start date
T

test test

How do I search thr'o column and put unique values in differnt sheet and
sum corresponding values in different column?

For example I have 3 columns in one sheet
from To total
ABC WP 10
ABC ABC 10

My output should looks like this

col from To
ABC 20 20
WP 0 10

thanks in advaced
 
Assume the original data is in Sheet1 in A1 to C3 with A1 holding the entry
col

On sheet2 enter

ABC
WP
with ABC in A2, WP in A3

in B2 put in the formula

=Sumif(Sheet1!$A:$A,$A2,Sheet1!B:B)
select this cell and drag fill to the right (to C2)

select B2:C2 and drag fill down the column
 
You can select the values in column A, then do Data=>Filter=>Advanced
filter, click the Unique Only checkbox in the lower left, select copy to
another location, specify D1 as the location (leave criteria blank) and
you get the unique list from A
Repeat for B, but put below the data now in Column D. Now select column D
and repeat, putting the data in E1.

Now you have your unique list and you can use the countif formula.

Turn on the macro recorder while you do this manually (after E is created,
select column D and delete it). Then turn off the macro recorder. Now
modify the code to make it more general if you desire.

Now you can use the code to get your unique list.
 
Back
Top