An excel problem (dynamic sum)

A friend of mine had this problem, data downloaded from ERP system contained duplicate value error which need to apportioned on quantity basis, the data set was fairly large.

Image

the solution I gave him involve helper column(s) , I myself believe its not the optimal solution but still sharing if anyone can come up with better solution

1> apply filter on data set and arrange “Total value” data in descending order

2>apply conditional formatting > highlight cells > duplicate values

this will show all duplicate values together in different color

3> add six helper columns (yes six,  you can reduce no. of columns later with better formulas , i have used them for better understanding)

helper1: apply “=COUNTIF($C$2:$C$92,C2)” this will count number of duplicates of particular values

helper2: apply “=IF(C2=C1,0,1)” this will help us indicate where values are changing

helper3: apply “=F2*G2” this will give number of times a unique value is repeated

helper4: apply “=SUM(D2:OFFSET(D2,H2-1,0))” this will sum the quantities corresponding to a Value

spend some time here on learning this formula, its called dynamic sum ranges, very helpful

helper5: apply “=IF(G2=1,I2,J1)” this will fill the total number of quantity in column corresponding to a value

helper6: apply “=C2*D2/J2” this will give you the desired values

Image

copy paste special values the helper6 column values in total values column

remove conditional formatting from total value column

arrange S.No column ascending order

there you are

better solution invited

link to file : https://drive.google.com/file/d/0B2FLZ9YjqpJTYXh2cUV4WGx0Vzg/edit?usp=sharing

 

Advertisements

One thought on “An excel problem (dynamic sum)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s