Merging.....

Posted by Chris on October 12, 2001 12:58 PM

I have a list of assets. The cost of these assets have been spread out to several departments/locations with no master list of the total of each item. They are listed with the same description, say, 5 times with costs charged to each dept. Is there a simple way to merge the items with the same description into one item?

Ex. Looks like this
DESC. Location Dept Cost
Computer Seattle Warehouse $500
Carpet Seattle MIS $50
Carpet Seattle Accounting $100
Carpet Seattle HR $75
Chair Seattle Purch $50

Want to merge into one item:

Computer Seattle Warehouse $500
Carpet Seattle blank or ? $225
Chair Seattle Purch $50

The real list has 2,400 items with several items split up and several with costs only in one dept.
Need help!!

Thanks

Posted by Todd on October 12, 2001 1:03 PM

In a new cell to the right of each one, do =left(a1,search(" ",a1,1+search(" ",a1)))
that will get the first two words.
If you need to get the dollar amounts out of a cell do right(a1,len(a1)-search("$"))
Do a pivot table. Put word items on the left, and in the body do sum of the amounts.

Posted by Chris on October 12, 2001 1:13 PM

Thanks Todd!!!