Formula to Group Companies under Categories

[TaraAlexander]

I have two excel lists.

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

<tbody>
</tbody>

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

Beans - CANNED	Beet - CANNED	Garlic - SPECIALTY	Potatoes: Russett - FROZEN
Azure Standard	Azure Standard	Basic American Foods, Inc.	Basic American Foods, Inc.
	Basic American Foods, Inc.	Beaverton Foods, Inc.	Beaverton Foods, Inc.
		Boardman Foods, Inc.	Calbee North America, LLC

<tbody>
</tbody>

 

[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

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
2​	Row Labels​	Beans - CANNED​	Beans - DRY​	Beet - CANNED​	Garlic - SPECIALTY​	Mixed Fruit - FROZEN​	Onions - FROZEN​	Onions - ORGANIC​	Onions - WHOLE PEELED​	Potatoes: Diced - FROZEN​	Potatoes: Russett - FROZEN​
3​	Azure Standard​	1​	1​	1​	​	1​	1​	​	​	​	​
4​	Basic American Foods, Inc.​	​	​	1​	1​	​	​	​	​	​	2​
5​	Beaverton Foods, Inc.​	​	​	​	1​	​	​	1​	​	​	1​
6​	Boardman Foods, Inc.​	​	​	​	1​	1​	​	​	​	​	​
7​	Calbee North America, LLC​	​	​	​	​	​	​	​	1​	1​	1​