DUPLICATING VALUES TO REPLACED AS ONE VALUE

[jams]

Dear All,

Hope all r doing great, once again i need your immediate response from you all to the following issue:

i have large HVAC parts data consist if 7,646 rows, where as in this data some part numbers and there qty are repating / duplicate and i want this repating part number and their qty should one item and qty should be added as one.

for example Coloumns A row # 4 to 6 are the same part number but their qty are different in coloumn # D 4 to 6 112, 5, 8. I want this value should be added against one line items that row # 4 and qty should be 125.

please help me in this regards and i trust always chandoo.org in responding promptly. I love this forumn very much.

regards
Jams

 

[jams]

for more refernce sample file is attache for your review.

 

[Smallman]

Hi Jams

If you just want a summary of the data in Sheet1 - quick and dirty, just throw a pivot table over the data.

File attached to show workings.

Take care

Smallman

 

[lohithsriram]

Hi Jams,

Smallman's approach with Pivot table is always easy and fastest way. In case, if you do not want to use the Pivot table(very unlikely) then you can follow this method.

1) Extract the unique CATNO list into another column (Advanced filter -> Unique records only). Say you have these unique records copied on K column.
2) Have this formula on L Column and you get the result same as the Pivot table does. Use the Vlookup on subsequent columns to fetch any other table data from the main table.

=SUMIF($A$2:$A$6,"="&K2,$D$2:$D$6)

 

[jams]

Dear smallman & Lohith,

above both ways are working.

many many thanks and appreciate you both for express response, may good bless you and keep on rocking every time.

Regards
Mohd Jameel​