Convert columns of categories into multiple rows in Excel

Question

I have a spreadsheet of Chamber of Commerce members that looks like this:

COMPANY              CATEGORY 1             CATEGORY 2       CATEGORY 3
Ameriprise           Lending Institutions   Banks
Country Inn          Lodging                Hotels           Bed & Breakfast
FirstBanc            Lending Institutions   Banks
Gillespie Fair       Events                 Festivals

That I need to convert—on a new sheet—into this:

COMPANY              CATEGORY
Ameriprise           Banks
Ameriprise           Lending Institutions
Country Inn          Bed & Breakfast
Country Inn          Hotels
Country Inn          Lodging
Firstbanc            Banks
Firstbanc            Lending Institutions
Gillespie Fair       Events
Gillespie Fair       Festivals

In a nutshell, I need to produce multiple entries for the Company for every Category that business falls under. Any help would be greatly appreciated.

This is a pretty frequently asked type of question. Here's one very complete answer: http://stackoverflow.com/a/20800915/293078. I've also written a VBA solution that might be of interest:http://stackoverflow.com/a/10922351/293078 — Doug Glancy, Jun 22 '16 at 01:54

score 0 · Answer 1 · answered Jun 22 '16 at 01:52

In data cleaning world, we call this reshaping a data frame from wide to long.

And there's a way to do it in Excel.

Hit this key combination: Alt D P. This will bring up PivotTable Wizard (Yup, not many know this.)
Select Multiple consolidation ranges. Click next.
Select I will create the page fields. Click next.
Select your data range. Click next.
Select the location to show to pivot table. Click next.
You'll see a pivot table appearing. In the PivotTable Field List, untick Row and Column.
You should be left with count of value and a number. Double click that cell. You'll see a new sheet with your converted data table.

Convert columns of categories into multiple rows in Excel

1 Answers1