summary sheet consolidate.

[shajan]

Dear Ninjas !

I have a sheet with raw data ! I am trying to consolidate and get only unique entries.

I have uploaded the sample file here - 48KB

http://www.2shared.com/file/5E-46vnE/summary-sheet.html

which also contains the desired output.


Please help me with a code to achieve this.

Thanks.

 

[Hui]

Shajan


Use an Asdvanced Filter Unique to extract the list of unique Item Codes

Then use Index/Match to retrieve the corresponding values for each text column

use Sumproduct to retrieve the corresponding cumulative values for Qty Out column

 

[shajan]

Thanks Hui,

Will try out ! Thanks for pointing in the right direction.

Regards!

 

[SirJB7]

Hi, shajan!


Here's another approach to automatize the process if you happen to do it repeatedly.


Give a look at this file:

https://dl.dropbox.com/u/60558749/summary%20sheet%20consolidate.%20-%20summary-sheet%20%28for%20shajan%20at%20chandoo.org%29.xlsm


Column A, A5:A38 : helper column

=A4+SI(CONTAR.SI(B$5:B5;B5)=1;1;0) -----> in english: =A4+IF(COUNTIF(B$5:B5,B5)=1,1,0)


H14:K37 : output items

=BUSCARV(FILA()-FILA(H$13);$A$5:$F$38;COLUMNA()-COLUMNA($H13)+2;FALSO) -----> in english: =VLOOKUP(ROW()-ROW(H$13),$A$5:$F$38,COLUMN()-COLUMN($H13)+2,FALSE)


L14:L37 : output sum

=SUMAR.SI($B$5:$B$38;H14;$F$5:$F$38) -----> in english: =SUMIF($B$5:$B$38,H14,$F$5:$F$38)


Just advise if any issue.


Regards!

 

[shajan]

Thanks Sir for the formulae, will study them.

Brilliant Formulae, I could even expand to contain more data and get the desired output.

Thanks for your time and effort.

Regards!

 

[SirJB7]

Hi, shajan!

That was the idea, to give you a tool that let you automatically do the same for other ranges as source and target.

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!