suming values in a column when corresponding values from different columns are similar

[yango]

Here we are working with 2 different sheets(1 and 2)
in sheet(1) If values in column A are similar and value in column O are also similar , then sum the curresponding values in column G , delete duplicates and output the final values on a different sheet(2).

Thanks in advance for your help....
NB: there are more than 32000 rows. Sheet(1) should remain unaffected at the end.

 

[DSCfromCFA]

G'day yango,

You will have to tell us what is "similar data" to get help.

5 and 6 may be similar if you are looking at numbers between 0 and 1,000, but they are not similar if you are looking at numbers between 4 and 8

There is some amiguity in your description of what you wish to sum
Sum all values in Column G where the value for Column A is similar to Column O, but only sum unique values
OR
Sum Column A & Column O if their values are similar AND unique and put the result in Column G


Posting actual sample data and call addresses is also helpful

Cheers

shane

 

[yango]

sorry i cant send a picture of the excel sheet, its doesnot uploading. similar in this case means same values. like 20,20,20 or 2.0.2, 2.0.2 or ASKI, ASKI, ASKI.

i will try explaining as good as possible.
Example: you have 3 columns(A,B,C) with 10 rows each. A are country names B are cities with the same country and C are numbers
if the values in columnA have duplicates e.g texas ,taxes ,texas (for the first 3 rows respectively) and have the same country names in coulmnB e.g USA,USA,USA( for the same respective rows), columnC has 20, 30 ,10 (for the same respective rows) then sum the values in column B (in this case 20+30+10).Export the result on a different worksheet.


Hope this explanation was better. Thanks in advance

[TABLE="width: 99"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]

[/TR]
[TR]
[TD].
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[yango]

here is a screenshot, if column A has duplicate and has the same sales in column O , then sum the value in column G for the same rows. export the output in a different sheet.

 

[DSCfromCFA]

Thanks yango,

that does make it clearer.

Another question - have you tried simply using the 'Subtotals' option found in the 'Data' ribbon in the 'Outline' section (over to the right). If you select all your data, then select Subtotals, you can then further select what you want to subtotal, i.e change of state, change of country or any other major way your data may be gouped, then tell it what column you want to sum.

Otherwise we will need to explore the functions SUMIF and SUMIFS

Cheers

shane

 

[yango]

there are more than 30000 rows of data with around 10 column. it would be better to atomize the work with a macro. can you help me with that?

Thanks in advance

 

[DSCfromCFA]

G'day yango,

The number of rows shouldn't be an issue for the subtotal capability of Excel, it will depend more on how many subtotals you are likley to get, and while I am not familiar with the Subtotal operation, I believe it can go down a few levels.

I am not able to help with VBA and macro's, but can help explore the possibilities of SUMIF (for a single criteria suchn as all sales for USA) or SUMIFS (for multiple criteria, such as all sales for Texas in January for this product.)

There is also the use of Pivot tables and slicers, another area of Excel I am not an experienced user of.

and finally there is an official Microsoft Add-in called 'Power Query' for Excel 2010 and 2013 and now called 'Get and Transform'

Let me know if you want help with SUMIF or SUMIFS for which it will be handy to know the columns that you want to examine, and the specific columns you want to sum, and how you want to display the results. I.e I want to look at Sheet1 Column B for "Texas", and sum every corresponding value in Column J with the result displayed in Sheet 2 Column A Row 2

Cheers

shane