Not certain I understand the logic of the "Company" cell in the output table. This is just a guess.
Array enter this formula in F3 copy down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Row\Col |
E |
F |
G |
H |
I |
2 |
State(s) of Interest |
MA |
CA |
NY |
NJ |
3 |
Company |
Company 2 |
Company 1 |
Company 3 |
Company 3 |
4 |
|
Company 3 |
Company 3 |
Company 6 |
Company 4 |
5 |
|
Company 5 |
Company 4 |
Company 7 |
Company 6 |
6 |
|
Company 6 |
Company 5 |
Company 8 |
Company 7 |
7 |
|
Company 10 |
Company 6 |
|
Company 8 |
8 |
|
|
Company 8 |
|
Company 10 |
9 |
|
|
Company 9 |
|
|
10 |
|
|
Company 10 |
|
|
11 |
|
|
|
|
|
12 |
|
|
|
|
|
Does this do what you want?
Bookmarks