How to sum all the unmerged cells next to a merged cell (repeatedly)?

**CreeDo** · 05-11-2012, 02:06 PM

Hopefully the screenshot explains what I want.

There are several manufacturers in Column B, and the products they make are in Column C.
The manufacturer's name is in a merged cell that spans all the products they make.
Column E is the # of units sold for each product.

I want to rearrange the manufacturers - currently they're in alphabetical order.
What I want is for them to be in order by highest number of units sold, to lowest.
It would be nice also to have a cell showing the totalled units for each manufacturer.

What is a logical way to do this? It's hundreds of rows so I don't want to do it manually.

\1

**Paul** · 05-11-2012, 02:17 PM

Hi CreeDo,

Please note the following:

1. You will usually get faster, more accurate assistance if you post sample workbooks instead of pictures. Most people don't like to manually create your workbook just to test a function.

2. You'd be best served never using Merged Cells. To act like a true database, every record should have an identifier if you plan to sort, group or summarize the data based on information in that column.

So my suggestion is to un-merge those cells and put the company name into each row of each product they manufacture. It may not be the prettiest, but it will make the task MUCH easier. You can then use summary functions like SUMIF, COUNTIF, AVERAGEIF, etc. on the data, and you could even create a Pivot Table to quickly summarize the information.

**CreeDo** · 05-11-2012, 03:46 PM

Paul - thanks for the reply. Unfortunately I didn't make it, I was just given the task of trying to sort it. Because this is someone else's workbook, I'm not sure if it's ok to post it. I'll try what you suggest, expanding a merged cell back to unmerged, while copying the contents to every cell, seems pretty straightforward.

edit: if anyone has a similar task, this is what I did -

• Highlighted the merged cells, unmerged them, ctrl+D to duplicate values.
There's a macro to do this here:
http://answers.microsoft.com/en-us/o...c-7c6302214534

• Copied just the essential data without headers to a new workbook.

• Inserted a new pivot table on Sheet 2 of that workbook, containing just manufacturer, product, and units sold. It seemingly magically read my mind and grouped everything according to manufacturer, with a [+] dropdown next to each manufacturer that contained each product, with its relevant units sold. It also totalled all of the units sold automatically for each manufacturer, and displayed that total in the next column.

• Right clicked one of the [+] or [-] icons, collapsed all. Highlighted the first totalled value in the next column, then did data -> sort. Sorted high to low.

That was it, surprisingly not so bad.