• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Amalgamate rows

A

adamuce

Member
Hi,

Hope you are all well.

I usually have to amalgamate quarterly data into year data.
Rows 3 to 14 are the raw data entry points. I want to end up with Row 18 to Row 24
For example:
USA Exports Commodity "A" to Canada in Q1:20 units, Q2:30 units, Q3:40 units, q4:50 units. I want to end up with one data entry which states USA to Canada Commodity A : 140 units.

Thanks in advance
 
@adamuce

Please take a moment to review the forum rules. While we don't mind cross-posting answers, we do ask that you provide links to avoid wasting time across different sites. Thanks.
 
adamuce ... and after You have read those links
For Cell [E18] =SUMIFS(E$3:E$14,B$3:B$14,B18,C$3:C$14,C18,D$3:D$14,D18) and copy down
( It's challenge to write while ... new good replies coming ... but I didn't want just delete above!)
 
I tried that it works. But I will have to have all the combinations done first.
I am guessing I do a pivot table to get the combos?
 
adamuce
You sent a snapshot which is Your case. Isn't this case solved?
or what else would there come a little by little?
You should have had an idea, then You opened this case.
... Pivot-table
... It's challenge with the snapshot!
 
vletm said:
adamuce
You sent a snapshot which is Your case. Isn't this case solved?
or what else would there come a little by little?
You should have had an idea, then You opened this case.
... Pivot-table
... It's challenge with the snapshot!
Click to expand...

Thanks for your reply. No its partially solved, because maybe it was misinterpreted. I am new to this and not an adept user so please bear with me :D

The solution I want which are row 18 to rows 24s, I wanted to know how I form the combination in the 1st place for each row for your formula to work. Ie: The exporter name, then the partner name, then the commodity code before I put the formula.

After you gave the formula I was trying to figure out how to get those rows and I replied the only way I could think of is doing a pivot table.
 
vletm said:
adamuce
The Results are in G-column.
Click to expand...

Hi,

I was really excited to learn this new formula that I went back home after work and tried it.

In order for this formula to work, I would need to sort the raw data first in the following order:
Exporter (Column B)
Partner (Column C)
Commodity Code (Column D)
 
adamuce
Sorting would be 'good to do' if would like to get one kind of answer.
.. You could test this file too;
[ Sort ]-button and same time 'refresh' those formulas.
 

Attachments

  • del forum.xls
    46.5 KB · Views: 1
You must log in or register to reply here.
Back
Top