Formula to Group Companies under Categories

[TaraAlexander]

I have two excel lists.

[TABLE="width: 1595"]
<tbody>[TR]
[TD]List A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Organization[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 3[/TD]
[TD]Category 4[/TD]
[TD]Category 5[/TD]
[/TR]
[TR]
[TD]Azure Standard[/TD]
[TD]Beans - CANNED[/TD]
[TD]Beans - DRY[/TD]
[TD]Beet - CANNED[/TD]
[TD]Mixed Fruit - FROZEN[/TD]
[TD]Onions - FROZEN[/TD]
[/TR]
[TR]
[TD]Basic American Foods, Inc.[/TD]
[TD]Potatoes: Russett - FROZEN[/TD]
[TD]Beet - CANNED[/TD]
[TD]Garlic - SPECIALTY[/TD]
[TD]Potatoes: Russett - FROZEN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beaverton Foods, Inc.[/TD]
[TD]Onions - ORGANIC[/TD]
[TD]Garlic - SPECIALTY[/TD]
[TD]Potatoes: Russett - FROZEN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boardman Foods, Inc.[/TD]
[TD]Garlic - SPECIALTY[/TD]
[TD]Mixed Fruit - FROZEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Calbee North America, LLC[/TD]
[TD]Potatoes: Diced - FROZEN[/TD]
[TD]Potatoes: Russett - FROZEN[/TD]
[TD]Onions - WHOLE PEELED[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]List B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Organization[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 3[/TD]
[TD]Category 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Safe Quality Food Institute (SQFI)[/TD]
[TD]AGENCIES/ASSOCIATIONS: Associations/Agencies[/TD]
[TD]FOOD SAFETY/SECURITY: HACCP/ISO[/TD]
[TD]FOOD SAFETY/SECURITY: Food Safety/Security[/TD]
[TD]TRAINING: Training & Education[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dorsey & Whitney LLP[/TD]
[TD]AGRICULTURE: Commodity Sourcing[/TD]
[TD]AGRICULTURE: Seed Distributors[/TD]
[TD]CONSULTING- BUSINESS SERVICES: Accounting/Tax/Financial[/TD]
[TD]CONSULTING- BUSINESS SERVICES: Insurance & Risk Mgmt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Safe Quality Food Institute (SQFI)[/TD]
[TD]FOOD SAFETY/SECURITY: HACCP/ISO[/TD]
[TD]TRAINING: Training & Education[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dorsey & Whitney LLP[/TD]
[TD]AGRICULTURE: Seed Distributors[/TD]
[TD]CONSULTING- BUSINESS SERVICES: Accounting/Tax/Financial[/TD]
[TD]CONSULTING- HUMAN RESOURCES: Employee Benefits[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A & K Development Company[/TD]
[TD]EQUIPMENT- CONVEYANCE/ BATCH PROCESSING: Conveyor Belts, Pulleys, Microrollers & Vibrators[/TD]
[TD]EQUIPMENT- CONVEYANCE/ BATCH PROCESSING: Conveyor Chain[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GEM Equipment of Oregon, Inc.[/TD]
[TD]EQUIPMENT- CONVEYANCE/ BATCH PROCESSING: Conveyor Belts, Pulleys, Microrollers & Vibrators[/TD]
[TD]EQUIPMENT- PROCESSING: Berry Processing Syst.[/TD]
[TD]EQUIPMENT- GENERAL: Processing Equipment[/TD]
[TD]EQUIPMENT- GENERAL: Packaging Equipment[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Need a formula to group as below. There are 200+ categories & 100+ companies.

[TABLE="width: 1226"]
<tbody>[TR]
[TD]Beans - CANNED[/TD]
[TD]Beet - CANNED[/TD]
[TD]Garlic - SPECIALTY[/TD]
[TD]Potatoes: Russett - FROZEN[/TD]
[/TR]
[TR]
[TD]Azure Standard[/TD]
[TD]Azure Standard[/TD]
[TD]Basic American Foods, Inc.[/TD]
[TD]Basic American Foods, Inc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Basic American Foods, Inc.[/TD]
[TD]Beaverton Foods, Inc.[/TD]
[TD]Beaverton Foods, Inc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Boardman Foods, Inc.[/TD]
[TD]Calbee North America, LLC[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roger Govier]

Hi
I don't see what List B has to do with what you are trying to achieve.

If you have Excel 2013 or above Use Get Data from Table for List A.
Select column A.
Select Unpivot other columns.
Delete the Category column.
Rename the last column as Products.
Save and Load data to Worksheet.
Summarize resulting table with Pivot Table and add Product to the Rows area, followed by Organisation.

 

[alansidman]

If you normalize your data, then you could create a Pivot Table that will resemble you requirements.

Here is code to normalize your data.

Option Explicit


Sub Normalize()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim i As Long, j As Long, lr As Long, lr2 As Long, lc As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A1") = "Organization"
    s2.Range("B1") = "Product"
    For i = 2 To lr
        lr2 = s2.Range("B" & Rows.Count).End(xlUp).Row
        lc = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        s1.Range("A" & i).Copy s2.Range("A" & lr2 + 1)
        s1.Range(Cells(i, 2), Cells(i, lc)).Copy
        s2.Range("B" & lr2 + 1).PasteSpecial xlPasteValues, , , True
        lr2 = s2.Range("B" & Rows.Count).End(xlUp).Row
    Next i
    For i = 3 To lr2
        If s2.Range("A" & i) = "" Then
            s2.Range("A" & i) = s2.Range("A" & i - 1)
        End If
    Next i
End Sub

Pivot would look like this

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

2​

[/td][td]

Row Labels​

[/td][td]

Beans - CANNED​

[/td][td]

Beans - DRY​

[/td][td]

Beet - CANNED​

[/td][td]

Garlic - SPECIALTY​

[/td][td]

Mixed Fruit - FROZEN​

[/td][td]

Onions - FROZEN​

[/td][td]

Onions - ORGANIC​

[/td][td]

Onions - WHOLE PEELED​

[/td][td]

Potatoes: Diced - FROZEN​

[/td][td]

Potatoes: Russett - FROZEN​

[/td][/tr]


[tr][td]

3​

[/td][td]

Azure Standard​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

4​

[/td][td]

Basic American Foods, Inc.​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

2​

[/td][/tr]


[tr][td]

5​

[/td][td]

Beaverton Foods, Inc.​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][/tr]


[tr][td]

6​

[/td][td]

Boardman Foods, Inc.​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

7​

[/td][td]

Calbee North America, LLC​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[/table]