Quickest way to merge values with data on separate rows but with same unique reference

[AlexNorman100]

Hello,

I have a large amount of data cleansing to do and I'm sure there is a quick way to do this but I can't find anything on Google - maybe I'm not entering the right buzzwords.

Please see attached and first column - Vendor No.. I've had to replace Vendor Name with Vendor A for confidentiality purposes.

As you can see (if you scroll down) some Vendors have the same Vendor Number - I need to merge the values for each duplicate Vendor Number so that there is just one Vendor Number with the aggregated value of the duplication.

EG Vendor No 1940 is duplicated and if the value is merged it will equal £60,460.06

Now I can do this manually but it will take a longggggg time! Is there anyway I can do this quickly without having to manually merge and add?

Thanks in advance!!

[Pete_UK]

First of all you need to get a unique list of Vendor numbers. You can do this easily by using Advanced Filter:

In the panel click on Copy to another location
List Range: $A$1:$A$10866
Put $I$1 in Copy To box
Click on Unique Records only
Click OK.

Copy the titles from B1:G2 into cells starting from J1, and adjust the column widths to suit.

Then you need to get the names that go with those numbers - put this formula in J3:

=VLOOKUP(I3,A:B,2,0)

then copy down by double-clicking the fill handle (the small black square in the bottom right corner of the cursor, with J3 selected)

Then you need to add the values for each vendor number - put this formula in K3:

=SUMIF($A:$A,$I3,C:C)

Format the cell as you wish to see it (use the Format Painter from cell C3), then copy the formula across into L3:O3, and then you can copy the formulae down the rows using the fill handle as before.

You can fix the values of these formulae, and then you can delete columns A to H.

Hope this helps.

Pete

[José Augusto]

Hi
1st. - copy the values of column A and paste them in a new sheet
2nd. - Select in the new sheet the column A and go to data->remove duplicates
3rd. - Use in column B (in B3) the following formula

Please Login or Register  to view this content.

4th. - Use in C3 the following formula and copy down and right to G10866

Please Login or Register  to view this content.

see the file

[AlexNorman100]

Thank you Jose and Pete. Both solutions worked!